How Oracle Stores Passwords

How Oracle Stores Passwords
Sean D. Stuber

Abstract—A DBA may need to replicate a user from one system to another while preserving the password, or restore a password after refreshing a system. In older systems (8.0.5 and earlier) in order to temporarily allow a user to login as someone else the DBA or other privileged user would need to change the password. Until 9i, even a DBA could not grant permissions on a schema’s objects without being logged in as the schema owner. So it was not uncommon to find the need to save passwords, temporarily change in order to log in and then restore the old version when the work was complete. With the advent of proxy users (8i) and GRANT ANY OBJECT PRIVILEGE ^[1] (9i) the need to take these steps has been largely removed; but it is still useful for administrators to understand where and how Oracle maintains user passwords.

Security—This article does not show, or attempt to show, how to break the algorithms or hack into a user’s account. Many of the queries and DDL used in the code snippets require enhanced privileges including: SELECT on the SYS.USER$ table and DBA_USERS view, EXECUTE privilege on the SYS.DBMS_CRYPTO^[2] package as well as the system privileges ALTER USER and ALTER SYSTEM.

Original publication:  2009-06-09
Updated through Oracle 21c:  2021-11-01

I. AUTHENTICATION

A user name is stored in plain text but the password information is stored as a hash. When a user logs in, the authentication information is hashed according to rules of the password version. If the generated hash and the stored hash match, the user is authenticated.

An obvious question is: Why does Oracle use Hashing instead of Encrypting? Hashing isn’t a reversible operation; but encryption is. The ramifications of this are simple. Since you can’t reverse a hash, you can’t extract the password from a hash; but, if the password is encrypted it could possibly be unencrypted (albeit with difficulty, but still possible.) In theory, an extremely lucky guess of random characters or brute force search of all possible combinations could pass with a false-positive since hash algorithms can duplicate. However, Oracle’s simplest algorithm yields 18,446,744,073,709,551,616 possible hashes. So, while it is possible two different strings could hash to the same value, the probability of finding one is remote.

II. HASHING ALGORITHM AND PERSISTANCE FOR 10G AND LOWER

A. Persistance
For database versions prior to 11g, the passwords are stored as a 16-digit hexadecimal number in the PASSWORD column of the SYS.USER$ table. The hash is published from the underlying USER$ table via the SYS.DBA_USERS view.

SQL> connect dbauser/dbapassword@mydatabase
Connected.
SQL> select username,password from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD
---------- ------------------
TESTUSER   AEB6397C8E7598A7

SQL> select name,password from sys.user$ where name = 'TESTUSER';

NAME       PASSWORD
---------- ------------------------
TESTUSER   AEB6397C8E7598A7

B. Algorithm
The hashing is a multi-step process. First the username and password are concatenated into a single string that is forced to upper case. That string is then converted to a multi-byte raw value with leading zeroes for each byte. This raw value is then encrypted with the standard DES algorithm using a default key. The last 16 bytes of the output are then used as a new key to encrypt the raw value a second time. The last 16 bytes of the second encryption becomes the stored hash value of the username and password.

There are some notable weaknesses in the methodology. One, the concatenation has no external salt, thus users with names and passwords that are identical except for a character offset can become the same when concatenated. For example: User ABCD with password EFGH will be concatenated to form: ABCDEFGH. Another user ABC with password DEFGH will be concatenated to form the same string: ABCDEFGH. This opens up (albeit with extreme resource consumption) an attack vector. Also, the DES algorithm is dated compared to modern encryption standards and, as such is subject to computational attack with sufficient resources, particularly when the attack is coupled with rainbow tables. More significantly, forcing upper-case on the initial concatenation means this algorithm cannot support case-sensitive passwords.
C. Code
The code below is not the actual function Oracle uses; but it mimics Oracle’s functionality. Entering a username and password the function will return the same value Oracle generates and stores in the SYS.USER$.PASSWORD column.

CREATE OR REPLACE FUNCTION create_10g_password_hash(p_name IN VARCHAR2,
                                                    p_password IN VARCHAR2)
    RETURN VARCHAR2
IS
    c_raw_zero            RAW(1) := HEXTORAW('0');
    c_encryption_method   INTEGER := 
                 DBMS_CRYPTO.encrypt_des + DBMS_CRYPTO.chain_cbc + DBMS_CRYPTO.pad_zero;
    v_str                 VARCHAR2(100);
    v_raw                 RAW(4096) := NULL;
    v_default_key         RAW(15) := HEXTORAW('0123456789ABCDEF');
    v_new_key             RAW(4096);
    v_encrypted           RAW(4096);
BEGIN
    -- Concatenate the username and password
    v_str := UPPER(p_name || p_password);

    -- Convert plain ascii string to multi-byte string with 0x00 in the high byte
    FOR i IN 1 .. LENGTH(v_str)
    LOOP
        v_raw := UTL_RAW.CONCAT(v_raw, c_raw_zero, 
                                UTL_RAW.cast_to_raw(SUBSTR(v_str, i, 1)));
    END LOOP;

    -- Encrypt with DES chipher block chaining (cbc) using default key
    -- Pad with 0's to the next even block length
    v_encrypted := DBMS_CRYPTO.encrypt(v_raw, c_encryption_method, v_default_key);

    -- Use the last 16 digits of the default encryption to generate a new key
    v_new_key := HEXTORAW(SUBSTR(RAWTOHEX(v_encrypted), -16));

    -- Re-encrypt with the new key
    v_encrypted := DBMS_CRYPTO.encrypt(v_raw, c_encryption_method, v_new_key);

    -- The last 16 digits are the hash
    RETURN SUBSTR(RAWTOHEX(v_encrypted), -16);
END create_10g_password_hash;

III. HASHING ALGORITHM AND PERSISTANCE FOR 11G

A. Persistance
11g added a new hashing algorithm and with that change the stored hash moved from the PASSWORD column of USER$ to the SPARE4 column. The 10g hash, if still used, is maintained in the old PASSWORD column as before. Neither of these hashes is published through the DBA_USERS view. Instead the PASSWORD column of the view returns NULL and a new column, PASSWORD_VERSIONS, indicates which password hash types are stored.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD             PASSWORD_VERSIONS                                                                                                     
---------- -------------------- --------------------                                                                                                  
TESTUSER                        10G 11G                                                                                                               

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME       PASSWORD             SPARE4                                                                                                                
---------- -------------------- ---------------------------------------------------------------                                                       
TESTUSER   AEB6397C8E7598A7     S:17F9149EFD0BDD9DBA305D6910D5928640F7727B29F261D851C58D37FA9A

B. Algorithm
In 11g the hashing algorithm is enhanced in two ways. First, a more modern hashing algorithm is used (SHA-1) instead of deriving a hash from substrings of encrypted output. Second, a randomized salt is used instead of using the username as the salt. These changes allow for the support of case-sensitivity in usernames and passwords.

While more robust, the algorithm is also simplified through these changes as well. First, a 10-byte (20 hex digit) salt value is generated through a randomizer. This value is then concatenated to the raw version of the password. This raw value is then hashed with the SHA-1 algorithm. The result is represented as a hex string prefixed with “S:” and suffixed with the hex representation of the salt.

Thus in the example above, the spare4 record can be split into pieces:
This the password hash: 17F9149EFD0BDD9DBA305D6910D5928640F7727B
This is the salt: 29F261D851C58D37FA9A

Unfortunately, while the 11g hashing algorithm is more secure than the older version, by the time 11g was first released in 2007, collision attacks on SHA-1 had already been demonstrated two years earlier.^[3]

C. Code
The code below mimics Oracle’s 11g hashing functionality. Entering a password and a salt value will return the same value Oracle generates and stores in the SYS.USER$.SPARE4 column.

CREATE OR REPLACE FUNCTION create_11g_password_hash(p_password IN VARCHAR2, 
                                                    p_hex_salt IN VARCHAR2)
    RETURN VARCHAR2
IS
    v_hash   RAW(80);
BEGIN
    -- The salt can be any value that is exactly 10 bytes (80 bits)
    -- i.e. the input salt must be 20 hex characters.
    IF NVL(LENGTH(p_hex_salt),0) != 20
    THEN
        raise_application_error(-20001, 'Salt must be 20 hex digits (0-9,A-F');
    END IF;

    -- Concatenate the password with the salt
    v_hash := UTL_RAW.CONCAT(UTL_RAW.cast_to_raw(p_password), HEXTORAW(p_hex_salt));

    -- Hash the concatenation
    v_hash := sys.DBMS_CRYPTO.hash(v_hash, sys.DBMS_CRYPTO.hash_sh1);

    -- Append the salt to the resulting hash and tag with "S:" prefix
    RETURN 'S:' || RAWTOHEX(v_hash) || p_hex_salt;
END create_11g_password_hash;

D. 12c usage
The initial release of 12c (version 12.1.0.1) also used the 11g hashing algorithm. The first patch set for 12c, 12.1.0.2, introduced a newer and more secure algorithm.^[4]

IV. HASHING ALGORITHM AND PERSISTANCE FOR 12C (12.1.0.2 AND HIGHER)

A. Persistance
The usage of the SPARE4 column expands in 12.1.0.2 by including the 12c hash as well as the 11g hash.  The different hashes are identified by their prefixes (S for 11g, T for 12c) and delimited by semicolons.  Another hash type, for XDB, may also be included (“H”-prefixed) but it’s unrelated to normal user logins and as such, outside the scope of this paper.   18c, 19c, and 21c use the same algorithms and configuration.  Old 10g password hashes are still maintained in the PASSWORD column; but none of the versions are exposed through the DBA_USERS view.  The complexity of the 12c hashing algorithm results in a significantly larger hash string: 160 characters as seen in the wrapping below.  Due to the length of the hash strings, the code below will extract each of them individually rather than all in a single query row.

SQL> SELECT username, password, password_versions
  2    FROM dba_users
  3   WHERE username = 'TESTUSER';

USERNAME   PASSWORD   PASSWORD_VERSIONS
---------- ---------- ---------------------
TESTUSER              10G 11G 12C

SQL> SELECT password pwd_10g
  2    FROM sys.user$
  3   WHERE name = 'TESTUSER';

PWD_10G
----------------
AEB6397C8E7598A7

SQL> SELECT REGEXP_SUBSTR(spare4, 'S:[^;]+') pwd_11g
  2    FROM sys.user$
  3   WHERE name = 'TESTUSER';

PWD_11G
--------------------------------------------------------------
S:7233E3B91B45F6B813BCFFB5D8669167CB4F498D0642558A8A3BB39948C0

SQL> SELECT REGEXP_SUBSTR(spare4, 'T:[^;]+') pwd_12c
  2    FROM sys.user$
  3   WHERE name = 'TESTUSER';

PWD_12C
------------------------------------------------------------------------------------------------------------------------------------------------------------------
T:381A70048CBB5B531196CDD2CB51393E05E3FBFB0CB019DB39AB4AAB717BB23CA7FB2EA0AD4F60B34C38C9B8CF97BB0C6A4A7530362FBF23492FB02139442AB758645C9EA1D1E33C33CB9454D0468BF9

B. Algorithm
The 12c hashing algorithm has several improvements. First, the hashing itself is repeated 4096 times per the PBKDF2^[5] specification with SHA-512 as the HMAC function to derive the final key. Similar to the original 10g and lower algorithm there is a default hashing key or salt. Unlike the older version though, the default only makes up a portion of the full salt, the remainder coming from the 128-bit input salt parameter (represented as 32 hex digits.) There is no built-in PBKDF2 function exposed within the database, so the implementation below includes a version of it. Some of the options described in RFC2898 are declared as constants in this version because the usage does not vary within the password hashing rules.

Once the PBKDF2 key is derived, the password and derived key are then hashed again with the user’s salt with the SHA-512 method. Finally, as with the 11g method, this hash has the user’s salt appended to it and then written to the SYS.USER$.SPARE4 column. A “T” prefix is used to distinguish the 12.1.0.2 hashes from the 11g “S”-prefixed hashes. The 12C hashes are then identified within the PASSWORD_VERSIONS column of the DBA_USERS view.

Thus in the example above, the spare4 record can be split into pieces:
This the 11g password hash: 7233E3B91B45F6B813BCFFB5D8669167CB4F498D
This is the 11g salt: 0642558A8A3BB39948C0
This the 12c password hash:

381A70048CBB5B531196CDD2CB51393E05E3FBFB0CB019DB39AB4AAB717BB23CA7FB2EA0AD4F60B34C38C9B8CF97BB0C6A4A7530362FBF23492FB02139442AB7

This is the 12c salt: 58645C9EA1D1E33C33CB9454D0468BF9
C. Code
Oracle’s 12.1.0.2 functionality is replicated by the code below. Entering a password and a salt value will return the same value Oracle generates and stores in the SYS.USER$.SPARE4 column.

CREATE OR REPLACE FUNCTION create_12c_password_hash(
    p_password   IN VARCHAR2,
    p_hex_salt   IN VARCHAR2
)
    RETURN VARCHAR2
IS
    --                    .///.
    --                   (0 o)
    ---------------0000--(_)--0000---------------
    --
    --  Sean D. Stuber
    --  sean.stuber@gmail.com
    --
    --             oooO      Oooo
    --------------(   )-----(   )---------------
    --             \ (       ) /
    --              \_)     (_/

    -- derived from notes by https://www.trustwave.com/Resources/SpiderLabs-Blog

    c_dummy_salt   VARCHAR2(44) := RAWTOHEX(UTL_RAW.cast_to_raw('AUTH_PBKDF2_SPEEDY_KEY'));

    v_key          RAW(64);
    v_hash         RAW(80);

    FUNCTION pbkdf2(p_password IN VARCHAR2, p_salt IN VARCHAR2)
        RETURN VARCHAR2
    IS
        -- Simplified implementation of algorithm described in section 5.2 of RFC2898
        -- https://tools.ietf.org/html/rfc2898
        -- Iterations and key length are constant for this embedded version

        c_dk_length      CONSTANT PLS_INTEGER := 64;
        c_interations    CONSTANT PLS_INTEGER := 4096;

        -- hLen refers to number of octets (bytes) returned from HMAC function
        -- for SH512, that length is 64
        c_hmac           CONSTANT PLS_INTEGER := DBMS_CRYPTO.hmac_sh512;
        c_hlen           CONSTANT PLS_INTEGER := 64;

        c_octet_blocks   CONSTANT PLS_INTEGER := CEIL(c_dk_length / c_hlen);

        v_raw_password            RAW(32767) := UTL_RAW.cast_to_raw(p_password);
        v_raw_salt                RAW(64) := HEXTORAW(p_salt);

        v_u                       RAW(32767);
        v_f_xor_sum               RAW(32767);
        v_t_concat                RAW(32767) := NULL;
        v_block_iterator          PLS_INTEGER := 1;
    BEGIN
        -- 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_dk_length)
        LOOP
            -- 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(v_raw_salt, UTL_RAW.cast_from_binary_integer(v_block_iterator, UTL_RAW.big_endian));

            v_u := DBMS_CRYPTO.mac(src => v_u, typ => c_hmac, key => v_raw_password);
            v_f_xor_sum := v_u;

            FOR c IN 2 .. c_interations
            LOOP
                v_u := DBMS_CRYPTO.mac(src => v_u, typ => c_hmac, key => v_raw_password);
                v_f_xor_sum := UTL_RAW.bit_xor(v_f_xor_sum, v_u);
            END LOOP;

            v_t_concat := UTL_RAW.CONCAT(v_t_concat, v_f_xor_sum);
            v_block_iterator := v_block_iterator + 1;
        END LOOP;

        RETURN RAWTOHEX(UTL_RAW.SUBSTR(v_t_concat, 1, c_dk_length));
    END;
BEGIN
    -- The salt can be any value that is exactly 16 bytes (128 bits)
    -- i.e. the input salt must be 32 hex characters.
    IF NVL(LENGTH(p_hex_salt),0) != 32
    THEN
        raise_application_error(-20001, 'Salt must be 32 hex digits (0-9,A-F)');
    END IF;

    -- Generate key from the password using an extended salt
    v_key := pbkdf2(p_password, p_hex_salt || c_dummy_salt);

    -- Hash the key concatenated with the salt
    v_hash := sys.DBMS_CRYPTO.hash(HEXTORAW(v_key || p_hex_salt), sys.DBMS_CRYPTO.hash_sh512);

    -- Append the salt to the resulting hash and tag with "T:" prefix
    RETURN 'T:' || RAWTOHEX(v_hash) || p_hex_salt;
END create_12c_password_hash;

V. ASSIGNING PASSWORDS BY HASH

A. Single Version Assignment
The Oracle database supports a well-known; but not officially documented syntax to assign password hashes directly. Normal password assignment is done via ALTER USER username IDENTIFIED BY password. If you have valid password hashes though you can use ALTER USER username IDENTIFIED BY VALUES hashstring. So, in 10g or lower a password is assigned normally like this:

alter user testuser identified by testpwd;

A 10g hash may be assigned directly as follows:

alter user testuser identified by values 'AEB6397C8E7598A7';

The same syntax maybe used for 11g or 12c password hashes too. An 11g hash would look something like this:

alter user testuser identified by values 'S:7233E3B91B45F6B813BCFFB5D8669167CB4F498D0642558A8A3BB39948C0';

A 12c hash would be assigned the same way. Note, the text wrapping is due to page width limitations. The hash value is a single line.

alter user testuser identified by values 'T:381A70048CBB5B531196CDD2CB51393E05E3FBFB0CB019DB39AB4AAB717BB23CA7FB2EA0AD4F60B34C38C9B8CF97BB0C6A4A7530362FBF23492FB02139442AB758645C9EA1D1E33C33CB9454D0468BF9';

B. Multiple Version Assignment
The assignment syntax above is fine for users with a single type of password hash; but if a user’s PASSWORD_VERSIONS has multiple values it will remove the unassigned values. To assign multiple hash versions simultaneously, the hashes should be concatenated as a single string; delimited by semicolons (;). The ordering of the hashes does not matter. The database will split the 10g hash into the SYS.USER$.PASSWORD column and will arrange the 11g hash to precede the 12c hash in the SPARE4 column if both are present. If used, the XDB hash mentioned earlier^[IV.A] may also be included into the concatenated string.

In the multi-hash example below, the passwords are concatenated with this order: ‘12c;10g;XDB;11g’ but could be rearranged to any order. Again, the hash string should have no line breaks. The text wrapping is due to page width limits.

alter user testuser identified by values 'T:381A70048CBB5B531196CDD2CB51393E05E3FBFB0CB019DB39AB4AAB717BB23CA7FB2EA0AD4F60B34C38C9B8CF97BB0C6A4A7530362FBF23492FB02139442AB758645C9EA1D1E33C33CB9454D0468BF9;AEB6397C8E7598A7;H:55C984560887F4CE3A0F926B2A50C7DC;S:7233E3B91B45F6B813BCFFB5D8669167CB4F498D0642558A8A3BB39948C0';

VI. CASE SENSITIVITY

As noted above the 10g hashing doesn’t support case sensitive passwords; but in 11g the new algorithm can support them if the database has the feature enabled.   Case sensitivity is enabled and disabled with the system parameter sec_case_sensitive_logon. If TRUE then passwords will support case sensitivity (using 11g/12c hashing), if FALSE then they won’t.  The default value is TRUE.  In 12c through19c, the parameter is deprecated but still supported.  In a multi-tenant architecture the parameter can only be set at the container root level, not from within the pluggable databases. 

SQL> show parameters sec_case;

NAME                                 TYPE        VALUE                                                                                                
------------------------------------ ----------- ------------------------------                                                                       
sec_case_sensitive_logon             boolean     TRUE   
                                                                                              
SQL> alter user testuser identified by testpwd;

User altered.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD             PASSWORD_VERSIONS                                                                                                     
---------- -------------------- --------------------                                                                                                  
TESTUSER                        10G 11G                                                                                                               

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';


NAME       PASSWORD         SPARE4                                                                                                                
---------- ---------------- ------------------------------------------------------------                                       
TESTUSER   AEB6397C8E7598A  S:17F9149EFD0BDD9DBA305D6910D5928640F7727B29F261D851C58D37FA9A                                                        

SQL> connect testuser/testpwd;
Connected.

In the following example the TESTUSER account has the password changed from “testpwd” to “TestPwd”. And we use the identified by values clause to ensure we only have a 10g hash. First we examine the status of the parameter then change password and test the user’s login. Next confirm the mixed case password is observed based on the V$PARAMETER setting shown above.

SQL> alter user testuser identified by values 'AEB6397C8E7598A7';

User altered.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD             PASSWORD_VERSIONS                                                                                                     
---------- -------------------- --------------------                                                                                                  
TESTUSER                        10G                                                                                                                   

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME       PASSWORD             SPARE4                                                                                                                
---------- -------------------- ---------------------------------------------------------------                                                       
TESTUSER   AEB6397C8E7598A7                                                                                                                           

SQL> connect testuser/testpwd;
Connected.
SQL> connect testuser/TestPwd;
Connected.
SQL> connect dbauser/dbapassword;
Connected.
SQL> alter user testuser identified by values 'S:C7C0B0D97F60CA87C0CEB1663522C76509BD2FF84624774EAED94982A453';

User altered.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD             PASSWORD_VERSIONS                                                                                                     
---------- -------------------- --------------------                                                                                                  
TESTUSER                        11G                                                                                                                   

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME       PASSWORD             SPARE4                                                                                                                
---------- -------------------- ---------------------------------------------------------------                                                       
TESTUSER                        S:C7C0B0D97F60CA87C0CEB1663522C76509BD2FF84624774EAED94982A453                                                        

SQL> connect testuser/testpwd;
ERROR:
ORA-01017: invalid username/password; logon denied 


Warning: You are no longer connected to ORACLE.
SQL> connect testuser/TestPwd;
Connected.

In the next example the password will be changed from lower case to mixed case. Notice the 10g hash in the PASSWORD field of USER$ will not change; but the SPARE4 hash will change. So, while the 10g hash is preserved it is not valid for use while case-sensitivity is enabled and 11g hashed passwords are in effect. However, if the 10g hash is the only hash available, then the sec_case_sensitive_logon parameter doesn’t apply for that user.

After confirming the correct observation of case-sensitivity, the 11g hash is removed, leaving only the 10g hash. Case sensitivity is still; but the user is able to logon with different cases.

SQL> connect dbauser/dbapassword
Connected.
SQL> alter user testuser identified by testpwd;

User altered.

SQL> select password,spare4 from sys.user$ where name = 'TESTUSER';

PASSWORD                       SPARE4
------------------------------ --------------------------------------------------------------
AEB6397C8E7598A7               S:34391551DB0AADE86B6A1A7263B8F217C8B65FF66E4829BA7AA0B846653E

SQL> alter user testuser identified by TestPwd;

User altered.

SQL> select password,spare4 from sys.user$ where name = 'TESTUSER';

PASSWORD                       SPARE4
------------------------------ --------------------------------------------------------------
AEB6397C8E7598A7               S:777A9BE09BBAF3C7D3AD7D964471C15B948FB710870627D38B84EA9668EF

SQL> connect testuser/testpwd
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> connect testuser/TestPwd
Connected.
SQL> connect dbauser/dbapassword
Connected.
SQL> alter user testuser identified by values 'AEB6397C8E7598A7';

User altered.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME                       PASSWORD                       PASSWORD
------------------------------ ------------------------------ --------
TESTUSER                                                      10G

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME               PASSWORD                       SPARE4
------------------ ------------------------------ -----------------------------------
TESTUSER           AEB6397C8E7598A7

SQL> show parameters sec_case;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> connect testuser/testpwd
Connected.
SQL> connect testuser/TestPwd
Connected.

An interesting security quirk arises if case sensitivity is disabled when there is no 10g hash. If this happens then the user will be locked out because the 10g hash is the only hash used (or could be used) for case insensitive passwords. In the example below the testuser still has the “TestPwd” password as above, but will not be able to log in because case sensitivity is disabled.

SQL> connect dbauser/dbapassword
Connected.
SQL> alter user testuser identified by values 'S:C7C0B0D97F60CA87C0CEB1663522C76509BD2FF84624774EAED94982A453';

User altered.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME                       PASSWORD                       PASSWORD
------------------------------ ------------------------------ --------
TESTUSER                                                      11G

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME            PASSWORD               SPARE4
--------------- ---------------------- --------------------------------------------------------------
TESTUSER                               S:C7C0B0D97F60CA87C0CEB1663522C76509BD2FF84624774EAED94982A453

SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.

SQL> show parameters sec_case;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL> connect testuser/testpwd
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect testuser/TestPwd
ERROR:
ORA-01017: invalid username/password; logon denied

Starting with 21c, the sec_case_sensitive_logon parameter is desupported. In effect, it is always TRUE.  The show parameter statements above will return nothing in a 21c database and attempts to set the value will return an error – thus producing output as shown below.

SQL> show parameters sec_case;
SQL>

SQL> alter system set sec_case_sensitive_logon=FALSE;
alter system set sec_case_sensitive_logon=FALSE
*
ERROR at line 1:
ORA-25138: SEC_CASE_SENSITIVE_LOGON initialization parameter has been made obsolete

VII. SQLNET.ORA PASSWORD VERSION RULES

In 11gR1 the SQLNET.ORA file gained a new^* parameter: SQLNET.ALLOWED_LOGON_VERSION. While this parameter didn’t directly affect password hashing; it could create versioning incompatibilities, resulting in a user with a valid password being unable to log in. That is, if the parameter was set to 10 or 11, then only 10g password hashes are usable without applying a Critical Patch Update to the client. With the CPU, 10g or 11g hashes would be usable. If the parameter is set to 12, then only the 11g hash would work as only the 11g protocol is supported.

12cR1 extended this functionality by splitting the parameter into two: SQLNET.ALLOWED_LOGON_VERSION_CLIENT and SQLNET.ALLOWED_LOGON_VERSION_SERVER. The “client” parameter controls what protocols are supported when connecting as a client. The “server” parameter controls what protocols are supported when receiving connections as a server. In both cases, the versioning will impact which hashing versions will be usable when authenticating. 10 or lower will support any of the 3 password hashing algorithms. 11 will only support 11g and 12c hashes. Setting the parameter to 12 can a bit misleading because it will force 12c clients but still allow 11g hashes. To enforce the use of the 12c hashing, the parameter must be set to 12a.
As previously noted, beginning with 12cR1, the database parameter sec_case_sensitive_logon was deprecated in favor of using the SQLNET.ORA parameters to control which hashing algorithms to support.

In 21c the default value for SQLNET.ALLOWED_LOGON_VERSION_SERVER is 12.  This corresponds with the desupport of sec_case_sensitive_logon and forces all users to use case-sensitive password hashing algorithms.  It is only a default though; if set to a lower level then the old 10g hashes will still be supported.

* In 10gR1, Oracle introduced the parameter SQLNET_ALLOWED_LOGON_VERSION that was later replaced by SQLNET.ALLOWED_LOGON_VERSIONS. However, as there was only one password hashing algorithm available, that parameter had no bearing on the password hashing usage.

VIII. SPECIAL FORMATS

Some users have no hash at all but are instead stored with fixed values.

ANONYMOUS – SYS.USER$.PASSWORD has a value of exactly 16 spaces.  The SPARE4 column is NULL.  Since the value is not the result of a hash, it is not possible to log in with user directly as no password will hash this value.  If read from the DBA_USERS view, the PASSWORD_VERSIONS value is NULL.  It is possible to create such a user of your own, using the IDENTIFIED BY VALUES clause of exactly 16 spaces; but it is not particularly useful to do so.  Perhaps in a pre-18 db it could be used to create a schema-only account since it could not be used for log in.

XS$NULL – This account isn’t a real user.  According to the Oracle documentation it is “An internal account that represents the absence of a user in a session.”  The SYS.USER$.PASSWORD value is NULL.  The SPARE4 value is an 11g placeholder of “S:” followed by 60 space characters.

18c introduced a new type of user called a “Schema Only” user.  Meaning it has no direct login.  The intent being these accounts will own objects.  They may not be used for users or applications to log in, thus providing an extra layer of isolation and security.  The SYS.USER$.PASSWORD value is NULL, the SPARE4 value contains 11g and 12c hash place holders consisting of only zeroes and a salt.  Thus “S:” followed by 40 zeroes and 20 hex-characters for the salt.  Then “T:” followed by 128 zeroes and a 32-character hex salt.

The Multi-Tenant option also introduces special password formats.  Within the CDB$ROOT, Common Users will follow standard hashing rules and storage.  When viewed from the Pluggable DBs though, the common users will have a NULL value for SYS.USER$.PASSWORD.  The SPARE4 value will have “S:“ and “T:” placeholders but the values will consist entirely of space characters – 60 spaces for the 11g value and 160 spaces for the 12c value.

IX. SETUP

As stated above, this article is not a how-to for hacking Oracle user accounts. The demonstrations show a user’s password being changed. However, those changes presuppose a DBA user exists with the necessary privileges already granted via legitimate means. The following commands, if run by SYS, will create such a user; as well as the corresponding test user used in the examples above.

create user dbauser identified by dbapassword;
grant create session to dbauser;
grant select on dba_users to dbauser;
grant select on sys.user$ to dbauser;
grant select on sys.v_$parameter to dbauser;
grant alter user to dbauser;
grant alter system to dbauser;

create user testuser identified by testpwd;
grant create session to testuser;

X. CAVEATS

While a DBA may have the ability or even the authority to change a user’s password does not mean he or she should do so, as there may be undesirable consequences.

• If the user has a profile that doesn’t allow password reuse, the DBA might not be allowed to restore the old password after changing it. The DBA may have authority to change profiles to allow it, or alter the user to a different profile then restore the profile.
• If the user’s password has an expiration lifetime, then resetting the hash value will reset the counter. Thus allowing that password to stick around longer than it normally would be allowed.
• If the original hash is lost, mistyped, cut-and-pasted incorrectly, or otherwise corrupted then that user will not be able to log in until a new password is created.
• Last, and most obvious, following the steps above allows a DBA to impersonate another user. Doing so may violate the company’s security/ethics policies. Proper auditing and authorization can ameliorate these issues.

REFERENCES

1. SQL Language Reference
   https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/index.html
2. PL/SQL Packages and Types Reference
   https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/index.html
3. Xiaoyun Wang, Yiqun Lisa Yin, Hongbo Yu, (2005, February 13) “Collision Search Attacks on SHA1”
   http://courses.csail.mit.edu/6.885/spring05/papers/wangyinyu.pdf
4. Martin Rakhmanov, (2015, June 1) Changes in Oracle Database 12c password hashes
   https://www.trustwave.com/Resources/SpiderLabs-Blog/Changes-in-Oracle-Database-12c-password-hashes
5. RSA Laboratories, (2000, September) Password-Based Cryptography Specification
   https://tools.ietf.org/html/rfc2898

Sean D. Stuber is a DBA and developer with over 25 years of experience, the majority of it working with Oracle databases. His main responsibilities are coding, tuning, and mentoring other developers in how to best utilize their Oracle databases. He has presented multiple times at the Collaborate conference for IOUG as well as the KScope conference for ODTUG. He is a Topic Advisor and regular contributor for Oracle and database related questions on Experts-Exchange (www.experts-exchange.com). Sean can be reached at sean.stuber@gmail.com.

A pdf of this article can be downloaded from my dropbox here.