Hashing

Hashing is the process of taking plaintext data and running it through a “hash” function, which converts it to a fixed-length encrypted-like value. Hashing differs from encryption in that it is a unidirectional process. The output is called the hash and it’s indistinguishable from its original input form. The hash property is that once the data has been hashed, it can’t be returned to its original form. There is no unhash function. Encrypted data, on the contrary, can be decrypted. The other important principle to performing a hash operation is that the resulting hash yields no information about the original data.

When you look at the results of hashing data, it looks very much like encrypted data. It’s completely undecipherable:

sec_mgr@KNOX10g> COL "Hashed Data" format a30  sec_mgr@KNOX10g> COL "Encrypted Data" format a30  sec_mgr@KNOX10g> SELECT data_crypto.hash ('Data')     2                                       "Hashed Data",    3         data_crypto.encrypt ('Data', 'k')     4                                    "Encrypted Data"    5   FROM DUAL;    Hashed Data       Encrypted Data  -------------   ------------------- ñ?<zMû ;µ-';?     +¦ÖOY=nççæ-§Wz   

Hashing and encryption are fundamentally different. Like encryption, hashing is popular for storing values that are very sensitive. However, with hashing you can never retrieve the original data. This makes hashing very suitable for some situations and unsuitable for others.

The best example of hashing is in securing passwords. Passwords, in almost all cases, are hashed and not encrypted. To authenticate a person, the password provided by the user is hashed, and the result is compared to the resulting hash that is stored for the user. In this manner, an attacker who acquires the table storing the passwords has practically no chance at recovering the original passwords. If the passwords were encrypted, the attacker would only have to figure out the encryption key and algorithm used to encrypt the passwords to recover the passwords. With a hash-based password algorithm, the only way to get the password is to guess passwords, hash them, and then compare the resulting hash.

DBMS_CRYPTO Hashing

The DBMS_CRYTPO package provides support for MD4, MD5, and SHA-1 hash algorithms. Each hashing algorithm is supported by a PL/SQL function. There is one function that accepts a RAW, one that accepts CLOBs, and one that accepts BLOBs. All three return a RAW data type. The RAW is returned because the hashed output is of fixed size. The hashing algorithms take any arbitrary input size and return a fixed output size (128 bits for MD4 and MD5 and 160 bits for SHA).

The DBMS_CRYPTO hashing functions are also wrapped by functions in the DATA_CRYPTO package. The default algorithm is set to SHA. The return types are RAW for the HASH2RAW function and VARCHAR2 for the HASH function. It doesn’t make sense to store the hashed CLOB data in a CLOB column, nor to store the hashed BLOBs in a BLOB column because the hashed values are extremely small (20 bytes at most).

Hashing Algorithms

The DATA_CRYPTO package supports all three algorithms the DBMS_CRYPTO supports. Functions are provided so the algorithm may be called within a SQL statement:

 g_md4    CONSTANT PLS_INTEGER    := dbms_crypto.hash_md4;   g_md5    CONSTANT PLS_INTEGER    := dbms_crypto.hash_md5;   g_sha    CONSTANT PLS_INTEGER    := dbms_crypto.hash_sh1;     FUNCTION md4      RETURN PLS_INTEGER   AS   BEGIN      RETURN g_md4;   END;   …  

To test the hashing, a table is created that contains a user and their password. The initial password is the InitCap of the user’s name (this is a bad practice and is done here for illustrative purposes only). For user SCOTT, the password will be “Scott.”

sec_mgr@KNOX10g> DROP TABLE people;    Table dropped.   sec_mgr@KNOX10g> CREATE TABLE people     2    (ename VARCHAR2(12),    3    password VARCHAR2(30));    Table created.    sec_mgr@KNOX10g> INSERT INTO people     2   SELECT ename, INITCAP (ename)    3     FROM scott.emp;    14 rows created.    sec_mgr@KNOX10g> UPDATE people     2    SET PASSWORD = data_crypto.HASH (PASSWORD);    14 rows updated.    sec_mgr@KNOX10g> COL password format a40  sec_mgr@KNOX10g> SELECT ename, utl_raw.cast_to_raw(PASSWORD) password     2     FROM people    3   WHERE ROWNUM <= 3;    ENAME     PASSWORD  -------   -------------- SMITH     96BCF8C98F94B6ACE4A4B716CF0E3B32743A08B1  ALLEN     CEF92E8D1C84BBAA35CE6FB2942B26BC98D92C2C  WARD      31D20079CB16C9DF490A8337CD142C5D095EEAA5   

The passwords as stored are unrecoverable; you can’t uncompute the values or derive the original value by simply looking at the hash result.

To authenticate a user, you can write a function that computes the hash of the password provided and verifies it with the one stored:

CREATE OR REPLACE FUNCTION is_user_auth (    p_username   IN    VARCHAR2,    p_password   IN    VARCHAR2  )     RETURN BOOLEAN  AS    l_hashed_password VARCHAR2 (20);    l_user_is_authed BOOLEAN := FALSE;  BEGIN     FOR rec IN (SELECT PASSWORD                   FROM people                 WHERE UPPER (ename) = UPPER (p_username))   LOOP    l_hashed_password := data_crypto.HASH (p_password);      IF l_hashed_password = rec.PASSWORD    THEN     l_user_is_authed := TRUE;    END IF;    END LOOP;     RETURN l_user_is_authed;  END;   /  

Testing this function shows how password authentication should be performed. Note that this example assumes a case-sensitive password:

sec_mgr@KNOX10g> BEGIN     2    IF (is_user_auth ('scott', 'scott'))    3    THEN    4      DBMS_OUTPUT.put_line ('Scott authenticated with "scott"');    5    ELSE    6      DBMS_OUTPUT.put_line ('Scott could not authenticate with "scott"');    7    END IF;     8     9    IF (is_user_auth ('scott', 'Scott'))   10    THEN   11      DBMS_OUTPUT.put_line ('Scott authenticated with "Scott"');   12    ELSE   13      DBMS_OUTPUT.put_line ('Scott could not authenticate with "Scott"');   14    END IF;   15  END;   16 /   Scott could not authenticate with "scott"  Scott authenticated with "Scott"    PL/SQL procedure successfully completed.   

Message Authentication Codes

Message Authentication Codes (MAC—not to be confused with the Mandatory Access Control (MAC) discussed in the last chapter) expand on hashing algorithms by adding a secret key. The key is used in computing the hash, and the same key is required to verify the hash. Often this process is referred to as hashed message authentication codes or HMAC.

DBMS_CRYPTO supports the RFC 2104 standard for HMAC. The two algorithms supported for this are based on the MD5 and SHA algorithms. These are distinguished from the base hash functions with a “hmac” prefix. You can see this in the assignment of the DATA_CRYPTO HMAC package variables:

g_hmac_md5 CONSTANT PLS_INTEGER := dbms_crypto.hmac_md5;  g_hmac_sha CONSTANT PLS_INTEGER := dbms_crypto.hmac_sh1;   

The problem with the standard hashing is that an attacker could predict, guess, or assume the basic hashing algorithm used to compute the hash. In the preceding, an attacker could perform an offline attack on the password fields by simply doing what you did—calculate the hash given the standard data input.

If you use a MAC, the attacker would also have to know the key used to compute the HMAC. In this manner, the hash is secured by the key the same way encrypted data is:

sec_mgr@KNOX10g> UPDATE people      2      SET PASSWORD = INITCAP (ename);    14 rows updated.    sec_mgr@KNOX10g> -- compute HMAC using key    sec_mgr@KNOX10g> UPDATE people      2      SET PASSWORD =   3                   data_crypto.mac (PASSWORD, 'MAC Password Key');    14 rows updated.    sec_mgr@KNOX10g> SELECT ename, UTL_RAW.cast_to_raw (PASSWORD) PASSWORD      2      FROM people     3   WHERE ROWNUM <= 3;    ENAME       PASSWORD    --------    -----------   SMITH       88E02617F4999A033AE332E5062B198AEF7D7ECC    ALLEN       377B8B51C15D57216C5A57945F86D5009F9E22DA    WARD        A838B3ABAFF605FD1B6CCCF71325DC7F9CBE4898    

If you try to authenticate SCOTT now using your old function, you get a false reading:

sec_mgr@KNOX10g> BEGIN       2     IF (is_user_auth ('scott', 'Scott'))    3     THEN       4       DBMS_OUTPUT.put_line ('Scott authenticated with "Scott"');    5     ELSE        6       DBMS_OUTPUT.put_line ('Scott could not authenticate with "Scott"');    7     END IF;     8   END;    9 /   Scott could not authenticate with "Scott"   

To authenticate now, the authentication function has to be modified to include the key to the HMAC function:

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION is_user_auth (     2    p_username IN VARCHAR2,    3    p_password IN VARCHAR2      4  )     5    RETURN BOOLEAN      6  AS      7    l_hashed_password VARCHAR2 (20);    8    l_user_is_authed BOOLEAN := FALSE;    9  BEGIN    10    FOR rec IN (SELECT PASSWORD     11                  FROM people     12                 WHERE UPPER (ename) = UPPER (p_username))   13   LOOP     14    l_hashed_password := data_crypto.mac (p_password,   15                                          'MAC Password Key');   16      17    IF l_hashed_password = rec.PASSWORD     18    THEN     19      l_user_is_authed := TRUE;   20    END IF;   21   END LOOP;   22    23  RETURN l_user_is_authed;   24 END;   25 /       Function created.    sec_mgr@KNOX10g> BEGIN       2    IF (is_user_auth ('scott', 'Scott'))    3    THEN      4      DBMS_OUTPUT.put_line ('Scott authenticated with "Scott"');    5    ELSE      6      DBMS_OUTPUT.put_line ('Scott could not authenticate with "Scott"');    7    END IF;     8  END;    9 /    Scott authenticated with "Scott"    PL/SQL procedure successfully completed.   

As with encryption, the success in implementing HMAC is in keeping the key a secret. Exposing the key reduces the HMAC to a simple hashing function. This does not imply that hashing by itself is not sufficient or secure. This merely points out that HMAC can provide more security if an attacker can determine the hashing algorithm and has access to the hashed results because the attacker must then also obtain the key.