Oracle Case sensitive password - SEC_CASE_SENSITIVE_LOGON

From Oracle 11 passwords are configured as case sensitive by default. Oracle has provided SEC_CASE_SENSITIVE_LOGON initialization parameters to enables or disables password case sensitivity in the database. Value of SEC_CASE_SENSITIVE_LOGON can be between True or False where TRUE means Database logon passwords are case sensitive and FALSE means Database logon passwords are not case sensitive.

SEC_CASE_SENSITIVE_LOGON parameter can be directly modified by ALTER SYSTEM command, and comes in effect without bouncing the instance.

Reference: Oracle Documentation

Lets check following example to understand SEC_CASE_SENSITIVE_LOGON parameter better.

Step 1: Check value of SEC_CASE_SENSITIVE_LOGON parameter

C:\Users\nimish.garg>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 14 19:58:28 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production


SQL> show parameter sec_case_sensitive_logon;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

Step 2: Lets create a user and grant required privileges

SQL> create user testuser identified by MyPassword;
User created.

SQL> grant create session to testuser;
Grant succeeded.

Step 3: Lets try to connect user with case sensitive password, it should work.

SQL> conn testuser/MyPassword
Connected.

Step 4: Lets try to connect user with password in Lower Case

SQL> conn / as sysdba
Connected.

SQL> conn testuser/mypassword
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Oh, we failed to login because Password is configured as case sensitive and we ignored the case .

Step 5: Lets configure Case Sensitivity off for User Name and Password

SQL> conn / as sysdba
Connected.

SQL> show parameter sec_case_sensitive_logon;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> alter system set sec_case_sensitive_logon=false scope=both;
System altered.

SQL> show parameter sec_case_sensitive_logon
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

Step 6: Lets try to login again with password in lower-case. We do not need to bounce the instance to make the effect of sec_case_sensitive_logon parameter value.

SQL> conn testuser/mypassword
Connected.

WOW, It worked. Also We need to remember that even when case sensitive passwords are not enabled, Oracle retains the original case of the password so that it can be used if case sensitivity is enabled later.

Hope you have enjoyed reading this post. Looking forward for your valuable feedback.


Related Posts:
- ORA-01017: invalid username/password; logon denied
- Oracle Default Username and Password
- How to connect sqlplus without tnsnames.ora
- SQLNET: How does Oracle Client connect with Oracle Server