Password Case Sensitivity
Database parameter to turn it on and off: sec_case_sensitive_logon
To view password hashes:
select name, password,spare4 from user$
To view what type of hashes are in use, but not the hashes themselves:
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;
The users having password_version=10g 11g, means they are imported from 10g and they have modified their passwords after being imported to 11g.
If we enable case sensitivity (sec_case_sensitive_logon TRUE), authentication is done against the mixed case 11g password.
If we disable case sensitivity (sec_case_sensitive_logon FALSE), authentication is done against the 10g case insensitive password
When only the 11g oracle hash is used as a value, the password is case sensitive and if the setting of sec_case_sensitive_logon is false, the login fails as there is no 10g string. This would be most secure.
When only the 10g oracle hash is used as a value, the password is case insensitive whatever the setting of sec_case_sensitive_logon is.
When using both hashes, switching back and forth to 11g mechanism is possible.
When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved.
When issuing a create user identified by values, you can choose if you want to have both passwords, only the case insensitive or only the case sensitive.
Security can be increased when case sensitive password are used AND logon’s are limited to the 11g authentication protocols.
Oracle calls this "EXCLUSIVE MODE".
"You optionally can configure Oracle Database to run in exclusive mode for Release 11 or later. When you enable exclusive mode, then Oracle Database uses the new SHA-1 hashing algorithm exclusively. Oracle Database 11g exclusive mode is compatible with Oracle Database 10g and later products that use OCI-based drivers, including SQL*Plus, ODBC, Oracle .NET, Oracle Forms, and various third-party Oracle Database adapters. However, be aware that exclusive mode for Release 11g is not compatible with JDBC type-4 (thin) versions earlier than Oracle Database 11g or Oracle Database Client interface (OCI)-based drivers earlier than Oracle Database 10g. After you configure exclusive mode, Oracle recommends that you remove the old password hash values from the data dictionary"http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#CHDEFIHB
This can be achievement by setting the sqlnet parameter SQLNET.ALLOWED_LOGON_VERSION to 11 and removing the 10g password hashes from USER$.
The parameter actually specifies the AUTHENTICATION PROTOCOL (for example SHA-1) that a client is allowed to use, NOT the actual VERSION of that client (for example 10): even though the parameter value might be '10', the internal check is against the authentication protocol 'SHA-1'. (How To Use the Parameter SQLNET.ALLOWED_LOGON_VERSION Correctly [ID 1304142.1])
Oracle 10g and 11g both use the SHA-1 protocol. Oracle 12g will use the SHA-2 protocol.
The process is documented here:
Ensuring Against Password Security Threats by Using the SHA-1 Hashing Algorithm
Instructions for Clearing pre-11g Database Password Hashes [ID 463999.1]
11g R1 New Feature : Case Sensitive Passwords and Strong User Authentication [ID 429465.1]
Case Sensitive Passwords and Strong User Authentication | OraDBA
How To Use the Parameter SQLNET.ALLOWED_LOGON_VERSION Correctly [ID 1304142.1]
Oracle native authentication version 9i and 10g
Downgrading the Oracle Native Authentication
SecuriTeam - Downgrading the Oracle Native Authentication