Analysis of Oracle password expiry

Oracle password expiry is managed by the profile limit PASSWORD_LIFE_TIME, but based on the user$ table attributes ASTATUS and EXPTIME.

SQL> connect a/a
Connected.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A OPEN 08 MAY 2010 10:22:52

SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';

NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 0 09 NOV 2009 10:22:52

# So ASTATUS = O and EXPTIME are not set

SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME 1;

Profile altered.

SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_GRACE_TIME 7;

Profile altered.

SQL> connect a/a
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A EXPIRED(GRACE) 27 NOV 2009 09:17:11

SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';

NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 2 09 NOV 2009 10:22:52 27 NOV 2009 09:17:11

# So after we login we find the ASTATUS=2 and EXPTIME is set

SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> connect a/a
ERROR:
ORA-28002: the password will expire within 7 days


Connected.

# But we see that after we remove the password lifetime, the account is still expired

SQL> select username, account_status, expiry_date from dba_users where username = 'A';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A EXPIRED(GRACE) 27 NOV 2009 09:17:11

SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';

NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 2 09 NOV 2009 10:22:52 27 NOV 2009 09:17:11

# and ASTATUS is still "2" and exptime is still set. So once expiry is set, changing the profile won't remove expiry.

SQL> alter user a identified by a;

User altered.

SQL> select username, account_status, expiry_date from dba_users where username = 'A';

USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A OPEN

SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';

NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 0 20 NOV 2009 09:19:11 27 NOV 2009 09:17:11

# When we change the password the PTIME is updated, and the ASTATUS is reset to O, but EXPTIME is not changed.

# Conclusion - password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, depends on a combination of ASTATUS and EXPTIME, and is reset by a password change. Changing the profile will not change ASTATUS. Changing the password resets ASTATUS, PTIME, but not EXPTIME.