LOCKED and LOCKED(TIMED)

This example, which was tested on Oracle 11.2, shows the difference between a user with an ACCOUNT_STATUS of LOCKED and one with an ACCOUNT_STATUS of LOCKED(TIMED). First I created a profile with a PASSWORD_LOCK_TIME of 0.0007 days i.e. roughly 1 minute and a FAILED_LOGIN_ATTEMPTS limit of 1:

SQL> create profile for_andrew

  2  limit failed_login_attempts 1

  3  password_lock_time 0.0007

  4  /

 

Profile created.

 

SQL>

Then I created a user and gave it this profile:

SQL> create user andrew

  2  identified by reid

  3  profile for_andrew

  4  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL>

I checked that the user had an ACCOUNT_STATUS of OPEN:

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>

Then I locked the user. This is how you get an ACCOUNT_STATUS of LOCKED:

SQL> alter user andrew account lock

  2  /

 

User altered.

 

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

LOCKED

 

SQL>

If you try to login to a LOCKED user, you get an ORA-28000: 

SQL> conn andrew/reid

ERROR:

ORA-28000: the account is locked

 

Warning: You are no longer connected to ORACLE.

SQL>

Then I unlocked the user to give it an ACCOUNT_STATUS of OPEN again: 

SQL> alter user andrew account unlock

  2  /

 

User altered.

 

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>

Next, I tried to login with an incorrect password:

SQL> conn andrew/blah

ERROR:

ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>

This changed the ACCOUNT_STATUS to LOCKED(TIMED) as the user's profile only allows one 1 FAILED_LOGIN_ATTEMPT: 

SQL> conn / as sysdba

Connected.

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

LOCKED(TIMED)

 

SQL>

... so even when I tried to login with the correct password, I got an ORA-28000 again:

SQL> conn andrew/reid

ERROR:

ORA-28000: the account is locked

 

Warning: You are no longer connected to ORACLE.

SQL>

Once the user has an ACCOUNT_STATUS of LOCKED(TIMED), he cannot connect to the database for PASSWORD_LOCK_TIME days (around one minute in this case). I checked the current time:

SQL> conn / as sysdba

Connected.

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

19:02:02

 

SQL>

... then I waited until the user's ACCOUNT_STATUS was OPEN again:

SQL> declare

  2    andrews_account_status

  3      dba_users.account_status%type := 'BLAH';

  4  begin

  5    while andrews_account_status != 'OPEN' loop

  6      dbms_lock.sleep(10);

  7      select account_status into andrews_account_status

  8        from dba_users

  9        where username = 'ANDREW';

 10    end loop;

 11  end;

 12  /

 

PL/SQL procedure successfully completed.

 

SQL>

I checked the time again to see how long it had taken. This may be longer than the value specified by PASSWORD_LOCK_TIME:

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

19:03:03

 

SQL>

I verified that the user's ACCOUNT_STATUS was OPEN:

SQL> select account_status

  2  from dba_users

  3  where username = 'ANDREW'

  4  /

 

ACCOUNT_STATUS

--------------------------------

OPEN

 

SQL>

... and checked that he could login again:

SQL> conn andrew/reid

Connected.

SQL> show user

USER is "ANDREW"

SQL>