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>
SQL> conn andrew/reid
SQL> alter user andrew account unlock
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>
