Oracle case sensitive passwords were introduced in version 11g of the database. These passwords can include a mixture of special characters and/or multibyte characters. Oracle 11g and later default to case sensitive passwords when you create a new user. The same is true when a user or DBA changes an existing password.
Oracle Case Sensitive Passwords
Strong Authentication
Oracle uses hashing to encrypt passwords. It’s the hashed value that’s stored in the database. Once Oracle hashes the password, it’s not possible to get the original text password back from it.
Oracle strengthened the algorithm used to hash case sensitive passwords in version 11g. The hashing now uses a 160 bit SHA-1 hashing algorithm for case sensitive passwords. Case insensitive passwords are still hashed using the old 3DES (Triple-DES) encryption algorithm.
Backwards Compatibility
When you migrate a database from a version older than 11g, Oracle keeps the original hash values.
This means the passwords will still be case insensitive until you change them.
You should change the passwords for all administrative accounts straight after migration.
You can use a password management policy to make end-users change passwords within a set time.
Disabling And Enabling Password Case Sensitivity
The SEC_CASE_SENSITIVE_LOGON database initialization parameter controls the use of case sensitive passwords.
For example, to turn off case sensitive passwords:
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
And to turn on case sensitivity:
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;
Only users with the SYSDBA or SYSOPER privileges can change this parameter. To check the current value for the parameter:
SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
Administration
Oracle added a new column PASSWORD_VERSIONS to the DBA_USERS view. This lets you see whether users have case sensitive passwords. For example,
SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where username = ‘TEST’;
USERNAME PASSWORD_VERSIONS -------- ----------------- TEST 10G 11G
In the above example, the value returned for the PASSWORD_VERSIONS column is “10G 11G”.
This means that both old and new-style hash values are available for that user.
Oracle stores the new hash value in the USER$.SPARE4 column.
If this column contains NULL it means the password hasn’t changed since database migration. That is, the user will still have their old case insensitive password.
This is regardless of whether you’ve turned on password case sensitivity. That is, it doesn’t matter whether the SEC_CASE_SENSITIVE_LOGON initialization parameter is set to TRUE.
Password File
Passwords stored in the password file are also case sensitive in 11g database and later. Password files also store both the new and old kinds of hash values as described above.
You control the password file case sensitivity using the ‘ignorecase’ argument. You specify this argument to the orapwd utility when creating the password file.
The default value is ‘n’ (no). So when you create a password file the SYS password will be case sensitive by default. If you want to change the case sensitivity, recreate the password file. When you recreate it, specify the ‘ignorecase’ parameter.
Database Links
From 11g onwards, database link passwords are also case sensitive by default. This may cause problems when connecting to older versions of Oracle using links.