PASSWORD not available from oracle 11g in DBA_USERS view

ALTER USER IDENTIFIED BY VALUES with Oracle 11g

Oracle 11g introduces case-sensitive passwords for databases created with the default Oracle Database 11g enhanced security. With the new initialization parameter SEC_CASE_SENTITIVE_LOGON,
it’s possible to enable or disable password case sensitivity in the database. The default value of this parameter is TRUE:

SQL> SELECT value FROM v$parameter WHERE name = ‘sec_case_sensitive_logon’;
VALUE
—————
TRUE

When we issue a CREATE/ALTER USER IDENTIFIED BY PASSWORD command, both the insensitive and the sensitive password hashes are saved (but the user’s password is sensitive). The two hashes are stored in the SYS.USER$ view (not into the DBA_USERS view):

SQL> CREATE USER anilve IDENTIFIED BY anilve;
User created.

SQL> GRANT connect, resource TO anilve;

Grant succeeded.

SQL> CONNECT anilve/Anilve
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> CONNECT anilve/anilve
Connected.

In the DBA_USERS there is a column, PASSWORD_VERSIONS, which tells us the current version of the password of a particular user. The default version is “10g 11g”:

SQL> SELECT password_versions FROM dba_users WHERE username = ‘ANILVE’ AND password IS NULL;

PASSWORD
——–
10G 11G

If we set the SEC_CASE_SENSITIVE_LOGON parameter to FALSE, both uppercase and lowercase passwords work:

SQL> ALTER SYSTEM SET sec_case_sensitive_logon = FALSE;
System altered.

SQL> CONNECT anilve/ anilve
Connected.
SQL> CONNECT anilve/Anilve
Connected.

In the SYS.USER$, the two hashes are stored in the two column PASSWORD (insensitive password) and SPARE4 (sensitive password):

SQL> SELECT password, spare4 FROM SYS.USER$ WHERE name = ‘ANILVE’;

PASSWORD SPARE4
——————– —————————————————————–
3AA2DE27F5F4B2B5 S:C27DFCE226D1D5EC47FAADE93E8588E5A064BC6E3D1E0BCD2B58E65F083F

If we want to change a password with the clause IDENTIFIED BY VALUES we can use one of the two values, or both. The consequence is that the version of the password will change.
When only the hash of the insensitive password only is used as a value, the password is case insensitive whatever the setting of sec_case_sensitive_logon is:

SQL> ALTER USER anilve IDENTIFIED BY VALUES ’ 3AA2DE27F5F4B2B5′;

User altered.

SQL> SELECT password_versions FROM dba_users WHERE username = ‘ANILVE’;

PASSWORD
——–
10G

SQL> CONNECT anilve/anilve
Connected.
SQL> CONNECT anilve/Anilve
Connected.

When only the hash of the sensitive password only is used as a value, the password is case sensitive and if the setting of SEC_CASE_SENSITIVE_LOGON is on false, the login will always failed because there is no insensitive hash value in the PASSWORD column:

SQL> ALTER USER anilve IDENTIFIED BY VALUES ‘S:C27DFCE226D1D5EC47FAADE93E8588E5A064BC6E3D1E0BCD2B58E65F083F′;

User altered.

SQL> SELECT password_versions FROM dba_users WHERE username = ‘ANILVE’;

PASSWORD
——–
11G

SQL> ALTER SYSTEM set sec_case_sensitive_logon = FALSE;

System altered.

SQL> CONNECT anilve/anilve
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> CONNECT anilve/Anilve
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

If we use both hashes, the password version will be the same and the the sensitivity of the password is related to the value of the SEC_CASE_SENSITIVE_LOGON parameter:

SQL> ALTER USER anilve IDENTIFIED BY VALUES ‘S:C27DFCE226D1D5EC47FAADE93E8588E5A064BC6E3D1E0BCD2B58E65F083F; 3AA2DE27F5F4B2B5′;

User altered.

SQL> SELECT password_versions FROM dba_users WHERE username = ‘ANILVE’;

PASSWORD
——–
10G 11G

The following table is a summary of the password’s sensitivity with Oracle 11g: