Teradata HMAC Hashing UDF

• Teradata C/C++ UDF for generating HMAC SHA256/MD5/SHA1 hashes
• The UDF code uses functions from the following library: Portable C++ Hashing Library

Installation

1. Clone the repo

git clone https://github.com/tonys-code-base/teradata-hmac-hash-udf.git

2. Edit file create_hmac_hash_function.sql and replace references to database MYPROJ to reflect your installation target

DATABASE MYPROJ;

• Launch a command prompt/terminal shell and change directory to location of cloned source
• Using BTEQ run the modified content of create_hmac_hash_function.sql
• This will create the UDF in the specified target database

<databasename>.hmac_hash(MSG, SECRET, HMAC_HASH_TYPE)    

UDF Parameters

The UDF requires 3 input parameters.

1. MSG

The input string which is to be hashed

2. SECRET_KEY

The HMAC key to used.  This must be a string literal of hexidecimal 
bytes as generated by your **CSPRNG**

3. HMAC_HASH_TYPE

The HASH algorithm to use.  This can be one of 3 values

• HMAC_SHA256
• HMAC_SHA1
• HMAC_MD5

SECRET_KEY for HMAC

1. Guidelines for Length of SECRET_KEY

The following table lists the recommended SECRET_KEY length as per RFC2104 for HMAC.

Algorithm(H)	SECRET_KEY Length (bytes)
MD5	16
SHA1	20
SHA-256	32

2. Generating a Secure Key

Generate a secure key according the guidelines listed above for key length using /dev/urandom on a Linux system.

For example, a key length of 32 bytes is recommended for SHA256, which can be generated as follows:

hexdump -C -n 32 /dev/urandom > h_sha256_key.txt

3. Guidelines for Securing SECRET_KEY

The following is a sample table can be used to store keys. Access to this table should be restricted.

CREATE TABLE myproj.k_store 
    (
    key_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
    key_value VARCHAR(512) CHARACTER SET LATIN CASESPECIFIC
    )
PRIMARY INDEX ( key_name );

4. Loading Keys into Target Table

• Load the key/s required into the above table using TPT Operators that do not log parameter values to Query (DBQL) and Access log tables. A sample TPT script has been provided in the repo at sample_tpt_load_keys/*

• Below are some sample keys that have been loaded into the table

myproj.k_store

key_name	key_value
h_md5_key	ca5af32a784e22de51df623bf13f51b1
h_sha1_key	0f04ae0aa54db11c0c53dc36fbefbac27e099dbf
h_sha256_key	6e670ed3e0ebf1daf7570f5f83b45ed48d6df3848c09f38c319c8df3fa5dfc41

5. "Fetching" the SECRET_KEY as Input to UDF

Using a correlated sub-query, we can obtain the key without exposing it in Teradata's log tables by substituting the following query as input to the SECRET_KEY UDF Parameter:

SELECT key_value FROM myproj.k_store WHERE key_name = '<key_name>';

Example of a Possible Usage Scenario

Scenario

Table Customer_Data contains field cust_surname. The field needs to be hashed before being presented to an Analyst (with Teradata userid td_analyst) in an SQL resultset. The Analyst should also not be able to see the SECRET_KEY.

Sample Solution

• DBA creates the following view

REPLACE VIEW Customer_Data_hashed
AS
SELECT hmac_hash(cust_surname
       ,(SELECT key_value FROM myproj.k_store WHERE key_name = 'h_sha256_key')
	   ,'HMAC_SHA256'
       ) as hashed_surname
       
FROM Customer_Data;

• DBA then grants the Analyst select access to this view

GRANT SELECT ON Customer_Data_hashed TO td_analyst;

Usage Examples

The following examples use test keys as setup in myproj.k_store and assume that the UDF has been installed into target database MYPROJ.

1. Generating HMAC SHA_256 hash

SELECT MYPROJ.hmac_hash(
       'The quick brown fox jumps over the lazy dog.'
       ,(SELECT key_value FROM myproj.k_store WHERE key_name = 'h_sha256_key')
	   ,'HMAC_SHA256'
       )

Output:

bb60d08740527e04c95f8d7c615a9e5c425951192913976c04c3f3419fa61004

2. Generating HMAC MD5 hash

SELECT MYPROJ.hmac_hash(
       'The quick brown fox jumps over the lazy dog.'
       ,(SELECT key_value FROM myproj.k_store WHERE key_name = 'h_md5_key')
	   ,'HMAC_MD5'
       )

Output:

d74a6e93a5bc07422e3e18db7de13bc4

3. Generating HMAC SHA1 hash

SELECT MYPROJ.hmac_hash(
       'The quick brown fox jumps over the lazy dog.'
       ,(SELECT key_value FROM myproj.k_store WHERE key_name = 'h_sha1_key')
	   ,'HMAC_SHA1'
       )

Output:

9aa16612fc822e77a149896701f65c4aa64d7614