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

7 comments:

  1. Just as you are getting use to being able to control password case Oracle make the statement

    "The IGNORECASE argument of ORAPWD and the SEC_CASE_SENSITIVE_LOGON system parameter are deprecated in Oracle Database 12c. By default, passwords in Oracle Database 12c are case sensitive."

    Looks like case sensitive passwords are the future!

    ReplyDelete
  2. If you've upgraded from 10G, in 11G you'll see you have two hashed password values to choose from in SYS.USER$, PASSWORD and SPARE4. If you have not changed the password for an account since the upgrade and you issue an ALTER USER ... IDENTIFIED BY VALUES ... go with the PASSWORD column. SPARE4 may not represent exactly the same password.

    Jon Tout
    Oracle Contractor at QVC

    ReplyDelete
  3. thanks..great work

    ReplyDelete
  4. I agree with Kevin Cardew .But if the value of SEC_CASE_SENSITIVE_LOGON in 11g database is false we must set the parameter to false after upgrade to 12c even if this deprecated otherwise user/application might impacted after upgrade .

    ReplyDelete
  5. Nice but give with example

    ReplyDelete