This post replicates a real-life situation where Oracle returned an ORA-01017 when the correct password was used. First I created a user in an Oracle 11 database and checked that I could connect to it:
Oracle 11: sqlplus /
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 23 10:55:39 2019
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user blah identified by secret_password
2 /
User created.
SQL> grant create session to blah
2 /
Grant succeeded.
SQL> conn blah/secret_password
Connected.
SQL>
Then I connected to that user from a server which only had Oracle 9 software installed:
Oracle 9: sqlplus blah/secret_password@flwdpt1
SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 23 11:02:32 2019
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
The user had a hashed password created using the pre Oracle 11 routine:
Oracle 11: sqlplus /
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 23 11:05:08 2019
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select password from sys.user$ where name = 'BLAH'
2 /
PASSWORD
------------------------------
92D633C444E0CD1A
SQL>
It also had a hashed password created using the Oracle 11 routine:
SQL> select spare4 from sys.user$ where name = 'BLAH'
2 /
SPARE4
--------------------------------------------------------------------------------
S:A19249C8E39996F37B62E3DD40A49EC9ADF171BD5B4036462304E353E384
SQL>
In the real-life situation, the user's password expired because the PASSWORD_LIFE_TIME was set to 180 in the DEFAULT profile. For the purposes of this test I expired it manually:
SQL> alter user blah password expire
2 /
User altered.
SQL> select account_status from dba_users
2 where username = 'BLAH'
3 /
ACCOUNT_STATUS
--------------------------------
EXPIRED
SQL>
In the real-life situation I did not know the user's password so I reinstated the Oracle 11 hash to preserve the password's case sensitivity:
SQL> alter user blah identified by values
2 'S:A19249C8E39996F37B62E3DD40A49EC9ADF171BD5B4036462304E353E384'
3 /
User altered.
SQL>
This set the ACCOUNT_STATUS to OPEN:
SQL> select account_status from dba_users
2 where username = 'BLAH'
3 /
ACCOUNT_STATUS
--------------------------------
OPEN
SQL>
It did not occur to me at the time but this also removed the Oracle 10 hash presumably because Oracle had no way of knowing if it was still correct:
SQL> select nvl(password,'NULL') from sys.user$
2 where name = 'BLAH'
3 /
NVL(PASSWORD,'NULL')
------------------------------
NULL
SQL>
I spoke to the developer concerned. He confirmed that he knew the user's password so I told him he could login again. A couple of hours later he told me that he could login to the user using SQL*Plus:
C:\>sqlplus blah/secret_password@flwdpt1
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 23 11:42:58 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
...but he could not connect using JDBC. I am not in a position to replicate that now but I can show the same problem by trying to login from the server with the Oracle 9 software:
Oracle 9: sqlplus blah/secret_password@flwdpt1
SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 23 11:45:18 2019
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
I guess this was sending a hash of the password created using the pre Oracle 11 routine.
The Oracle 11 database had nothing to compare this with so it returned an ORA-01017. The developer told me the password and I reset it. This reinstated the pre Oracle 11 hash of the password:
Oracle 11: sqlplus /
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 23 11:49:12 2019
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter user blah identified by secret_password
2 /
User altered.
SQL> select password from sys.user$ where name = 'BLAH'
2 /
PASSWORD
------------------------------
92D633C444E0CD1A
SQL> select spare4 from sys.user$ where name = 'BLAH'
2 /
SPARE4
--------------------------------------------------------------------------------
S:784AAC15DBA436AA97653EE6C3868F7B87B434793F2BD69DCEDF55C2EDDB
SQL>
This allowed me to login again from the server with the Oracle 9 software:
Oracle 9: sqlplus blah/secret_password@flwdpt1
SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 23 11:54:01 2019
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
...and the developer was able to set up his JDBC connection.
No comments:
Post a Comment