Case Sensitive Passwords and Strong User Authentication

With 11g R1 Oracle introduced case sensitive passwords for database accounts based on the SHA1 hash algorithm. This feature can easily be enabled with the init.ora parameter SEC_CASE_SENSITIVE_LOGON. As soon as this parameter is set to true, all new passwords will be case sensitive. Existing passwords will remain case insensitive until they are changed.
The downside of this new feature is, that the passwords are also stored with the pre-11g database password hash. This is a potential security leak. The pre-11g password hash string from USER$ can be used to crack the case insensitive version of the password. All kind of tools, utilities, password lists etc are available to do this. As soon as the case insensitive version of the password is known, the case sensitive password can be guessed.

Case Sensitiv Passwords

First of all lets have a look at the parameter of an 11g R2 test database.

SHOW parameter sec_case_sensitive_logon

NAME                      TYPE     VALUE
------------------------- -------- ---------
sec_case_sensitive_logon  BOOLEAN  TRUE

The Column PASSWORD_VERSIONS in DBA_USERS shows the Database version in which the password was created or changed. The user TEST_10G shows only 10g which means that this user has been created before the database has been migrated to 11g and was never changed.

SELECT username, password_versions
FROM dba_users
WHERE username LIKE 'TEST%';

USERNAME        PASSWORD
--------------- --------
TEST            10G 11G
TEST_11G        10G 11G
TEST_10G        10G

The Password hashes for both the 11g (SPARE4) and pre-11g hashes (PASSWORD)

SET linesize 120
col name FOR a10
col password FOR a16
col spare4 FOR a50
SELECT name,password,spare4 FROM USER$ WHERE name LIKE 'TEST%';

NAME       PASSWORD         SPARE4
---------- ---------------- --------------------------------------------------
TEST       7A0F2B316C212D67 S:7D5C8604CDF7811E06DAA7C718ADB3684A883CE7521CF5C0
                            66721877D457
TEST_10G   48AFCE9CD794074D
TEST_11G   AE6FC028DF3997FC S:CFD77E59711BC61589C6631C1F824CFC0966972D01599EF6
                            ED1558A2046F

As you can see user TEST and TEST_11G have a pre-11g Hash and the long 11g Hash. The user TEST_10G only have a pre-11g Hash. This indicates that the user has been created before the database was migrated to 11g and the password never has been changed. Therefor the password for this user is case insensitive even when the parameter SEC_CASE_SENSITIVE_LOGON is set to true.

To enable or disable case sensitive passwords just alter the init.ora parameter.

ALTER system SET SEC_CASE_SENSITIVE_LOGON=TRUE scope=spfile;
ALTER system SET SEC_CASE_SENSITIVE_LOGON=FALSE scope=spfile;

Increase Security

The Idea

The security can be increase when case sensitive password are used and logon’s are limited to the 11g authentication protocols. This can be achievement by setting the sqlnet parameter SQLNET.ALLOWED_LOGON_VERSION to 11. As soon as this has been done the pre-11g hashes can be removed from USER$.

In detail the following steps are required to enable Oracle Database 11g exclusive mode and increase database security.

  • Make sure all application’s and clients are supporting the 11g authentication protocols
  • After migration / database creation enable SEC_CASE_SENSITIVE_LOGON
  • Change passwords on all database accounts. A minimum of 10 alphanumeric characters, special characters and mixed case is recommended
  • Verify and test batch jobs or scripts to make sure the use of mixed chases is consistent
  • Edit the sqlnet.ora parameter file and add the line SQLNET.ALLOWED_LOGON_VERSION=11 or change it to 11
  • Remove the old password hash values from USER$

Remove the old password hash

To remove the old password hash values it is necessary to perform an update on a SYS table. Therefor a full backup of the database is highly recommended. As soon as you have a complete backup, connect to the Oracle Database with SYSDBA privileges to perform the following update.

UPDATE sys.USER$ SET password=NULL;
DELETE FROM user_history$;
commit;

From now the password column will be empty even when new user are created as you can see in the following example.

SELECT name,password,spare4 FROM USER$ WHERE name LIKE 'TEST%';
NAME       PASSWORD          SPARE4
---------- ---------------- --------------------------------------------------
TEST                        S:4C15788E25102B7B70713D3BD054EF2731E9664CA4FCFD67
                            BA224E15C5CA
TEST_10G                    S:35547B514FCA0C895259A4CAD1E50A88AFE086B9844ACF97
                            A6B9E211490B
TEST_11G                    S:CDE994528EF0CED53917790F412C1CE4280A05E5007CC0E1
                            0407337B407
CREATE USER test_new IDENTIFIED BY Test001;

USER created.

SELECT name,password,spare4 FROM USER$ WHERE name LIKE 'TEST%';

NAME       PASSWORD         SPARE4
---------- ---------------- --------------------------------------------------
TEST                        S:4C15788E25102B7B70713D3BD054EF2731E9664CA4FCFD67
                            BA224E15C5CA
TEST_10G                    S:35547B514FCA0C895259A4CAD1E50A88AFE086B9844ACF97
                            A6B9E211490B
TEST_11G                    S:CDE994528EF0CED53917790F412C1CE4280A05E5007CC0E1
                            0407337B407A
TEST_NEW                    S:D4808E96184DE35110A5CDFC83A7C496402147338C77185D
                            336748914299

Conclusion

The sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION provides an easy way to increase the overall database password security for 11g environments (Database and Client / Applications), but it does not replace a well-defined password policy. If user have simple passwords like username=password they still can easily be guessed.

Reference

Metalink Notes related to Case Sensitive Passwords and the Oracle Database 11g Exclusive Mode.

  • 11g R1 New Feature: Case Sensitive Passwords and Strong User Authentication [429465.1]
  • Instructions for Clearing pre-11g Database Password Hashes [463999.1]