Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017

The init.ora/spfile parameter SEC_CASE_SENSITIVE_LOGON got deprecated since Oracle Database 12.1.0.1. This means, we don’t do any further developments to it, you shouldn’t change it from its default TRUE – and if you still do you’ll receive a nice warning during STARTUP of your database:

SQL> alter system set sec_case_sensitive_logon=false scope=spfile;

System altered.

SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Recently a customer asked me if we’d changed the behavior of this parameter in Oracle Database 12c Release 2 as he receives now an ORA-1017: Invalid username or password error when having SEC_CASE_SENSITIVE_LOGON=FALSE with every user except SYSDBAs.

In which situations may you receive an ORA-1017?

This is outlined in the Oracle 12.1 documentation already:

• “Ensure that the SEC_CASE_SENSITIVE_LOGON parameter is not set to FALSE if the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to 12 or 12a. This is because the more secure password versions used for this mode only support case-sensitive password checking. For compatibility reasons, Oracle Database does not prevent the use of FALSE for SEC_CASE_SENSITIVE_LOGON when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a. Setting SEC_CASE_SENSITIVE_LOGON to FALSE when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a causes all accounts to become inaccessible.”

The key is the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER. And here’s the difference between Oracle Database 12.1 and Oracle Database 12.2:

• Oracle Database 12.1: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 11 out of the box
• Oracle Database 12.2: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 12 out of the box

Behavior difference Oracle 12.1 vs Oracle 12.2

See this simple example after switching SEC_CASE_SENSITIVE_LOGON=FALSE in both databases (as shown above):

• Oracle Database 12.1.0.2:

  SQL> alter user system identified by oracle;
  
  User altered.
  
  SQL> connect system/oracle
  Connected.
  

• Oracle Database 12.2.0.1:

  SQL> alter user system identified by oracle;
  
  User altered.
  
  SQL> connect system/oracle
  ERROR:
  ORA-01017: invalid username/password; logon denied
  
  
  Warning: You are no longer connected to ORACLE.

How to workaround the ORA-1017?

First of all you need to edit your sqlnet.ora adding (or lowering) the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a value below 12. But if you try to connect directly after restarting your listener you will receive the same ORA-1017 again. The secret is mentioned in the above documentation link as well: you will have to recreate the user’s passwords if you need the logon process to work as it did work before Oracle Database 12.2.

sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Check in DBA_USERS:

SQL> select username, password_versions from DBA_USERS where username='SYSTEM'; 

USERNAME      PASSWORD_VERSIONS
------------- ---------------------
SYSTEM        11G 12C

There’s no “10G” mentioned. This will prevent the connection.

Solution: You will have to specify the password again respective ALTER the user(s):

SQL> alter user system identified by oracle;

User altered.

SQL> select username, password_versions from DBA_USERS where username='SYSTEM'; 

USERNAME       PASSWORD_VERSIONS
-------------- ----------------------
SYSTEM         10G 11G 12C

SQL> connect system/oracle
Connected.

Further information and links

• MOS Note: 2040705.1
  Lockout of all database authenticated users getting error ORA-01017: invalid username/password; logon denied
• Oracle Documentation 12.1 – Database Security Guide
  SEC_CASE_SENSITIVE_LOGON and password case sensitivity
• Oracle Documentation 12.1 – Database Upgrade Guide
  SQLNET.ALLOWED_LOGON_VERSION got deprecated since Oracle Database 12.1.0.1
• Oracle Documentation 12.2 – Database Upgrade Guide
  Case-Insensitive Passwords and ORA-1017

–Mike