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 marco IDENTIFIED BY marco;
User created.

SQL> GRANT connect, resource TO marco;

Grant succeeded.

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

Warning: You are no longer connected to ORACLE.

SQL> CONNECT marco/marco
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 = ‘MARCO’ 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 marco/marco
Connected.
SQL> CONNECT marco/Marco
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 = ‘MARCO’;

PASSWORD SPARE4
——————– —————————————————————–
8F47D92B6B3C41D4 S:E00F1A202A3FEB441FC7029A57BDD2B406DE13AD04A671BA61EA925453F4

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 marco IDENTIFIED BY VALUES ‘8F47D92B6B3C41D4’;

User altered.

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

PASSWORD
——–
10G

SQL> CONNECT marco/marco
Connected.
SQL> CONNECT marco/Marco
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 marco IDENTIFIED BY VALUES ‘S:E00F1A202A3FEB441FC7029A57BDD2B406DE13AD04A671BA61EA925453F4’;

User altered.

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

PASSWORD
——–
11G

SQL> ALTER SYSTEM set sec_case_sensitive_logon = FALSE;

System altered.

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

Warning: You are no longer connected to ORACLE.
SQL> CONNECT marco/Marco
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 marco IDENTIFIED BY VALUES ‘S:E00F1A202A3FEB441FC7029A57BDD2B406DE13AD04A671BA61EA925453F4;8F47D92B6B3C41D4’;

User altered.

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

PASSWORD
——–
10G 11G

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