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

–Mike

Share this: