I did blog a day ago about ORA-1017 connection issues in Oracle Database 12.2 once you would like to use the deprecated init.ora/spfile parameter SEC_CASE_SENSITIVE_LOGON=FALSE:
But how can this change actually happen?
Let’s check how the PASSWORD_VERSIONS is set in a fresh Oracle Database 12.1.0.2 database with the January 2017 Proactive Bundle Patch applied to it:
SQL> select username, password_versions from dba_users order by 1; USERNAME PASSWORD_VERSIONS ------------------------------ ----------------- ANONYMOUS APPQOSSYS 10G 11G 12C AUDSYS 10G 11G 12C DBSNMP 10G 11G 12C DIP 10G 11G 12C GSMADMIN_INTERNAL 10G 11G 12C GSMCATUSER 10G 11G 12C GSMUSER 10G 11G 12C ORACLE_OCM 10G 11G 12C OUTLN 10G 11G 12C SYS 10G 11G 12C SYSBACKUP 10G 11G 12C SYSDG 10G 11G 12C SYSKM 10G 11G 12C SYSTEM 10G 11G 12C WMSYS 10G 11G 12C XDB 10G 11G 12C XS$NULL 18 rows selected.
As I haven’t touched SEC_CASE_SENSITIVE_LOGON it will will default to TRUE. In my environment I use an Oracle 12.2 listener therefore the default for the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is 12 already.
After the successful upgrade to Oracle Database 12.2 my DBA_USER’s PASSWORD_VERSIONS looks like this:
SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS order by 1 USERNAME PASSWORD_VERSIONS ------------------------------ ----------------- ANONYMOUS APPQOSSYS 10G 11G 12C AUDSYS 10G 11G 12C DBSFWUSER 11G 12C DBSNMP 10G 11G 12C DIP 10G 11G 12C GGSYS 11G 12C GSMADMIN_INTERNAL 10G 11G 12C GSMCATUSER 10G 11G 12C GSMUSER 10G 11G 12C ORACLE_OCM 10G 11G 12C OUTLN 10G 11G 12C REMOTE_SCHEDULER_AGENT SYS 10G 11G 12C SYS$UMF 11G 12C SYSBACKUP 10G 11G 12C SYSDG 10G 11G 12C SYSKM 10G 11G 12C SYSRAC 11G 12C SYSTEM 10G 11G 12C WMSYS 10G 11G 12C XDB 10G 11G 12C XS$NULL 23 rows selected.
None of the existing user account’s PASSWORD_VERSIONS get changed. Only new users will be created with either PASSWORD_VERSIONS “11G 12C” or locked.
Upgrading with SEC_CASE_SENSITIVE_LOGON=FALSE
What happens if you have set SEC_CASE_SENSITIVE_LOGON=FALSE in your source database prior to an upgrade to Oracle Database 12.2?
First of all you will receive a preupgrade warning when you execute preupgrade.jar:
RECOMMENDED ACTIONS
===================
+ Consider removing the following DEPRECATED initialization parameters.
They are not OBSOLETE in version 12.2.0.1.0
but probably will be OBSOLETE in a future release.
Parameter
------------------------------
sec_case_sensitive_logon
My users in my current – in this case Oracle 11.2.0.4 database – look like this before upgrade:
SQL> select username, password_versions from dba_users USERNAME PASSWORD ------------------------------ -------- SYSTEM 10G 11G SYS 10G 11G LBACSYS 10G 11G OUTLN 10G 11G DBSNMP 10G 11G APPQOSSYS 10G 11G ANONYMOUS XDB 10G 11G WMSYS 10G 11G XS$NULL 11G DIP 10G 11G ORACLE_OCM 10G 11G 12 rows selected.
And the result is “as expected” – no changes to the PASSWORD_VERSIONS when you upgrade to Oracle Database 12.2:
USERNAME PASSWORD_VERSIONS
------------------------------ -----------------
ANONYMOUS
APPQOSSYS 10G 11G
AUDSYS 11G 12C
DBSFWUSER 11G 12C
DBSNMP 10G 11G
DIP 10G 11G
GGSYS 11G 12C
GSMADMIN_INTERNAL 11G 12C
GSMCATUSER 11G 12C
GSMUSER 11G 12C
LBACSYS 10G 11G
ORACLE_OCM 10G 11G
OUTLN 10G 11G
REMOTE_SCHEDULER_AGENT
SYS 10G 11G
SYS$UMF 11G 12C
SYSBACKUP 11G 12C
SYSDG 11G 12C
SYSKM 11G 12C
SYSRAC 11G 12C
SYSTEM 10G 11G
WMSYS 10G 11G
XDB 10G 11G
XS$NULL
24 rows selected.
But what happens when you try to connect now with user SYSTEM? SYSTEM has the PASSWORD_VERSIONS="10G 11G" as only the new users will get created with PASSWORD_VERSIONS="11G 12C".
SQL> connect system/oracle
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Even if you ALTER now SYSTEM‘s password you can’t login as the SEC_CASE_SENSITIVE_LOGON=FALSE setting collides with the default SQL*Net authentication protocol (SQLNET.ALLOWED_LOGON_VERSION_SERVER=12).
SQL> alter user system identified by system;
User altered.
SQL> connect system/system
ERROR:
ORA-01017: invalid username/password; logon denied
SYSTEM‘s password is now "11G 12C" – but still you can’t connect because of the SEC_CASE_SENSITIVE_LOGON=FALSE setting:
SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where USERNAME='SYSTEM'; USERNAME PASSWORD_VERSIONS ------------------------------ ----------------- SYSTEM 11G 12C
Simple solution: You change SEC_CASE_SENSITIVE_LOGON=TRUE, the default.
SQL> alter system set sec_case_sensitive_logon=true; System altered. SQL> alter user system identified by oracle; User altered. SQL> connect system/oracle Connected.
Or you use the workaround from my previous blog post:
What happens during export/import?
Next question I’ve had: What happens if I export and import a user, let’s say from Oracle Database 11.2.0.4 into Oracle Database 12.2.0.1. Quick test again:
SQL> grant connect, resource, dba to hugo identified by hugo; Grant succeeded. SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO'; USERNAME PASSWORD ------------------------------ -------- HUGO 10G 11G SQL> alter user hugo default tablespace users; User altered. SQL> create table hugo.tab1 as select * from tab$; Table created.
Then over to my destination database:
SQL> create directory mydir as '/u02/oradata/DB12/mydir'; Directory created. SQL> grant read, write on directory mydir to system; Grant succeeded. SQL> create public database link SOURCEDB connect to system identified by oracle using 'UPGR'; Database link created. SQL> select instance from v$thread@SOURCEDB; INSTANCE -------------------------------------------------------------------------------- UPGR
Now let’s move HUGO over to the DB12 database using the database link:
$ impdp system/oracle network_link=sourcedb schemas=hugo metrics=y logtime=all exclude=statistics directory=mydir logfile=hugo.log Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:34:48 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 24-APR-17 15:34:54.082: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** network_link=sourcedb schemas=hugo metrics=y logtime=all exclude=statistics directory=mydir logfile=hugo.log 24-APR-17 15:34:55.181: W-1 Startup took 2 seconds 24-APR-17 15:34:55.299: W-1 Estimate in progress using BLOCKS method... 24-APR-17 15:34:56.428: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA 24-APR-17 15:34:56.485: W-1 Estimated 1 TABLE_DATA objects in 1 seconds 24-APR-17 15:34:56.490: W-1 Total estimation using BLOCKS method: 256 KB 24-APR-17 15:34:56.807: W-1 Processing object type SCHEMA_EXPORT/USER 24-APR-17 15:34:56.975: W-1 Completed 1 USER objects in 0 seconds 24-APR-17 15:34:56.975: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT 24-APR-17 15:34:57.014: W-1 Completed 1 SYSTEM_GRANT objects in 1 seconds 24-APR-17 15:34:57.014: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT 24-APR-17 15:34:57.095: W-1 Completed 3 ROLE_GRANT objects in 1 seconds 24-APR-17 15:34:57.095: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE 24-APR-17 15:34:57.385: W-1 Completed 1 DEFAULT_ROLE objects in 0 seconds 24-APR-17 15:34:57.385: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 24-APR-17 15:35:00.257: W-1 Completed 1 PROCACT_SCHEMA objects in 3 seconds 24-APR-17 15:35:00.257: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE 24-APR-17 15:35:01.646: W-1 Completed 1 TABLE objects in 4 seconds 24-APR-17 15:35:01.841: W-1 . . imported "HUGO"."TAB1" 1325 rows in 0 seconds using network link 24-APR-17 15:35:02.015: W-1 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA 24-APR-17 15:35:02.106: W-1 Completed 1 PROCACT_SCHEMA objects in 0 seconds 24-APR-17 15:35:02.495: W-1 Completed 1 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 0 seconds 24-APR-17 15:35:02.609: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Apr 24 15:35:02 2017 elapsed 0 00:00:10
Check how the user got created:
SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO';
USERNAME PASSWORD_VERSIONS
---------------------------- --------------------
HUGO 10G 11G
No worries – Data Pump creates the user exactly as it did exist before.
But what happens if a mandatory password change happens to HUGO?
SQL> alter user hugo identified by hugo;
User altered.
SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO';
USERNAME PASSWORD_VERSIONS
------------------------------ -----------------
HUGO 11G 12C
If you change SQLNET.ALLOWED_LOGON_VERSION_SERVER to 12a, the currently highest and most secure setting, and then ALTER the user within a new session, you’ll receive:
SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO'
USERNAME PASSWORD_VERSIONS
------------------------------ -----------------
HUGO 12C
This leads now to the situation that a below-Oracle-12c client can’t connect to this database anymore. I tried to use my SQL*Plus from the Oracle 11.2.0.4 installation:
$ sqlplus "hugo/hugo@DB12"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 24 15:51:08 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-28040: No matching authentication protocol
So be aware of keepin your client environment in good shape when enforcing higher security standards – which we highly recommend of course.
–Mike
Nice post, thanks you.
Hi Mike,
I am a bit uncertain about a scenario. We have thousands of user accounts in our 11.2.0.4 databases where PASSWORD_VERSION=10G (only 10G, nothing else). We are using SEC_CASE_SENSITIVE_LOGON=false. If I do a network import from 11.2.0.4 to 12.2, what will happen to these user accounts?
Also, can you please test a scenario for me since you have the necessary setup already? Can you please try a network import of a large (70 GB total) partitioned table from 11.2.0.4 to a 12.2 PDB? I did that an got a ORA-2049 error. The nearest I could find was MOS Doc ID 11677757.8 which details a bug which is fixed in 11.2.0.3/12.1.0.1. I am on higher versions so not sure why I am hitting this error.
Thank you for all your help.
Arun
Hi Arun,
for your first question the answer is straight forward:
1. the users will be imported as they existed before – no change to PASSWORD_VERSIONS
2. if you have SEC_…=FALSE then you may not be able to connect with any of them as long as you have SQLNET.ALLOWED..SERVER=12.
You will have to lower the sql net authentication protocol in order to still use the SEC..=FALSE setting in Oracle 12.2 with user accounts using older authentication methods.
For the 2nd question you may please open an SR as I don’t have the capabilities to reproduce this.
Cheers
Mike
Hi Mike,
in the column PASSWORD_VERSIONS I find the value “10G 11G 12C HTTP” (Oracle EE 12.1.0.2.170117). What’s the meaning of HTTP?
Regards
Dieter
Dieter,
I have no idea right now – but let me ask the right person. Will come back ASAP.
Cheers
Mike
Dieter,
this MOS Note deals with the topic:
Oracle Support Document 2194116.1 (Removing MD5 hashes) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2194116.1
Basically, if you don’t use XDB, you should enter:
exec DBMS_XDB_HTTP_DIGEST.disable_all_http_digest;
to get rid of the MD5 hashes.
Cheers
Mike
Sorry, I do not have access to Oracle’s forum to read up more your answer
If XDB is required in order to retrieve data from an external source, what is the security practices in locking down any potential security gaps?
Sorry Aloe,
but I’m not allowed to copy&paste MOS notes here. Basically the MOS note explains why this is and how to remove those entries. You’ll find the command in the comments section below the link to the MOS note.
Cheers
Mike
Great! Thank you for the quick response. Not easy to find in the Docs and on the Internet/MOS!
Dieter
Mike,
In the never ending saga of passwords, here are some observations:
a) I did an import from 11.2.0.4 to 12.2, and the users with PASSWORD_VERSIONS=10G has their accounts created as locked and passwords expired, as seen from the import log entry. This is what is expected.
08-JUN-17 14:03:42.842: ORA-39384: Warning: User a_bcdefrc has been locked and the password expired.
b) Now, I used DBMS_METADATA to generate DDL for the same user in 11.2.0.4 and ran the DDL in same 12.2 DB, the account was created with STATUS=OPEN and PASSWORD_VERSIONS=10G. Is this expected behavior?
Thanks,
Arun
Arun,
please provide me your DBMS_METADATA.GET_DDL statement and let me check this in my env.
Cheers
Mike
Here is the DDL:
CREATE USER “a_bcdefrc” IDENTIFIED BY VALUES ‘251343A56EFBCD7A’
DEFAULT TABLESPACE “USERS”
TEMPORARY TABLESPACE “TEMP”
Arun,
I meant your call to DBMS_METADATA – not the result 😉 And a select from DBA_USERS with the user specs would be helpful as well.
Cheers
Mike
Sorry, I misunderstood. Here it is:
SELECT DBMS_METADATA.GET_DDL(‘USER’, USERNAME) || ‘/’ USER_DDL FROM DBA_USERS;
This is the output from DBA_USERS from the PDB (I ran the CREATE USER.. in PDB):
A_BCDEFRC 260 OPEN USERS TEMP TEMP 09-JUN-17 01:28:00 PM DEFAULT DEFAULT_CONSUMER_GROUP 10G N PASSWORD N NO N NO USING_NLS_COMP NO NO
Mike,
We upgraded 12.1.0.2.170117 to 12.2.0.1 and several bad things happened.
The worst of which is that proxy authentication for enterprise users no longer seems to work properly.
we create two users in the database, midtier and guest
ceate user midtier identified by passwd;
grant create session to midtier;
create user guest authenticated globaly as ”;
alter user guest grant connect through midtier;
Then create a mapping in in OID so that all of our enterprise users end up connected to the db as username guest, with which whichever roles are enabled for them in OID.
after upgrading to 12.2 we get the ubiquitous ORA-28030.
we turned on tracing for event 28033 and see that an ldapsearch failed to find guest in the OID.
So. We added guest into OID as a user in the correct context, and now ldapsearch finds the uid=GUEST.
The trace file ends without error, but we still get ORA-28030 at sqlplus prompt.
We have verified and tested that the wallet is good and that ldapbind succeed.
The 28033 trace shows this as well, it finds the wallet and binds to the directory ok.
If I set a password for an EUS user in OID and then try to connect and supply a password, it works.
But it looks like 12.2 changes how proxy authentication works.
Any info you could provide would help.
Peter,
did you open an SR? If yes, please send me the SR number and I’ll have a look into it.
Actually I don’t have any further information on this topic at this moment. But once I have an SR number I can ask the appropriate people.
Cheers
Mike
I need scenarios which will cause issues while upgrading database from 11.2.0.4 to 12.2.0.1
Ravi,
I’m not a “service provider”.
You may please describe clearly what you are looking for. With just this sentence I can’t figure it out.
Cheers,
Mike
Thanks for all the correspondents. We’ve export dumpfile from 11.2.0.4 to 19c and face the issue while connecting with application schema. ORA-01017: invalid usernam/password login denied. We’ve just alter user with required password in 19c while SEC_CASE_SENSITIVE_LOGON = TRUE and get connected. We’ve made changes in user profile so that the password can be reused and then revertback user profile accordingly. Thanks Muhammad Arif
We are using AutoUpgrade and sec_case_sensitive_logon = TRUE and upgrading from 12.1.0.2 to 19.12. POST-upgrade SYSTEM password is working, SYS is not 🙁 Is he expected behavior of some sort 🙁
AutoUpgrade does not change or adjust any of your passwords.
And without having an error message, I can just speculate.
Thanks,
Mike
Great post! Is there a difference between the encryption algorithms used for allowed_logon_version_server set to 12 vs 12a ?
Hi Jenn,
I think stricter rules will be applied. Not sure about the algorithm itself.
Cheers,
Mike
Hi Mike,
We have a situation where we do not know the password of an application user as it has been running for a very long time. The password version is 10G and database is 11G. When we upgrade database to 19C how do we handle the password as we won’t be able to connect with the current password? Is there any way to capture password from 11G db and re-use in 19C? I noticed the encrypted password captured from 11G works in another 11G database, but not in 19C database.
Hi Gan,
you can use the HASHED password in a command instead to ALTER the user from DBA’s view.
See USER$ and DBA_USERS – and you can use DBMS_METADATA as well since it will give you the hashed value (but of course not the PW itself).
This way you can alter the user.
Cheers
Mike