When I wrote my article on Oracle passwords I included a small, limited implementation of the PBKDF2 algorithm in order to illustrate the 12c hashing methodology. Here I’m publishing a fuller implementation with parameters for determining the derived key length, number of hashing iterations, and a choice of hashing methods (SH1, SH256, SH384, or SH512.) I’ve included methods to return a RAW value (up to 32K octets/bytes) and VARCHAR2 (up to 16K octets, 32K hex characters) as well as BLOB and CLOB variations if you happen to need a large hash value.
The VARCHAR2 version is simply RAWTOHEX wrapped around the get_raw. The BLOB and CLOB follow the same overall logic but have some small internal optimizations to help with extremely large results and/or numerous iterations.
My implementation is a fairly straight forward adaption of documentation in section 5.2 of RFC2898. The only significant deviation is with the BLOB and CLOB functions where I batch up intermediate concatenations within a RAW or VARCHAR2 before concatenating to the BLOB or CLOB result. I do this for efficiency because lob operations are slower.
First, a couple of use cases to test the functionality. These were both taken from the test vectors published in RFC6070.
SQL> select pbkdf2.get_hex('password',utl_raw.cast_to_raw('salt'),1,20,2) from dual;
PBKDF2.GET_HEX('PASSWORD',UTL_RAW.CAST_TO_RAW('SALT'),1,20,2)
-------------------------------------------------------------------------------------------
0C60C80F961F0E71F3A9B524AF6012062FE037A6
SQL> select pbkdf2.get_hex('password',utl_raw.cast_to_raw('salt'),4096,20,2) from dual;
PBKDF2.GET_HEX('PASSWORD',UTL_RAW.CAST_TO_RAW('SALT'),4096,20,2)
-------------------------------------------------------------------------------------------
4B007901B765489ABEAD49D926F721D065A429C1
And now, the PBKDF2 package
CREATE OR REPLACE PACKAGE pbkdf2
IS
-- .///.
-- (0 o)
---------------0000--(_)--0000---------------
--
-- Sean D. Stuber
-- sean.stuber@gmail.com
--
-- oooO Oooo
--------------( )-----( )---------------
-- \ ( ) /
-- \_) (_/
-- Implementation of algorithm described in section 5.2 of RFC2898
-- https://tools.ietf.org/html/rfc2898
-- dk_length refers to number of octets returned for the desired key
-- regardless of whether the result is raw/blob or hex characters in varchar2/clob
-- So, a 20-octet key returned by get_raw, would be a 40 character hex string
-- returned by get_hex. The dk_length parameter would be 20 in both cases.
-- The following HMAC algorithms are supported
-- DBMS_CRYPTO.HMAC_SH1 = 2
-- DBMS_CRYPTO.HMAC_SH256 = 3
-- DBMS_CRYPTO.HMAC_SH384 = 4
-- DBMS_CRYPTO.HMAC_SH512 = 5
-- Test vectors
-- https://tools.ietf.org/html/rfc6070
-- select pbkdf2.get_hex('password',utl_raw.cast_to_raw('salt'),1,20,2) from dual;
-- 0C60C80F961F0E71F3A9B524AF6012062FE037A6
-- select pbkdf2.get_hex('password',utl_raw.cast_to_raw('salt'),2,20,2) from dual;
-- EA6C014DC72D6F8CCD1ED92ACE1D41F0D8DE8957
-- select pbkdf2.get_hex('password',utl_raw.cast_to_raw('salt'),4096,20,2) from dual;
-- 4B007901B765489ABEAD49D926F721D065A429C1
-- select pbkdf2.get_hex('passwordPASSWORDpassword',utl_raw.cast_to_raw('saltSALTsaltSALTsaltSALTsaltSALTsalt'),4096,25,2) from dual;
-- 3D2EEC4FE41C849B80C8D83662C0E44A8B291A964CF2F07038
FUNCTION get_raw(
p_password IN VARCHAR2,
p_salt IN RAW,
p_iterations IN PLS_INTEGER,
p_dk_length IN PLS_INTEGER,
p_hmac IN PLS_INTEGER DEFAULT DBMS_CRYPTO.hmac_sh512
)
RETURN RAW
DETERMINISTIC;
FUNCTION get_hex(
p_password IN VARCHAR2,
p_salt IN RAW,
p_iterations IN PLS_INTEGER,
p_dk_length IN PLS_INTEGER,
p_hmac IN PLS_INTEGER DEFAULT DBMS_CRYPTO.hmac_sh512
)
RETURN VARCHAR2
DETERMINISTIC;
FUNCTION get_blob(
p_password IN VARCHAR2,
p_salt IN RAW,
p_iterations IN PLS_INTEGER,
p_dk_length IN PLS_INTEGER,
p_hmac IN PLS_INTEGER DEFAULT DBMS_CRYPTO.hmac_sh512
)
RETURN BLOB
DETERMINISTIC;
FUNCTION get_clob(
p_password IN VARCHAR2,
p_salt IN RAW,
p_iterations IN PLS_INTEGER,
p_dk_length IN PLS_INTEGER,
p_hmac IN PLS_INTEGER DEFAULT DBMS_CRYPTO.hmac_sh512
)
RETURN CLOB
DETERMINISTIC;
END;
/
CREATE OR REPLACE PACKAGE BODY pbkdf2
IS
-- .///.
-- (0 o)
---------------0000--(_)--0000---------------
--
-- Sean D. Stuber
-- sean.stuber@gmail.com
--
-- oooO Oooo
--------------( )-----( )---------------
-- \ ( ) /
-- \_) (_/
-- Implementation of algorithm described in section 5.2 of RFC2898
-- https://tools.ietf.org/html/rfc2898
-- dk_length refers to number of octets returned for the desired key
-- regardless of whether the result is raw/blob or hex characters in varchar2/clob
-- So, a 20-octet key returned by get_raw, would be a 40 character hex string
-- returned by get_hex. The dk_length parameter would be 20 in both cases.
-- The following HMAC algorithms are supported
-- DBMS_CRYPTO.HMAC_SH1 = 2
-- DBMS_CRYPTO.HMAC_SH256 = 3
-- DBMS_CRYPTO.HMAC_SH384 = 4
-- DBMS_CRYPTO.HMAC_SH512 = 5
c_max_raw_length CONSTANT PLS_INTEGER := 32767;
c_max_hex_length CONSTANT PLS_INTEGER := 32767;
SUBTYPE t_maxraw IS RAW(32767);
SUBTYPE t_maxhex IS VARCHAR2(32767);
SUBTYPE t_hmac_result IS RAW(64); -- must be big enough to hold largest supported HMAC
FUNCTION iterate_hmac_xor(
p_salt IN RAW,
p_iterations IN PLS_INTEGER,
p_hmac IN PLS_INTEGER,
p_block_iterator IN PLS_INTEGER,
p_raw_password IN RAW
)
RETURN t_hmac_result
IS
v_u t_maxraw;
v_f_xor_sum t_hmac_result;
BEGIN
-- The RFC describes the U(1)...U(c) values recursively
-- but the implementation below simply loops with a stored value
-- to achieve the same functionality.
v_u :=
UTL_RAW.CONCAT(
p_salt,
UTL_RAW.cast_from_binary_integer(p_block_iterator, UTL_RAW.big_endian)
);
v_u := DBMS_CRYPTO.mac(src => v_u, typ => p_hmac, key => p_raw_password);
v_f_xor_sum := v_u;
FOR c IN 2 .. p_iterations
LOOP
v_u := DBMS_CRYPTO.mac(src => v_u, typ => p_hmac, key => p_raw_password);
v_f_xor_sum := UTL_RAW.bit_xor(v_f_xor_sum, v_u);
END LOOP;
RETURN v_f_xor_sum;
END iterate_hmac_xor;
FUNCTION get_raw(
p_password IN VARCHAR2,
p_salt IN RAW,
p_iterations IN PLS_INTEGER,
p_dk_length IN PLS_INTEGER,
p_hmac IN PLS_INTEGER DEFAULT DBMS_CRYPTO.hmac_sh512
)
RETURN RAW
DETERMINISTIC
IS
c_hlen CONSTANT PLS_INTEGER
:= CASE p_hmac
WHEN DBMS_CRYPTO.hmac_sh1 THEN 20
WHEN DBMS_CRYPTO.hmac_sh256 THEN 32
WHEN DBMS_CRYPTO.hmac_sh384 THEN 48
WHEN DBMS_CRYPTO.hmac_sh512 THEN 64
END ;
c_octet_blocks CONSTANT PLS_INTEGER := CEIL(p_dk_length / c_hlen);
v_t_concat t_maxraw := NULL;
v_block_iterator PLS_INTEGER := 1;
BEGIN
-- raise exception message per rfc
-- but this limit is rather moot since the function
-- is capped by raw limits
IF p_dk_length > (POWER(2, 32) - 1) * c_hlen
THEN
raise_application_error(-20001, 'derived key too long');
ELSIF p_dk_length > c_max_raw_length
THEN
raise_application_error(-20001, 'raw output must be less than to 32K bytes');
END IF;
IF p_iterations < 1
THEN
raise_application_error(-20001, 'must iterate at least once');
END IF;
-- Loop one block of hlen-octets at a time of the derived key.
-- If we build a key past the desired length then exit early, no need to continue
WHILE v_block_iterator <= c_octet_blocks
AND (v_t_concat IS NULL OR UTL_RAW.LENGTH(v_t_concat) < p_dk_length)
LOOP
v_t_concat :=
UTL_RAW.CONCAT(
v_t_concat,
iterate_hmac_xor(
p_salt,
p_iterations,
p_hmac,
v_block_iterator,
UTL_RAW.cast_to_raw(p_password)
)
);
v_block_iterator := v_block_iterator + 1;
END LOOP;
RETURN UTL_RAW.SUBSTR(v_t_concat, 1, p_dk_length);
END get_raw;
FUNCTION get_hex(
p_password IN VARCHAR2,
p_salt IN RAW,
p_iterations IN PLS_INTEGER,
p_dk_length IN PLS_INTEGER,
p_hmac IN PLS_INTEGER DEFAULT DBMS_CRYPTO.hmac_sh512
)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
-- raise exception message per rfc
-- but this limit is rather moot since the function
-- is capped by varchar2 limits
IF p_dk_length > c_max_raw_length / 2
THEN
raise_application_error(-20001, 'hex representation must be less than 32K characters');
END IF;
RETURN RAWTOHEX(
get_raw(
p_password,
p_salt,
p_iterations,
p_dk_length,
p_hmac
)
);
END get_hex;
FUNCTION get_blob(
p_password IN VARCHAR2,
p_salt IN RAW,
p_iterations IN PLS_INTEGER,
p_dk_length IN PLS_INTEGER,
p_hmac IN PLS_INTEGER DEFAULT DBMS_CRYPTO.hmac_sh512
)
RETURN BLOB
DETERMINISTIC
IS
c_hlen CONSTANT PLS_INTEGER
:= CASE p_hmac
WHEN DBMS_CRYPTO.hmac_sh1 THEN 20
WHEN DBMS_CRYPTO.hmac_sh256 THEN 32
WHEN DBMS_CRYPTO.hmac_sh384 THEN 48
WHEN DBMS_CRYPTO.hmac_sh512 THEN 64
END ;
c_octet_blocks CONSTANT PLS_INTEGER := CEIL(p_dk_length / c_hlen);
v_t_concat BLOB;
v_block_iterator PLS_INTEGER := 1;
v_temp t_maxraw;
BEGIN
-- raise exception message per rfc
IF p_dk_length > (POWER(2, 32) - 1) * c_hlen
THEN
raise_application_error(-20001, 'derived key too long');
END IF;
IF p_iterations < 1
THEN
raise_application_error(-20001, 'must iterate at least once');
END IF;
DBMS_LOB.createtemporary(lob_loc => v_t_concat, cache => FALSE, dur => DBMS_LOB.session);
-- Loop one block of hlen-octets at a time of the derived key.
-- If we build a key past the desired length then exit early, no need to continue
WHILE v_block_iterator <= c_octet_blocks AND (DBMS_LOB.getlength(v_t_concat) < p_dk_length)
LOOP
v_temp :=
UTL_RAW.CONCAT(
v_temp,
RAWTOHEX(
iterate_hmac_xor(
p_salt,
p_iterations,
p_hmac,
v_block_iterator,
UTL_RAW.cast_to_raw(p_password)
)
)
);
-- Concatenating raw is faster than blob
-- only concatenate to the blob when the temp raw fills up
IF UTL_RAW.LENGTH(v_temp) > c_max_raw_length - c_hlen
THEN
DBMS_LOB.writeappend(v_t_concat, UTL_RAW.LENGTH(v_temp), v_temp);
v_temp := NULL;
END IF;
v_block_iterator := v_block_iterator + 1;
END LOOP;
DBMS_LOB.writeappend(v_t_concat, UTL_RAW.LENGTH(v_temp), v_temp);
DBMS_LOB.TRIM(v_t_concat, p_dk_length);
RETURN v_t_concat;
END get_blob;
FUNCTION get_clob(
p_password IN VARCHAR2,
p_salt IN RAW,
p_iterations IN PLS_INTEGER,
p_dk_length IN PLS_INTEGER,
p_hmac IN PLS_INTEGER DEFAULT DBMS_CRYPTO.hmac_sh512
)
RETURN CLOB
DETERMINISTIC
IS
c_hlen CONSTANT PLS_INTEGER
:= CASE p_hmac
WHEN DBMS_CRYPTO.hmac_sh1 THEN 20
WHEN DBMS_CRYPTO.hmac_sh256 THEN 32
WHEN DBMS_CRYPTO.hmac_sh384 THEN 48
WHEN DBMS_CRYPTO.hmac_sh512 THEN 64
END ;
c_octet_blocks CONSTANT PLS_INTEGER := CEIL(p_dk_length / c_hlen);
v_t_concat CLOB;
v_block_iterator PLS_INTEGER := 1;
v_temp t_maxhex;
BEGIN
-- raise exception message per rfc
IF p_dk_length > (POWER(2, 32) - 1) * c_hlen
THEN
raise_application_error(-20001, 'derived key too long');
END IF;
IF p_iterations < 1
THEN
raise_application_error(-20001, 'must iterate at least once');
END IF;
DBMS_LOB.createtemporary(lob_loc => v_t_concat, cache => FALSE, dur => DBMS_LOB.session);
-- Loop one block of hlen-octets at a time of the derived key.
-- If we build a key past the desired length then exit early, no need to continue
-- The end result is a HEX string, so double the length (2 characters to represent one byte)
WHILE v_block_iterator <= c_octet_blocks
AND (DBMS_LOB.getlength(v_t_concat) < p_dk_length * 2)
LOOP
v_temp :=
v_temp
|| RAWTOHEX(
iterate_hmac_xor(
p_salt,
p_iterations,
p_hmac,
v_block_iterator,
UTL_RAW.cast_to_raw(p_password)
)
);
-- Concatenating varchar2 is faster than clob
-- only concatenate to the clob when the temp varchar2 fills up
IF LENGTH(v_temp) > c_max_hex_length - 2 * c_hlen
THEN
v_t_concat := v_t_concat || v_temp;
v_temp := NULL;
END IF;
v_block_iterator := v_block_iterator + 1;
END LOOP;
v_t_concat := v_t_concat || v_temp;
DBMS_LOB.TRIM(v_t_concat, p_dk_length * 2);
RETURN v_t_concat;
END get_clob;
END;
/
The PBKDF2 algorithm is supposed to be slow to execute in order to discourage brute force hacking attempts. While I did use a few coding techniques to try to help performance, this is still not a fast implementation. If you need maximal speed then I recommend a c library. For short strings and less than 100000 iterations the package should have adequate speed for most use cases.
The code presented above requires at least 12cR1, but could run on lower versions by changing the supported hashing methods.
I hope it helps, questions and comments are always welcome.
Hi, thanks a lot for sharing your implementation.
Unfortunatly it doesn’t compile. The get_raw method has an error at:
WHILE v_block_iterator <= c_octet_blocks
AND (v_t_concat IS NULL OR UTL_RAW.LENGTH(v_t_concat)
c_max_raw_length / 2
THEN
raise_application_error(-20001, 'hex representation must be less than 32K characters');
END IF;
For sure a problem of wordpress 🙂
Thank you for reading and bringing the problem to my attention.
Your assessment is correct – wordpress was “formatting” my code. I’ve corrected it and the embedded code blocks should be readable and compilable now.
Thanks again!