Teradata RDBMS: Generating HMAC SHA256, MD5 and SHA1 Hashes

Why the Need for Hashing?

Data Science/Analytics on Personally Identifiable Information (PII)

Transmission of Data

Guidelines for HMAC

RFC2104 Spec

Key Length

Specifications for Algorithm Key/Blocksizes
MD5    : K = 128/16 (bits/bytes)
SHA1 : K = 160/20 (bits/bytes)
SHA256 : K = 256/32 (bits/bytes)

Generating a Secret Key (K)

echo `hexdump -n 16 /dev/urandom -v -e '"" 1/1 "%02X" ""'`ca5af32a784e22de51df623bf13f51b1
echo `hexdump -n 20/dev/urandom -v -e '"" 1/1 "%02X" ""'`0f04ae0aa54db11c0c53dc36fbefbac27e099dbf
echo `hexdump -n 32/dev/urandom -v -e '"" 1/1 "%02X" ""'`6e670ed3e0ebf1daf7570f5f83b45ed48d6df3848c09f38c319c8df3fa5dfc41

How/Where Should the Keys be Stored?

  • The key/s should not be loaded using Client utilities such as SQL Assistant, BTEQ. For example, the following statement:
INSERT INTO myproj.k_store (KEY_NAME, KEY_VALUE) VALUES (
‘hmac_sha256_key’
,‘XXXXXXXXXXXXXXXX
)
  • Instead, look into loading the key/s from a file using Teradata TPT Operators, for which parameter values are not resolved in the Query and Access log tables/views
  • Once the key has been loaded, its value should never be hard coded into SQL queries/qualifiers (unless it’s a throw away key used solely for “sandpit” testing). As an example, the following query would expose the key’s value in the RDBMS log tables:
SELECT * FROM myproj.k_store WHERE KEY_VALUE = ‘XXXXXXXXXXXXXXXX
  • As mentioned earlier, a correlated sub-query can be used to retrieve the key’s value as input to the UDF. Examples of this will be demonstrated later in this article
  • Ensure the key is generated on a machine that conforms to security standards (i.e, encrypted storage, attached to secure network, etc.)

UDF Dependencies

Hash Libraries

Header File for Teradata Equivalent C Data Types

UDF Installation

git clone https://github.com/tonys-code-base/teradata-hmac-hash-udf.git
https://github.com/tonys-code-base/teradata-hmac-hash-udf/archive/master.zip
<database_name>.hmac_hash

Loading Keys into a Teradata Table

Table Format

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 )
;

Save Keys to File on Encrypted Drive

Load Keys using Teradata Parallel Transporter (TPT)

<Teradata_Host/IP>
<Teradata_Username>
<Teradata_Password>
tbuild -f tpt_sample_load_k_store.script -j tpt_sample_load_k_store -s 1

UDF Parameters

hmac_hash(msg, secret_key, hmac_hash_type)
hmac_hash(msg, '6173646667686a6b6c666531', hmac_hash_type)
hmac_hash(msg, '6f61696679726568666b6a32', hmac_hash_type)
hmac_hash(msg, '696f6a666566656665667733', hmac_hash_type)

“Fetching” the SECRET_KEY as Input to UDF

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

UDF Usage Examples

HMAC SHA256 hash Example

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'
)
bb60d08740527e04c95f8d7c615a9e5c425951192913976c04c3f3419fa61004

HMAC MD5 hash Example

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'
)
d74a6e93a5bc07422e3e18db7de13bc4

HMAC SHA1 hash Example

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'
)
9aa16612fc822e77a149896701f65c4aa64d7614

Closing Comments

select ‘XXXXXXXXXXXX’xc 

References

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store