Case Sensitive Passwords in 11g

Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation.

The SEC_CASE_SENSITIVE_LOGON initialization parameter gives control over case sensitive passwords. If existing applications struggle to authenticate against 11g, you can use the ALTER SYSTEM command to turn off this functionality.

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> 


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

SQL>

The important thing to remember is even when case sensitive passwords are not enabled, the original case of the password is retained so it can be used if case sensitivity is subsequently enabled. The following code disables case sensitivity and creates a new user with a mixed case password.

The DBA_USERS view includes a PASSWORD_VERSIONS column that indicates the database release in which the password was created or last modified.
Users imported from a 10g database have a PASSWORD_VERSIONS value of "10G" and maintain case insensitive passwords independent of the SEC_CASE_SENSITIVE_LOGON parameter setting. Their passwords become case sensitive as soon as they are changed, assuming the SEC_CASE_SENSITIVE_LOGON parameter is set to TRUE.

The ignorecase parameter of the orapwd utility allows control over case sensitivity of passwords in the password file. The default value is "n", meaning the passwords are case sensitive. When privileged users (SYSDBA & SYSOPER) are imported from a previous release their passwords are included in the password file. These users will retain case insensitive passwords until the password is modified.
To create case insensitive passwords in the password file, recreate the password file using the ignorecase=y option.

$ orapwd file=orapwDB11Gb entries=100 ignorecase=y password=mypassword

The passwords associated with database links are also case sensitive, which presents some issues when connecting between different releases:

• 11g to 11g: The database link must be created with the password in the correct case to match the remote users password.
• 11g to Pre-11g: The database link can be created with the password in any case as case is ignored by the remote database.
• Pre-11g to 11g: The remote user must have its password modified to be totally in upper case, as this is how it will be stored and passed by the Pre-11g database.

Note:
The password is not longer displayed in dba_users.password in 11g, You can use the following script to generate the password instead.

select 'alter user '||name||' identified by values '''||password||''';' from user$ where spare4 is null and password is not null
union
select 'alter user '||name||' identified by values '''||spare4||';'||password||''';' from user$ where spare4 is not null and password is not null;