Unraveling password lifetime and grace period

Some time back I began to get an odd complaint from some of my end users. It seems that when they connected to the Oracle database, they received a warning that their password was about to expire, and yet never – even after the password grace period had passed – received a prompt to change their password.

Normal and expected behavior would have been for them to start receiving this message when they reached their expiry date and continue to receive it until they reached the end of the grace period, at which time they would be forced to enter a new password. So why were they never prompted/forced for a new password? A check of a typical account showed they had an EXPIRY_DATE of null. I thought this was odd, but being pressed for time, simply expired the account (ALTER USER joe ACCOUNT EXPIRE;), had the user change passwords, and checked that they had a new, valid EXPIRY_DATE. Thinking I had the fix, I ran a script to expire all users with an EXPIRY_DATE of null, and moved on.

Then a couple of months later one of my primary users again mentioned that he was being warned about an expiring password, but never prompted to change. Since I knew for a fact I had fixed this problem for him (he was my test case) I decided to dig a bit deeper. What I found was that under normal circumstances, the first time a user connects after reaching their EXPIRY_DATE, their status changes from OPEN to EXPIRED(GRACE), and EXPIRY_DATE is reset to sysdate + grace period. If the grace period is default/unlimited, sysdate + grace period means the new EXPIRY_DATE will be null. Now when the user connects, Oracle sees his status is EXPIRED(GRACE), so triggers the warning, but the comparison of sysdate to EXPIRY_DATE (null) never evaluates to TRUE, so never triggers the forcing of a password change.

While I always knew in general terms what PASSWORD_LIFETIME and PASSWORD_GRACE_TIME were all about, I had never really thought through all the implications of the relationships, especially if one was set and the other defaulted. So allow me to put up a clear demonstration.

I’ll start with a base line and demonstrate normal, expected behavior. Then I’ll recreate my problem and show how it plays out in terms of the user account status and dates. My test system is Oracle 11.2 Enterpirse, running on Oracle Linux 5 under VMworkstation on my Win 7 Home Premium laptop. I can “accelerate” time forward to reach expiration dates by simply having the root user change the system time.

First, set the default profile. I’ll set the lifetime and grace period differently so that we can see which one is working to set new EXPIRY_DATEs:

[oracle@vmlnxsrv01 sql]$ sqlplus system/halftrack

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 16 08:50:56 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter profile DEFAULT limit
  2     PASSWORD_LIFE_TIME 2
  3     PASSWORD_GRACE_TIME 3;

Profile altered.

SQL>

Fig. 1

Next, create a user, check his account, and also check the current date

SQL> drop user joe;

User dropped.

SQL> create user joe identified by joe;

User created.

SQL> grant create session to joe;

Grant succeeded.

SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from dba_users
  6  where username='JOE'
  7  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      OPEN            18-JAN-2012 08:51:50 16-JAN-2012 08:51:50

1 row selected.

SQL>

Fig. 2

Just as expected, EXPIRY_DATE is two days from today, so Joe should be able to connect with no surprises:

SQL> conn joe/joe
Connected.
SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from user_users
  6  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      OPEN            18-JAN-2012 08:51:50 16-JAN-2012 08:53:01

1 row selected.

SQL>

Fig. 3

Now let’s set the clock forward to the EXPIRY_DATE and have Joe connect again

[oracle@vmlnxsrv01 sql]$ sqlplus joe/joe

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 18 08:00:19 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from user_users
  6  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      OPEN            18-JAN-2012 08:51:50 18-JAN-2012 08:00:35

1 row selected.

SQL>

Fig. 4

So it appears we have to go PAST the EXPIRY_DATE (down to the second) to trigger anything:

[oracle@vmlnxsrv01 sql]$ sqlplus joe/joe

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 18 08:52:22 2012

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

ERROR:
ORA-28002: the password will expire within 3 days

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from user_users
  6  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      EXPIRED(GRACE)  21-JAN-2012 08:52:22 18-JAN-2012 08:52:28

1 row selected.

SQL>

Fig. 5

Notice that joe was allowed to connect, but received a warning. Also notice that his status has change to EXPIRED(GRACE), and the EXPIRY_DATE has changed to sysdate + grace period (3 days).

Now let’s move forward past the new EXPIRY_DATE.

[oracle@vmlnxsrv01 sql]$ sqlplus joe/joe

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 21 09:00:27 2012

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

ERROR:
ORA-28001: the password has expired

Changing password for joe
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from user_users
  6  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      OPEN            23-JAN-2012 09:00:32 21-JAN-2012 09:00:59

1 row selected.

SQL>

Fig. 6

Joe is prompted for a new password, his status is set back to OPEN, and EXPIRY_DATE is set to sysdate + password lifetime (2 days) All is well with the world.

Now let’s set the grace period to unlimited and run the test again.

[oracle@vmlnxsrv01 sql]$ sqlplus system/halftrack

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 23 09:07:16 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter profile DEFAULT limit
  2     PASSWORD_LIFE_TIME 2
  3     PASSWORD_GRACE_TIME unlimited;

Profile altered.

SQL> drop user joe;

User dropped.

SQL> create user joe identified by joe;

User created.

SQL> grant create session to joe;

Grant succeeded.

SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from dba_users
  6  where username='JOE'
  7  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      OPEN            25-JAN-2012 09:07:20 23-JAN-2012 09:07:20

1 row selected.

SQL> conn joe/joe
Connected.
SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from user_users
  6  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      OPEN            25-JAN-2012 09:07:20 23-JAN-2012 09:07:20

1 row selected.

SQL>

Fig. 7

Move the date to a point after current EXPIRY_DATE, and test Joe again

[oracle@vmlnxsrv01 sql]$ sqlplus joe/joe

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 25 09:08:29 2012

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

ERROR:
ORA-28011: the account will expire soon; change your password now

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from user_users
  6  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      EXPIRED(GRACE)                       25-JAN-2012 09:08:47

1 row selected.

SQL>

Fig. 8

Notice a couple of things in this test.

First, while the account status is “EXPIRED(GRACE)” (just like the earlier scenario), since there was no specific grace period, there was nothing to set EXPIRY_DATE to, except NULL.

Second, the message changed from “ORA-28002: the password will expire within n days” to “ORA-28011: the account will expire soon; change your password now”. And since the EXPIRY_DATE is null, we can never reach a point where the difference between sysdate and EXPIRY_DATE will be any different than it is now. The user will get this message until either the DBA forces it expired, or until he changes his password on his own.

Let’s have the DBA do it

[oracle@vmlnxsrv01 sql]$ sqlplus system/halftrack

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 25 09:10:16 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user joe password expire;

User altered.

SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from dba_users
  6  where username='JOE'
  7  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      EXPIRED         25-JAN-2012 09:10:20 25-JAN-2012 09:10:20

1 row selected.

SQL>

Fig. 9

So now the account status is simply ‘EXPIRED”. Next time joe connects, he has to change his password:

SQL> conn joe/joe
ERROR:
ORA-28001: the password has expired

Changing password for joe
New password:
Retype new password:
Password changed
Connected.
SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from user_users
  6  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      OPEN            27-JAN-2012 09:11:28 25-JAN-2012 09:11:32

1 row selected.

SQL>

Fig. 10

Now I’ll change the system date to force Joe back into limbo

[oracle@vmlnxsrv01 sql]$ sqlplus joe/bob

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 27 09:58:19 2012

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

ERROR:
ORA-28011: the account will expire soon; change your password now

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from user_users
  6  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      EXPIRED(GRACE)                       27-JAN-2012 09:58:24

1 row selected.

SQL> password
Changing password for JOE
Old password:
New password:
Retype new password:
Password changed

SQL> select  username
  2  ,       account_status
  3  ,       expiry_date
  4  ,       sysdate
  5  from user_users
  6  ;

USERNAME ACCOUNT_STATUS  EXPIRY_DATE          SYSDATE
-------- --------------- -------------------- --------------------
JOE      OPEN            29-JAN-2012 10:00:05 27-JAN-2012 10:00:10

1 row selected.

SQL>

Fig. 11

Lessons Learned

I learned several lessons from this exercise. And the most important lessons had more to do with human failings than technology.

Lesson One

First, no matter how much you think you understand something, when presented with a problem, you should always read and research the actual error message. If I had done that, I would have seen this:

[oracle@vmlnxsrv01 ~]$ oerr ORA 28011
28011, 00000, "the account will expire soon; change your password now"
// *Cause:   The user's account is marked for expiry; the expiry period
//           is unlimited.
// *Action:  Change the password or contact the DBA.
[oracle@vmlnxsrv01 ~]$

Fig. 12

Of course that would have still left me wondering why the expiry period was unlimited. Which leads to lesson #2

Lesson Two

No matter how well you think you understand something, it never hurts to review the documentation – AGAIN!

If I had heeded that lesson, I would have looked up CREATE PROFILE in the SQL Reference Manual, and found this nugget:

PASSWORD_LIFE_TIME Specify the number of days the same password can be used for authentication. If you also set a value for PASSWORD_GRACE_TIME, the password expires if it is not changed within the grace period, and further connections are rejected. If you do not set a value for PASSWORD_GRACE_TIME, its default of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.

Note that the above is from the 10.2 SQL Reference. My production database is running 10.2.0.5. In setting up this demo I was using my personal test database, which is running 11.2, and in working out some anomalies, discovered that the behavior changed from 10.2 to 11.2. Specifically, the default value of PASSWORD_GRACE_TIME changed from UNLIMITED in 10g to 7 days in 11g. See the Oracle® Database 2 Day + Security Guide, “Using the Default Security Settings“.

Lesson Three

No matter how well you think you know your own system, always double check any settings or values involved in the problem at hand. I have no idea how my PASSWORD_GRACE_TIME got set to DEFAULT (which is UNLIMITED on my production 10g system). But until I made a point of checking it, I was shooting in the dark to get to the bottom of the problem.

Other lessons?

So what do you think?  Have you had issues or unexplained behaviors related to password lifetime?  Do you have any questions about how these should be handled?

Please like us:

1 Comment


  1. // Reply

    The above article was originally published on my first website at wordpress.com, and re-published here when I decided to self-host. The original site had some follow-up questions from readers that I felt were worthwhile to summarize here.

    One reader observed that he had expiry_date at 28-Feb with a grace period of 2 days. Thus he expected that a logon after 2-March (expiry_date + grace_period) would see the account as fully expired and force a password change. What he failed to understand is that the key elements at logon time are expiry_date and account_status. Neither password_life_time nor grace_period are involved at this point. Here is my response at that time:

    “The key elements at logon time (and thus, which return message you get) are expiry_date and account_status (see dba_users). Password lifetime is not involved at this point. The first time a user logs on after the current value of expiry_date, he will receive ORA-28002 and two things happen to his account. One, the account_status will be changed from ‘OPEN’ to ‘EXPIRED(GRACE)’ and (two) expiry_date is recalculated to sysdate + profile grace_period. The only time the profile settings for password lifetime comes into play is at the time a password is changed, and then it is only used to compute a new expiry_date.”

Leave a Reply

Your email address will not be published. Required fields are marked *