Oracle password storage

Answer:  Staring in Oracle 11g, the Oracle the hash password is no longer visible in the in dba_users view, and instead, the hash password is stored inside the SYS.USER$ table in the column "password" and the spare4 column.

Also see my notes on saving encryptrd Oracle passwords.

Oracle ensures the security of this sys.user$ table, and you should never mess with any table owned by the SYS user.

The dba_users view includes a password_versions column that indicates the database release in which the password was created (or last modified).

SQL> SELECT username, password_versions FROM dba_users;

USERNAME PASSWORD
------------------------------ --------
 . . . .
SYSTEM 10G 11G
SYS 10G 11G
MGMT_VIEW 10G 11G
OUTLN 10G 11G

If your password_versions is 11g only then you will need to look in the sys.user$ spare4 column and you will see a much larger hex number. This is because Oracle has switched to the SHA-1 algorithm.

This means that there are different ways password can be set depending on whether  "password" and "spare4″ are set in SYS.USER$.

This new SHA-1 algorithm is more secure than the old, and unlike the 10g and lower hashing does not appear to use the username to generate the hash instead a new salt value is added which is stored in the last 20 characters of the SPARE4 hash. It is possible the salt is somehow derived from the username but Oracle has not documented this feature for obvious reasons.

If only "SPARE4″ is used to set the password the password version is 11g and in SYS.USER$ password value becomes NULL after ALTER statement

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

PASSWORD
——————————
SPARE4
——————————————————————————–
F894844C34402B67

If both password and spare4 is used to restore the password, the password_versions column is set to  "10G 11G", to set this the identified by values is passed in as "SPARE4;PASSWORD" from sys.user$:

SQL> alter user scott identified by values 'S:AEAEF0006791D6D6 . . . 12A0145B;F894844C34402B67′;

User altered.

SQL> select password_versions from dba_users where username = 'SCOTT';

PASSWORD
——–
10G 11G