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 toFALSE
if theSQLNET.ALLOWED_LOGON_VERSION_SERVER
parameter is set to12
or12a
. 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 ofFALSE
forSEC_CASE_SENSITIVE_LOGON
whenSQLNET.ALLOWED_LOGON_VERSION_SERVER
is set to12
or12a
. SettingSEC_CASE_SENSITIVE_LOGON
toFALSE
whenSQLNET.ALLOWED_LOGON_VERSION_SERVER
is set to12
or12a
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
Thank you for the share.
Good information, Mike. I had just discovered this behavior in 12.2 but hadn’t had an opportunity to investigate it further.
Thank you, Mike, you are a life saver 🙂
You helped me a lot! Thanks
Just one word… Thanks !
Always welcome – and glad if it helped.
Cheers,
Mike
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
Dawood,
if it doesn’t do the job for you, please open an SR and check with Support. I can’t give tech support via the blog.
Cheers,
Mike
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 ?
I think so – but I’m not 100% sure. I restart it after such a change.
Mike
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,
thanks a lot !
Welcome!
This was realy great stuff. Thank you for that.
thanks a lot ! You saved me thousand of attempts
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
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
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
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.”