Friday, February 01, 2019

Correct Password Gives ORA-01017

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.