Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017

Posted on April 24, 2017 by Behavior Change Oracle Database 12.2.0.1 Security 24

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

Tags:

You may also like...

24 Responses

  1. Foued says:
    April 24, 2017 at 15:34

    Thank you for the share.

    Reply
  2. Bruce Dungy says:
    April 24, 2017 at 22:57

    Good information, Mike. I had just discovered this behavior in 12.2 but hadn’t had an opportunity to investigate it further.

    Reply
  3. Florin says:
    May 29, 2017 at 18:42

    Thank you, Mike, you are a life saver 🙂

    Reply
  4. Lucas says:
    June 14, 2017 at 16:59

    You helped me a lot! Thanks

    Reply
  5. Billy D says:
    November 23, 2017 at 13:39

    Just one word… Thanks !

    Reply
  6. Dawood says:
    May 24, 2018 at 15:46

    Hi
    The above given solution did not do the job.
    Tried to unlock hr but in vain.
    alter hr identified by hr account unlock;
    alter system set sec_case_sensitive_logon=false;
    I still get the error invalid password and logon denied.
    This is 12C you you are talking about. Many many issues with it unfortunately
    Yes the HR and SCOTT users exist/
    select username from cdb_users or dba_users;
    What is your recommendation to solve this problem after trying all options but in vain?.
    Good day.
    Dawood

    Reply
  7. Ahmed says:
    September 13, 2018 at 11:29

    Hi, and thank you for all
    you had said : if you try to connect directly after restarting your listener you will receive the same ORA-1017 again
    do we need to restart the listener after sqlnet.ora change to resolve this issue ?

    Reply
  8. Szymon Skorupinski says:
    November 22, 2018 at 16:21

    Hello,

    I’ve just posted a new blog post, describing another related problem, which I was able to solve thanks to your post. So…big thanks and keep blogging!

    If interested, please find it here: https://changetracking.wordpress.com/2018/11/22/redo-transport-from-oracle-11-2-to-12-2-and-sys-password-case-sensitivity-problems.

    Best regards,

    Reply
  9. Jean-Marc Mairlot says:
    October 1, 2019 at 13:40

    thanks a lot !

    Reply
  10. Michaeloqlkello says:
    October 22, 2019 at 15:47

    This was realy great stuff. Thank you for that.

    Reply
  11. Dennis Lonndon says:
    March 13, 2020 at 11:37

    thanks a lot ! You saved me thousand of attempts

    Reply
  12. Darren Rhymer says:
    April 8, 2020 at 10:44

    Hi
    Really useful stuff, thanks.

    I am trying to install Primavera Data Warehouse version 18. I have my Oracle 12.2.0.1 database and Oracle Gateway database on the database server.
    And on the application server I have an Oracle Database Client (not sure i need this but the documentation said i MUST), and eventually i will have the PDW code. All products used are in the tested configuraions XLS.

    In the instructions:
    https://docs.oracle.com/cd/E91469_01/English/install_upgrade_installing/pdw_install_config/index.html
    Topics
    Primavera Data Warehouse Installation Prerequisites=>Creating the Primavera Data Warehouse Tablespaces
    it says to run the code
    alter system set sec_case_sensitive_logon=false;

    Is this correct to leave it like this? Or should i be using your suggestions above to allow non-sysdba db access?

    When installing the PDW software STAR schema configuration i need to provide username and password to connect to my database, which now will only accept SYS user…..

    Thanks

    Reply
    • Mike.Dietrich says:
      April 8, 2020 at 15:16

      Hi Darren,

      you should always follow the guidelines of the application provider (in this case “Oracle” for Primavera). The same would apply to EBS, Siebel, SAP etc
      When the app provider says “we need this parameter set that way” then it’s better to follow it as otherwise you may run into trouble.

      Cheers,
      Mike

      Reply
      • Darren Rhymer says:
        April 8, 2020 at 22:18

        HI Mike,

        Thanks for getting back to me.

        I have raised an SR which came back with the suggestion to ‘log in as system’. Which is the problem i am having!!

        As i am using 12.2.0.1 i guess the only thing to do is follow your advice above. I think i will forward the URL of this page to the SR advisor as they don’t seem to know the issues the alter system line is causing.

        Kind Regards

        Darren

        Reply
      • Darren Rhymer says:
        April 9, 2020 at 08:57

        Got another reply back from Oracle on the SR:

        “To be honest, I have never run that alter system command when installing data warehouse (it is really not a requirement).
        I suggest that you “skip” the alter system command and during the configuration, you will be specifying the SYSTEM username and password.”

        Reply
  13. Pepe says:
    July 2, 2025 at 18:20

    Thanks a lot, Mike – really helpful post as always! Just one question that hasn’t been raised: in a real upgrade scenario with dozens or hundreds of users, how would you recommend resetting their passwords?

    Your examples show how to handle a single user, but in larger environments, we’d need to reset many. Would it be best to extract the hashes directly from SYS.USER$ before the upgrade and recreate them afterward? Or could we rely on DBMS_METADATA.GET_DDL(‘USER’, …) to generate the necessary CREATE USER … IDENTIFIED BY VALUES statements with the existing hashes?

    Dealing with passwords after an upgrade – and even after a Data Guard switchover – is a nightmare sometimes!

    Thanks again for the great insights!

    Reply
    • Mike.Dietrich says:
      July 14, 2025 at 09:30

      Hi Pepe,

      I think the best way is to apply a profile to all the users which ages out the password.
      You can remove the profile later then when all is done. At least, this is the route I would evaluate if scripts don’t do the trick.

      Cheers,
      Mike

      Reply
  1. What happens to PASSWORD_VERSIONS during an upgrade to Oracle 12.2? | Upgrade your Database - NOW!
    April 25, 2017

    […] Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017 […]

  2. Monitoring script SQL 1 – Oracleoradaemon Blog
    October 1, 2021

    […] https://mikedietrichde.com/2017/04/24/having-some-fun-with-sec_case_sensitive_logon-and-ora-1017/ […]

  3. sec_case_sensitive_logon – Focusing and Concentrating Oracle Recipes, Tips and Techniques
    September 29, 2023

    […] 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 […]

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Mike Dietrich
Oracle Vice President of Product Management and Development for Database Upgrade, Cloud Migrations and Patching writing just another blog.

Opinions certainly are my own as is this page.

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 11.3K other subscribers