Password case sensitivity is a new Oracle 11g feature. This is an additional password complexity as well as a security feature. sec_case_sensitive_login is an initialization parameter which controls password case sensitivity. This is a dynamic parameter.
Default value is TRUE ie, once your 11g upgrade from previous versions is complete you will have this feature enabled in your database. After upgrade existing user passwords will remain case-insensitive as in previous versions until the passwords are changed.
Password case sensitivity is disabled by setting sec_case_sensitive_login to FALSE.
ALTER SYSTEM set sec_case_sensitive_logon = false;
To check if our password is case sensitive or not in 11g database, we use the below query,
SELECT username, password_versions FROM dba_users;
HIMI 10G 11G
Value 10g in password versions column says they are not case sensitive yet in 11g (because they have not been changed).
10g11g means it is a case sensitive password.
While upgrading the database with DBCA, the sec_case_sensitive_login parameter will be added to your SPFILE with a TRUE setting. It also allows us to revert these settings back as in previous version during database creation.
Note: 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.
Case-sensitive passwords for privileges SYSDBA and SYSOPER users can be enabled by modifying the orapwd file. Ignorecase parameter should be set to Y to enable password case.
$ orapwd file=orapwDB11Gb entries=100 ignorecase=y password=mypassword
When these SYS users are imported from a previous version their passwords are included in the password file. These users will retain case insensitive passwords until the password is modified.
After 11g upgrade, the database links whose passwords are also case sensitive may give some issues:
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.
The scripts with passwords stored in lowercase needs to be modified accordingly before 11g upgrade. Else the script may throw errors. We have to be very careful while implementing this new feature.
Article by Divya