Oracle provides the column ACCOUNT_STATUS in the dictionary view DBA_USERS to determine the current status of an Oracle user account. I have recently come across several DBA using this column to incorrectly determine the status of an Oracle user and how their account could be placed back into a workable status. This article addresses the ACCOUNT_STATUS and what a DBA should do with the information.

There are 6 basic types of account status and various combinations of the types. The typical system will contain the following ACCOUNT_STATUS values and each is explained in detail with its meaning and examples.

EXPIRED
EXPIRED & LOCKED
EXPIRED(GRACE)
LOCKED
LOCKED(TIMED)
OPEN

EXPIRED

Meaning – The EXPIRED status indicates one of three events.
1. The DBA has created a new account and expired the password, so the user must change their password on the first logon. An example of this situation is the create user statement below: An example of this situation is the create user statement below:

SQL> Create user TEST identified by test password expire
Default tablespace user_data
Quota unlimited on user_data;
2 3
User created.

SQL> Grant create session to test;
Grant succeeded.
SQL>Grant resource to test;
Grant succeeded.
SQL>
SQL> connect test/test
ERROR:
ORA-28001: the password has expired

Changing password for test
New password:
Retype new password:
Password changed
Connected.
SQL>

2. The password has exceeded the profile limit of PASSWORD_LIFE_TIME and the password must be changed.
3. The administrator alter the user with clause “password expire” to force a password change. An example of this can be seen in the SQL below:

SQL> alter user test password expire;

User altered.

SQL>

EXPIRED & LOCKED

Meaning – An account status of EXPIRED & LOCKED means the password is no longer valid and the account is locked. This is normally caused by the password not being changed before the PASSWORD_LIFE_TIME profile limit is exceeded and the administrator has locked the account.

EXPIRED(GRACE)

Meaning – An account status of EXPIRED(GRACE) indicates that the account password is locked due to the password not being changed before the PASSWORD_LIFE_TIME profile parameter pasted, however you have the additional time defined by the PASSWORD_GRACE_TIME profile parameter to actually change your password. If you do not change the password within the grace time the status will be changed to EXPIRED.

NOTE: The status of the account will not change until you attempt to connect with that username. The example below demonstrates the occurrence of EXPIRED(GRACE).

SQL> select account_status from dba_users where username=’TEST’;

ACCOUNT_STATUS
——————————–
OPEN

SQL> connect test/pass
ERROR:
ORA-28002: the password will expire within 0 days

Connected.
SQL> connect / as sysdba
Connected.

SQL> select account_status from dba_users where username=’TEST’;

ACCOUNT_STATUS
——————————–
EXPIRED(GRACE)

SQL> connect test/pass
ERROR:
ORA-28001: the password has expired

Changing password for test
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)

Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
Connected.
SQL> select account_status from dba_users where username=’TEST’;

ACCOUNT_STATUS
——————————–
EXPIRED

SQL>

LOCKED

Meaning – The account was locked by a DBA with the SQL statement:

Alter user “username” account lock;

LOCKED(TIMED)

Meaning – The account was locked by a user trying to access the username with the incorrect password beyond the profile limit of FAILED_LOGIN_ATTEMPTS. The account will remained locked until the profile limit of PASSWORD_LOCK_TIME is reached. PASSWORD_LOCK_TIME is a definition of time in minutes.

OPEN

Meaning – The account status of OPEN means the account is open for logon and the user can perform any task defined by his granted rights.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com