Friday, November 20, 2009

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.