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 following code demonstrates the case sensitive password functionality. First, it resets the SEC_CASE_SENSITIVE_LOGON initialization parameter to TRUE and creates a new user with a mixed case password.
CONN / AS SYSDBA ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE; CREATE USER test2 IDENTIFIED BY Test2; GRANT CONNECT TO test2;
We can see the case sensitive password functionality in operation if we attempt to connect to the new user with both the correct and incorrect case password.
SQL> CONN test2/Test2 Connected. SQL> CONN test2/test2 ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL>
By switching the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE we are able to connect using both variations of the password.
CONN / AS SYSDBA ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; SQL> CONN test2/Test2 Connected. SQL> CONN test2/test2 Connected. SQL>
The important thing to remember here 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.
CONN / AS SYSDBA ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; CREATE USER test3 IDENTIFIED BY Test3; GRANT CONNECT TO test3;
As you would expect, connection to the user is possible regardless of the case of the password.
SQL> CONN test3/Test3 Connected. SQL> CONN test3/test3 Connected. SQL>
If we enable case sensitivity, authentication is done against the mixed case password.
CONN / AS SYSDBA ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE; SQL> CONN test3/Test3 Connected. SQL> CONN test3/test3 ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL>
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 ------------------------------ -------- TEST 10G 11G SPATIAL_WFS_ADMIN_USR 10G 11G SPATIAL_CSW_ADMIN_USR 10G 11G APEX_PUBLIC_USER 10G 11G . . . SYSTEM 10G 11G SYS 10G 11G MGMT_VIEW 10G 11G OUTLN 10G 11G 32 rows selected. SQL>
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.