DBA_USERS.ACCOUNT_STATUS shows OPEN status when EXPIRY_DATE is alreay passed

We see that the status of an account is OPEN when the expiry date is aleady older than current date. What causes this?

This is the expected behavior. The ACCOUNT_STATUS column of DBA_USERS is not automatically updated, but updated only when a user tries to connect. Normally, when a user tries to connect, the EXPIRY_DATE is compared with the current date and if it is older than the current date, then database checkes if PASSWORD_GRACE_TIME is set. If PASSWORD_GRACE_TIME is set, the grace period is started and the ACCOUNT_STATUS is updated with 'EXPIRED(GRACE)'.

PASSWORD_GRACE_TIME defaults to 7 days. EXPIRY_DATE is derived from PASSWORD_LIFE_TIME, if set. Otherwise, it is 180 days from date when the account is created or when the password is reset. Once the PASSWORD_GRACE_TIME is crossed, the account is locked.

Example From below, we see that the status is OPEN, but expiry date is passed long ago.

SQL> select username, account_status, expiry_date, sysdate
2    from dba_users order by account_status;

USERNAME ACCOUNT_STATUS EXPIRY_DATE             SYSDATE
-------- -------------- ----------------------- -----------------------
PRAJEETH OPEN           12/Nov/2013 10:24:37 PM 14/Jan/2014 12:13:42 AM

The user tries to connect to database and was able to connect.

SQL> conn prajeeth/prajeeth 
ERROR: 
ORA-28002: the password will expire within 7 days 

Connected.

At this time, the status of account is updated with EXPIRED(GRACE), which means that his account's grace period bagan and EXPIRY_DATE is updated with the duration of PASSWORD_GRACE_TIME, which by default is 7 days.

SQL> select username, account_status, expiry_date, sysdate 
2    from dba_users order by account_status; 

USERNAME ACCOUNT_STATUS EXPIRY_DATE             SYSDATE 
-------- -------------- ----------------------- ----------------------- 
PRAJEETH EXPIRED(GRACE) 21/Jan/2014 12:14:59 AM 14/Jan/2014 12:16:30 AM 
Comments