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?
Ed Stevens, DBA 