8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

Case Sensitive Passwords in Oracle Database 11g Release 1

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:

For more information see:

Hope this helps. Regards Tim...

Back to the Top.