Oracle Case-Sensitive Passwords

In Oracle Database prior to release 11g, user passwords were case insensitive. For example:

SQL> conn scott/tiger
Connected.
SQL> conn scott/TIGER
Connected.

This arrangement presents a problem for standards such as the Payment Card Industry (PCI) Data Security Standard, which require passwords to be case sensitive.

Problem solved; in Oracle Database 11g passwords can be case sensitive as well. While creating the database via DBCA, you will be prompted whether you want to upgrade to the "new security standards," one of which is the case-sensitive password. If you accept, passwords will be recorded in the same case as they were created. Here is the resulting behavior, assuming you have accepted the new standard:

SQL> conn scott/tigger
Connected.
SQL> conn scott/TIGGER
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Note how "tigger" and "TIGGER" are treated differently.

Now, some of your apps may not be passing the password in proper case right now. A typical example is a user input form: Many forms accept passwords with no case conversion being performed. However, with Oracle Database 11g, that login may fail unless the user enters the password in case-sensitive format or the developer changes the app to convert to upper or lower case (which may not be possible quickly).

If you wish, however, it is still possible to revert to case insensitivity by altering a system parameter, SEC_CASE_SENSITIVE_LOGON, as shown in the example below.

SQL> conn / as sysdba
Connected.
SQL>  alter system set sec_case_sensitive_logon = false;

System altered.

SQL> conn scott/TIGER
Connected.     

When you upgrade an existing Oracle 10g database to 11g, you can migrate your passwords to the new standard. You can check the status of the password by querying the DBA_USERS view, especially the new column PASSWORD_VERSIONS.

select username, password, password_versions
from dba_users;

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

The first thing you notice is that the password column is NULL, not populated with the hashed value as it is in Oracle Database 10g and prior versions. So what happened to the password? It's still stored in the database (in the table USER$) but it is not visible in the DBA_USERS view. When the user is created as either global or externally authenticated, the status is indicated—GLOBAL or EXTERNAL—but the hash value of the password is not displayed.

Next, note the column PASSWORD_VERSIONS, which is new in Oracle Database 11g. This column signifies the case sensitivity of the password. The value "10G 11G" signifies that the user was either created in 10g and migrated to 11g or created in 11g directly.