ACCOUNT_STATUS of ORACLE USER

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips


Normal STATUS for database user


1.
SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
OPEN

2.


When DBA want to lock any user to access for database.
"alter user [username] ACCOUNT LOCK;



SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
LOCKED

3.

When FAILED_LOGIN_ATTEMPTS parameter configure according to this whenever user give wrong PASSWORD more than FAILED_LOGIN_ATTEMPTS parameter that time user account is locked for TIMED.


SQL> alter profile default LIMIT failed_login_attempts 1;

Profile altered.

SQL> conn taj/wrongpassword
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn taj/taj
ERROR:
ORA-28000: the account is locked

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
LOCKED(TIMED)

4.


when PASSWORD_LIFE_TIME parameter value cross by database user and PASSWORD_GRACE_TIME is not specified then user account is EXPIRED.



SQL> alter user taj password expire;

User altered.

SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED


5.



when PASSWORD_LIFE_TIME or PASSWORD_LOCK_TIME value cross by database user then user account EXPIRE with LOCK.



SQL> alter user taj password expire account lock;

User altered.

SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED & LOCKED


6.


when PASSWORD_LIFE_TIME parameter is reached by user and PASSWORD_GRACE_TIME parameter configure then some password will be use according PASSWORD_GRACE_TIME parameter but account status show EXPIRE(GRACE)



09:53:38 SQL> alter profile default limit password_lock_time 1/1440;

Profile altered.

09:53:47 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
OPEN

09:53:59 SQL> alter profile default limit password_grace_time 2/1440;

Profile altered.

09:54:14 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
OPEN


09:55:03 SQL> conn taj/taj
ERROR:
ORA-28002: the password will expire within 0 days


Connected.

09:55:08 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE)

7.
10:11:10 SQL> alter profile default limit failed_login_attempts 1;

Profile altered.

10:11:28 SQL> alter user TAJ password expire;

User altered.

10:12:25 SQL> conn taj/wrongpassword
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
10:12:31 SQL> conn sys as sysdba
Enter password:
Connected.
10:12:37 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED & LOCKED(TIMED)

10:12:40 SQL>

8.

10:14:04 SQL> alter profile default limit password_lock_time 30/86400;

Profile altered.

10:14:20 SQL> alter profile default limit password_grace_time 1/1440;

Profile altered.

10:14:38 SQL> alter profile default limit failed_login_attempts 1;

Profile altered.

10:14:45 SQL> conn taj/taj
ERROR:
ORA-28002: the password will expire within 0 days


Connected.
10:15:16 SQL> conn sys as sysdba
Enter password:
Connected.
10:15:28 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE)

10:15:32 SQL> conn taj/wrongpassword
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
10:15:41 SQL> conn sys as sysdba
Enter password:
Connected.
10:15:45 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE) & LOCKED(TIMED)

9.
10:16:37 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE)

10:16:41 SQL> alter user taj account lock;

User altered.

10:16:48 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE) & LOCKED


NOte: All Configuration depand on below parameters

FAILED_LOGIN_ATTEMPTS
PASSWORD_LOCK_TIME
PASSWORD_GRACE_TIME
PASSWORD_LIFE_TIME