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.
Smart way of Technology 