PBKDF2 in Oracle 12c


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) 

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

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

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.

How Oracle Stores Passwords article updated through 12.2


I’ve updated the content through 12.2 and added a section on SQLNET.ORA parameters affecting the protocols (and thereby the hashing algorithms) supported.

The pdf file can be downloaded from my dropbox here.

How Oracle Stores Passwords


Several years ago I wrote a small summary of the Oracle password hashing and storage for versions up to 11g.

Today I’ve completed my update of that article up to 12.1.0.2, including code to mimic generation of passwords given the appropriate salts.
The initial publication is in PDF format, I may convert and reformat it to other forms for better distribution.

The pdf file can be downloaded from my dropbox here.

It was interesting and enjoyable digging into the details of the hashes and how they change between versions and interact with the case-sensitivity settings.

I hope you enjoy it as much as I did writing it.

Let’s get started!


Welcome to my blog about Oracle database development. I’m also interested in math and frequently write pl/sql and sql snippets to solve various math problems.

For example, here’s an article I wrote about solving a combinatorics problem found in dart games like 301,501,701, etc.

Fun with Oracle SQL – Solving Checkouts in a Game of 501 Darts – Oracle, SQL, Darts, Combinatorics

That article used a purely SQL solution,  I also ported the Mersenne Twister pseudo-random number generator algorithm to pl/sql.  Tackling that was both fun and frustrating.  Fun because it was an interesting task both mathematically and programmatically.  It was frustrating at the same time though because much of the algorithm is based on pointer manipulation which doesn’t exist within the context of pl/sql, so I had to fake it procedural get/set operations.  The final solution I came up successfully mimics all of the functionality of the original SIMD oriented Fast Mersenne Twister(SFMT) by Mutsuo Saito and Makoto Matsumoto.  In addition I allowed parameterization of the mersenne exponent which the original c version only supported by recompiling.  However, the pl/sql implementation is much slower than the original c (even with native compilation.)  I implemented the pl/sql port largely for academic interest, not as a production-ready solution.  However it does work and for small scale testing the performance may be adequate.

I hope those articles give a good example of the type of code you might find here and whet your appetite for more.


%d bloggers like this: