Enable/Disable Case Sensitive Password Login - Oracle Database

Enable/Disable Case Sensitive Password Login in Oracle Database 11g

Enable/Disable Case Sensitive Password Login - Oracle Database

Oracle 11g introduced a new parameter to manage Case Sensitive Passwords since ‘s’ and ‘S’ and two different letters just like ‘apple’ and ‘APPLE’ and completely different from each other albeit the meaning is same for both the words. This article will show you how to manage Case Sensitive Passwords in Oracle Database 11g or greater versions of Oracle Database.

By default, I have SEC_CASE_SENSITIVE_LOGON set as TRUE in my Oracle database which means Case Sensitive Password Login is Enabled for my database.

SQL> show parameter case_sens

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------------------

sec_case_sensitive_logon             boolean     TRUE

I’ll create a user with Case Sensitive Password and test the login with different alternatives.

SQL> create user CASE1 identified by Case1;

User created.

SQL> grant connect to case1;

Grant succeeded.

SQL> conn case1@oradb11g/case1

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn case1@oradb11g/Case1

Connected.

Since the SEC_CASE_SENSITIVE_LOGON parameter in enabled, it will allow Database login with the same password which I provided the user at the time of creating it.

Now I’ll disable the parameter and then see what happens.

SQL> conn sys@oradb11g as sysdba

Enter password:

Connected.

SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.

SQL> create user case2 identified by Case2;

User created.

SQL> grant connect to case2;

Grant succeeded.

SQL> conn case2@oradb11g/case2

Connected.

SQL> conn case2@oradb11g/Case2

Connected.

Even if the parameter is disabled at the time of User Creation, still the database saves the original copy of the password so that if I enable the parameter again, it won’t allow me to login without exact match. See below,

SQL> conn sys@oradb11g as sysdba

Enter password:

Connected.

SQL> alter system set sec_case_sensitive_logon=TRUE;

System altered.

SQL> conn case2@oradb11g/case2

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn case2@oradb11g/Case2

Connected.

Note: In case if you are importing users from Oracle database 10g to 11g database, their passwords will remain Case Insensitive till the moment they are not changed after import. No Doubt it requires the SEC_CASE_SENSITIVE_LOGON parameter to be enabled at the time of Password Change.

I hope this helps !!