Friday, January 03, 2014

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 log in 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 time can vary quite a bit. You seem to be able to speed up the change of the user’s ACCOUNT_STATUS by trying to login with the correct password from another server session:

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>

No comments: