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.

LOCKED and LOCKED(TIMED)

This example, which was tested on Oracle 11.2, shows the difference between a user with an ACCOUNT_STATUS of LOCKED and one with an ACCOUNT_STATUS of LOCKED(TIMED). First I created a profile with a PASSWORD_LOCK_TIME of 0.0007 days i.e. roughly 1 minute and a FAILED_LOGIN_ATTEMPTS limit of 1:

SQL> create profile for_andrew

  2  limit failed_login_attempts 1

  3  password_lock_time 0.0007

  4  /

 

Profile created.

 

SQL>

Then I created a user and gave it this profile:

SQL> create user andrew

  2  identified by reid

  3  profile for_andrew

  4  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL>

I checked that the user had an ACCOUNT_STATUS of OPEN:

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>

Then I locked the user. This is how you get an ACCOUNT_STATUS of LOCKED:

SQL> alter user andrew account lock

  2  /

 

User altered.

 

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

LOCKED

 

SQL>

If you try to login to a LOCKED user, you get an ORA-28000: 

SQL> conn andrew/reid

ERROR:

ORA-28000: the account is locked

 

Warning: You are no longer connected to ORACLE.

SQL>

Then I unlocked the user to give it an ACCOUNT_STATUS of OPEN again: 

SQL> alter user andrew account unlock

  2  /

 

User altered.

 

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>

Next, I tried to login with an incorrect password:

SQL> conn andrew/blah

ERROR:

ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>

This changed the ACCOUNT_STATUS to LOCKED(TIMED) as the user's profile only allows one 1 FAILED_LOGIN_ATTEMPT: 

SQL> conn / as sysdba

Connected.

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

LOCKED(TIMED)

 

SQL>

... so even when I tried to login with the correct password, I got an ORA-28000 again:

SQL> conn andrew/reid

ERROR:

ORA-28000: the account is locked

 

Warning: You are no longer connected to ORACLE.

SQL>

Once the user has an ACCOUNT_STATUS of LOCKED(TIMED), he cannot connect to the database for PASSWORD_LOCK_TIME days (around one minute in this case). I checked the current time:

SQL> conn / as sysdba

Connected.

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

19:02:02

 

SQL>

... then I waited until the user's ACCOUNT_STATUS was OPEN again:

SQL> declare

  2    andrews_account_status

  3      dba_users.account_status%type := 'BLAH';

  4  begin

  5    while andrews_account_status != 'OPEN' loop

  6      dbms_lock.sleep(10);

  7      select account_status into andrews_account_status

  8        from dba_users

  9        where username = 'ANDREW';

 10    end loop;

 11  end;

 12  /

 

PL/SQL procedure successfully completed.

 

SQL>

I checked the time again to see how long it had taken. This may be longer than the value specified by PASSWORD_LOCK_TIME:

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

19:03:03

 

SQL>

I verified that the user's ACCOUNT_STATUS was OPEN:

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>

... and checked that he could login again:

SQL> conn andrew/reid

Connected.

SQL> show user

USER is "ANDREW"

SQL>

How to Test a Database Link

Before creating a database link, you need a schema to connect to in the remote database:

 

SQL> conn /@remotedb

Connected.

SQL> grant create session to link_schema

  2  identified by link_schema_password

  3  /

 

Grant succeeded.

 

SQL>

 

Then you can create a database link in the local database as follows:

 

SQL> create database link andrews_link

  2  connect to link_schema

  3  identified by link_schema_password

  4  using 'REMOTEDB'

  5  /

 

Database link created.

 

SQL>

 

... and you can test it like this:

 

SQL> select * from dual@andrews_link

  2  /

 

D

-

X

 

SQL>

 

If things go wrong in the remote database, the test will fail in the local database and will often give you a good idea of what is wrong. Here is one example:

 

SQL> conn /@remotedb

Connected.

SQL> revoke create session from link_schema

  2  /

 

Revoke succeeded.

 

SQL> conn /@localdb

Connected.

SQL> select * from dual@andrews_link

  2  /

select * from dual@andrews_link

                   *

ERROR at line 1:

ORA-01045: user LINK_SCHEMA lacks CREATE SESSION privilege; logon denied

ORA-02063: preceding line from ANDREWS_LINK

 

SQL>

 

... and here is another:

 

SQL> conn /@remotedb

Connected.

SQL> alter user link_schema

  2  identified by new_password

  3  /

 

User altered.

 

SQL> conn /@localdb

Connected.

SQL> select * from dual@andrews_link

  2  /

select * from dual@andrews_link

                   *

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from ANDREWS_LINK

 

SQL>

remote_os_authent

The Oracle RDBMS used to have a parameter called remote_os_authent. This specified whether or not you could connect to an instance remotely using OS authentication. Setting it to true was a security risk, especially if you used OS authentication for database users which had the DBA role. For example, you might have an externally identified user in your database called ORACLE and grant the DBA role to that user. A malicious user with admin rights on a remote machine could create a user called oracle on that machine and use it to connect to your database as an administrator without providing a password. In version 11, the Oracle RDBMS deprecated this parameter but have retained it (for now) for backward compatibility. The example below illustrates this. I ran it on a UNIX server as a UNIX user called oracle. First I connected to the database as SYS and set remote_os_authent to true in the server parameter file:

 

SQL> conn / as sysdba

Connected.

SQL> alter system set

  2  remote_os_authent = true

  3  scope = spfile

  4  /

 

System altered.

 

SQL>

 

Then I bounced the database. The Oracle RDBMS displayed an error message when it saw the deprecated parameter:

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area  158703616 bytes

Fixed Size                  2086736 bytes

Variable Size              83888304 bytes

Database Buffers           67108864 bytes

Redo Buffers                5619712 bytes

Database mounted.

Database opened.

SQL>

 

I reconnected to the database remotely and reset remote_os_authent in the server parameter file:

 

SQL> conn /@test11

Connected.

SQL> show user

USER is "ORACLE"

SQL> alter system reset remote_os_authent

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database again. This time there was no error message:

 

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  158703616 bytes

Fixed Size                  2086736 bytes

Variable Size              88082608 bytes

Database Buffers           62914560 bytes

Redo Buffers                5619712 bytes

Database mounted.

Database opened.

SQL>

 

Changing the remote_os_authent parameter stopped the remote connection working:

 

SQL> conn /@test11

ERROR:

ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>

Failed Login Attempts

(Tested on an Oracle 9.2.0.7.0 database.)

This can be used to limit the number of times a user can enter an incorrect password. First create a test user and show what profile he is using:

  1  grant create session to andrew

  2* identified by reid

SQL> /

Grant succeeded.

SQL> select profile from dba_users

  2  where username = 'ANDREW';

PROFILE

------------------------------

DEFAULT

SQL>

Then change that profile so that only two incorrect password attempts are allowed:

SQL> alter profile default

  2  limit failed_login_attempts 2;

Profile altered.

SQL>

To demonstrate the limit, the user must then try to login twice with the wrong password:

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

SQL>

If he tries to login again he will see that the account is locked:

SQL> conn andrew/wrong_password

ERROR:

ORA-28000: the account is locked

SQL>

Note that this limit still applies even if resource_limit is set to false:

SQL> col value format a20

SQL> l

  1  select value from v$parameter

  2* where name = 'resource_limit'

SQL> /

VALUE

--------------------

FALSE

SQL>

Also note that the failed login attempts need to be consecutive. A successful login attempt sets the count of failed login attempts back to zero:

SQL> conn / as sysdba

Connected.

SQL> alter user andrew account unlock;

User altered.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL>