Saturday, June 03, 2017

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>

3 comments:

  1. For your reference, the status are listed in user_astatus_map

    STATUS# STATUS
    ---------- --------------------------------
    0 OPEN
    1 EXPIRED
    2 EXPIRED(GRACE)
    4 LOCKED(TIMED)
    8 LOCKED
    5 EXPIRED & LOCKED(TIMED)
    6 EXPIRED(GRACE) & LOCKED(TIMED)
    9 EXPIRED & LOCKED
    10 EXPIRED(GRACE) & LOCKED


    Cheers
    Laurent

    ReplyDelete
  2. Good to hear from you again, Laurent, and thank you for the information. I will try to fit it into a future post.

    ReplyDelete

  3. Iam so thrilled because of finding your alluring website here.Actually i was searching for Oracle DBA.Your blog is so astounding and informative too..Iam very happy to find such a creative blog. Iam also find another one by mistake while am searching the same topicOracle APEX.Thank you soo much..

    ReplyDelete