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.

Password Expire

If a user forgets his password, he may ask you to reset it for him. You will then know his new password, which you may see as a security issue. By including the password expire clause in the alter user command, you can force the user to change his password the next time he logs in. After this, you will no longer know his password. The examples which follow show a DBA changing a password in red and a user logging in afterwards in green.

 

The first example shows a DBA using an Oracle 11 version of SQL*Plus to change a password in an Oracle 11 database:

TEST11 > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:03:51 2015

 

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 a identified by b

  2  password expire

  3  /

 

User altered.

 

SQL>

The user then logs in with the same Oracle 11 version of SQL*Plus and is prompted to change his password. After doing this, he reconnects to the database. This is not necessary, it is just to show that the password change has taken effect:

TEST11 > sqlplus a/b

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:11:51 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

ERROR:

ORA-28001: the password has expired

 

Changing password for a

New password:

Retype new password:

Password changed

 

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> conn a/c

Connected.

SQL>

The DBA then resets and expires the password again using the same Oracle 11 version of SQL*Plus:

TEST11 > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:56:10 2015

 

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 a identified by b

  2  password expire

  3  /

 

User altered.

 

SQL>

The user logs in using an Oracle 10 version of SQL*Plus this time. He is prompted to change his password but is unable to do so:

TEST10 > sqlplus a/b@test11

 

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 26 11:59:46 2015

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

ERROR:

ORA-28001: the password has expired

 

Changing password for a

New password:

Retype new password:

ERROR:

ORA-01017: invalid username/password; logon denied

 

Password unchanged

Enter user-name: 

So, if you want to expire a password in an Oracle 11 database, you need to check that the person who will be logging in to that user afterwards is using an Oracle 11 version of SQL*Plus, not an Oracle 10 one.

Bind Variables

This example, tested on Oracle 11, shows how you can define bind variables in SQL*Plus, assign values to them in PL/SQL then display those values afterwards back in SQL*Plus:

SQL> variable bv1 varchar2(3)

SQL> variable bv2 number

SQL> begin

  2  select 'ABC' into :bv1 from dual;

  3  select 123 into :bv2 from dual;

  4  end;

  5  /

PL/SQL procedure successfully completed.

SQL> print bv1

BV1

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

ABC

SQL> print bv2

       BV2

----------

       123

SQL> execute :bv1 := 'XYZ';

PL/SQL procedure successfully completed.

SQL> execute :bv2 := 456;

PL/SQL procedure successfully completed.

SQL> print bv1

BV1

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

XYZ

SQL> print bv2

       BV2

----------

       456

SQL>

db_writer_processes

I wrote the first part of this example in 2012.

 

The database writer copies data blocks from the buffer cache onto disk. The db_writer_processes initialization parameter determines how many processes will do this task. Its default value is 1 or cpu_count / 8, whichever is greater. I found an Oracle 9 database on a Tru64 server with cpu_count set to 1:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'cpu_count'

SQL> /

 

VALUE

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

1

 

SQL>

 

The database used the default value for db_writer_processes, which Oracle had calculated as 1:

 

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'db_writer_processes'

  4  /

 

VALUE                          ISDEFAULT

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

1                              TRUE

 

SQL>

 

(However, since I first wrote this, I have found documentation suggesting that db_writer_processes always defaulted to 1 in Oracle 9 and took no notice of cpu_count.)

 

I looked for this process in the operating system as follows (I had to squash the ps output a bit to make it fit the screen):

 

UNIX > ps -ef|grep dbw|grep TEST9

oracle 250424 1 0.0 02:07:18 ?? 0:00.87 ora_dbw0_TEST9

UNIX >

 

I found an Oracle 11 database on a Solaris server with cpu_count set to 16:

 

SQL> select value from v$parameter

  2  where name = 'cpu_count'

  3  /

 

VALUE

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

16

 

SQL>

 

It also used the default value for db_writer_processes, which Oracle had calculated as 2:

 

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'db_writer_processes'

  4  /

 

VALUE                          ISDEFAULT

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

2                              TRUE

 

SQL>

 

When I found these processes in the operating system, I saw that Oracle had given them consecutive numbers i.e. dbw0 and dbw1 (the ps output was squashed again to fit the screen):

 

UNIX > ps -ef|grep dbw|grep PROD11

oracle 12230 1 0 19:42:36 ? 1:24 ora_dbw0_PROD11

oracle 12232 1 0 19:42:36 ? 1:21 ora_dbw1_PROD11

UNIX >

 

If you don’t have enough database writer processes, you can apparently have problems with free buffer waits. I searched all our production databases and could only find two with any of these at all. This was one of them:

 

  1  select time_waited from v$system_event

  2* where event = 'free buffer waits'

SQL> /

 

TIME_WAITED

-----------

71

 

SQL>

 

As this figure is given in hundredths of a second, I decided to do nothing about it.

 

I created an Oracle 11.2.0.4 database in 2015 and set db_writer_processes to 36:

 

SQL> alter system set db_writer_processes = 36

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database (this is not shown). When I looked for the database writer processes in the operating system, I saw that Oracle had called them dbw0 through dbw9 then dbwa through dbwz:

 

UNIX > ps -ef|grep dbw|grep BECHEDV1

oracle 50594 50565 0 17:27:03 ? 0:00 ora_dbwh_BECHEDV1

oracle 50583 50565 0 17:27:02 ? 0:00 ora_dbw6_BECHEDV1

oracle 50578 50565 0 17:27:02 ? 0:00 ora_dbw1_BECHEDV1

oracle 50611 50565 0 17:27:03 ? 0:00 ora_dbww_BECHEDV1

oracle 50589 50565 0 17:27:02 ? 0:00 ora_dbwc_BECHEDV1

oracle 50593 50565 0 17:27:03 ? 0:00 ora_dbwg_BECHEDV1

oracle 50600 50565 0 17:27:03 ? 0:00 ora_dbwn_BECHEDV1

oracle 50615 50565 0 17:27:03 ? 0:00 ora_dbwz_BECHEDV1

oracle 50612 50565 0 17:27:03 ? 0:00 ora_dbwx_BECHEDV1

oracle 50582 50565 0 17:27:02 ? 0:00 ora_dbw5_BECHEDV1

oracle 50580 50565 0 17:27:02 ? 0:00 ora_dbw3_BECHEDV1

oracle 50608 50565 0 17:27:03 ? 0:00 ora_dbwt_BECHEDV1

oracle 50588 50565 0 17:27:02 ? 0:00 ora_dbwb_BECHEDV1

oracle 50610 50565 0 17:27:03 ? 0:00 ora_dbwv_BECHEDV1

oracle 50579 50565 0 17:27:02 ? 0:00 ora_dbw2_BECHEDV1

oracle 50602 50565 0 17:27:03 ? 0:00 ora_dbwp_BECHEDV1

oracle 50595 50565 0 17:27:03 ? 0:00 ora_dbwi_BECHEDV1

oracle 50614 50565 0 17:27:03 ? 0:00 ora_dbwy_BECHEDV1

oracle 50598 50565 0 17:27:03 ? 0:00 ora_dbwl_BECHEDV1

oracle 50609 50565 0 17:27:03 ? 0:00 ora_dbwu_BECHEDV1

oracle 50590 50565 0 17:27:03 ? 0:00 ora_dbwd_BECHEDV1

oracle 50581 50565 0 17:27:02 ? 0:00 ora_dbw4_BECHEDV1

oracle 50586 50565 0 17:27:02 ? 0:00 ora_dbw9_BECHEDV1

oracle 50596 50565 0 17:27:03 ? 0:00 ora_dbwj_BECHEDV1

oracle 50584 50565 0 17:27:02 ? 0:00 ora_dbw7_BECHEDV1

oracle 50599 50565 0 17:27:03 ? 0:00 ora_dbwm_BECHEDV1

oracle 50604 50565 0 17:27:03 ? 0:00 ora_dbwr_BECHEDV1

oracle 50587 50565 0 17:27:02 ? 0:00 ora_dbwa_BECHEDV1

oracle 50585 50565 0 17:27:02 ? 0:00 ora_dbw8_BECHEDV1

oracle 50591 50565 0 17:27:03 ? 0:00 ora_dbwe_BECHEDV1

oracle 50603 50565 0 17:27:03 ? 0:00 ora_dbwq_BECHEDV1

oracle 50601 50565 0 17:27:03 ? 0:00 ora_dbwo_BECHEDV1

oracle 50606 50565 0 17:27:03 ? 0:00 ora_dbws_BECHEDV1

oracle 50597 50565 0 17:27:03 ? 0:00 ora_dbwk_BECHEDV1

oracle 50592 50565 0 17:27:03 ? 0:00 ora_dbwf_BECHEDV1

oracle 50577 50565 0 17:27:02 ? 0:00 ora_dbw0_BECHEDV1

UNIX >

 

I read somewhere that the maximum value for db_writer_processes is 36 but Oracle allowed me to change it to 37:

 

SQL> alter system set db_writer_processes = 37

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database again:

 

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  521936896 bytes

Fixed Size                  2252448 bytes

Variable Size             306184544 bytes

Database Buffers          205520896 bytes

Redo Buffers                7979008 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

UNIX >

 

However, when I checked in the operating system, there were still only 36 database writer processes:

 

UNIX >  ps -ef|grep dbw|grep BECHEDV1|wc -l

      36

UNIX >

 

… and there was a message in the alert log telling me that db_writer_processes had been adjusted:

 

NOTE: db_writer_processes has been changed from 37 to  36ue to NUMA requirements.