ORA-01017 in Oracle 12c due to Case Sensitive parameter

ORA-01017 in Oracle 12c due to Case Sensitive parameter

In Oracle 12.2 version, Oracle provide us more secure password for that they mention in documentation that we always need to set the parameter “sec_case_sensitive_logon” to “TRUE” value.
Note: If we change this parameter value to “FALSE” then in Oracle 12c then all other user except sys unable to connect.

Link for Oracle: https://docs.oracle.com/database/121/DBSEG/authentication.htm#GUID-2C63433B-1C93-4DFE-B2B9-F63E8C3EEC1F

Note from Oracle link:
In SQLNET.ORA file, The password version for these secure roles cannot be used, unless you set the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter to 8.
If this parameter is set to 12 or 12a, then you should run the following SQL statement to ensure that case sensitivity is enabled.

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = "TRUE";

Solution

If you want to use 11g or 12.1 password to 12.2 version then solution is to set the “ALLOWED_LOGON_VERSION_SERVER” parameter to lower value then 12 or 12a.
By Default, In oracle 12c parameter value consider as 12 in sqlnet.ora file.

For using lower version of password

--SQLNET.ORA file in oracle 12.2 version:
--Add the following parameter in the file
-- In My Example i change the value to 8 or 11 for supporting all password version.
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

--Check the password version
SQL> select username, password_versions from DBA_USERS where username='C##SUNNY';

USERNAME  PASSWORD_VERSIONS
--------- -----------------
C##SUNNY  11G 12C

SQL> alter user c##sunny identified by sunny;
User altered.

SQL> select username, password_versions from DBA_USERS where username='C##SUNNY';

USERNAME  PASSWORD_VERSIONS
--------  -----------------
C##SUNNY  10G 11G 12C

--Now i can handle the 12c login error by setting the parameter in SQLNET file.
SQL> conn sys/password1 as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=FALSE scope=both;
System altered.

SQL> conn c##sunny/sunny
Connected.

Example of error faced by user in Oracle 12c after changing CASE Sensitive to false

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system set sec_case_sensitive_logon=FALSE scope=both;
System altered.

SQL> conn c##test/Test
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn c##test/test
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> conn sys as sysdba
Enter password:
Connected.

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

SQL> conn c##sunny/sunny
Connected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.