sec_case_sensitive_logon

Recently I helped one of my colleagues troubleshooting the issue about never log into oracle database 19.15 even if he has inputted the correct username and password. I’ve discovered an interesting blog note from Mike Dietrich on Google. Wow, very great! It’s the same issue as encountered by my colleague.

Now let us simulate the senario from my test environment of oracle 19.3.

SQL> show parameter sec_case_sensitive_logon;

NAME                                 TYPE                      VALUE
------------------------------------ ----------------------    ------------------------------
sec_case_sensitive_logon             boolean                   TRUE

SQL> create user c##test identified by "1QAZ2WSX!";

SQL> grant connect, resource to c##test;

SQL> conn c##test
Enter password:    (no prompt)
connected.
SQL> show user;
USER is "C##TEST"

SQL> conn / as sysdba
SQL> alter system set sec_case_sensitive_logon=false;

SQL> conn c##test
Enter password:   (no prompt, used the correct password)
Error:
ORA-01017: invalid username/password; logon denied

SQL> conn c##test
Enter password:   (no prompt, use all of the lower case character for password)
Error:
ORA-01017: invalid username/password; logon denied

SQL> show parameter sec_case_sensitive_logon;

NAME                                 TYPE                      VALUE
------------------------------------ ----------------------    ------------------------------
sec_case_sensitive_logon             boolean                   FALSE

SQL> alter system set sec_case_sensitive_logon=true;

SQL> show parameter sec_case_sensitive_logon;

NAME                                 TYPE                      VALUE
------------------------------------ ----------------------    ------------------------------
sec_case_sensitive_logon             boolean                   TRUE

SQL> conn c##test
Enter password:    (no prompt)
connected.

Hope to help you as well …

Best wishes for you Mid Autumn Festival in China :-).