Case Sensitive Passwords in Oracle Database 11g Release 1

Post date: 30-Dec-2009 09:05:39

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 ingnorecase=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.