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>

ORA-02382

This was tested on Oracle 11.2. I wanted to see if you could drop a profile if somebody was using it. First I created a profile.

SQL> create profile andrews_profile

  2  limit failed_login_attempts 3

  3  /

Profile created. 

SQL> 

Then I assigned the profile to a user:

SQL> create user andrew identified by reid

  2  profile andrews_profile

  3  /

User created.

SQL> select profile from dba_users

  2  where username = 'ANDREW'

  3  /

PROFILE

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

ANDREWS_PROFILE 

SQL> 

I tried to drop the profile but this failed as it was in use:

SQL> drop profile andrews_profile

  2  /
drop profile andrews_profile

*

ERROR at line 1:

ORA-02382: profile ANDREWS_PROFILE has users assigned,

cannot drop without CASCADE

SQL>

I ran the command again, adding CASCADE at the end:

SQL> drop profile andrews_profile cascade

  2  /

Profile dropped.

SQL>

This dropped the profile and assigned the DEFAULT profile to ANDREW instead:

SQL> select profile from dba_users

  2  where username = 'ANDREW'

  3  /

PROFILE

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

DEFAULT

SQL>

Reply to: Reply to Andrew Reid

Send

Failed Login Attempts

(Tested on an Oracle 9.2.0.7.0 database.)

This can be used to limit the number of times a user can enter an incorrect password. First create a test user and show what profile he is using:

  1  grant create session to andrew

  2* identified by reid

SQL> /

Grant succeeded.

SQL> select profile from dba_users

  2  where username = 'ANDREW';

PROFILE

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

DEFAULT

SQL>

Then change that profile so that only two incorrect password attempts are allowed:

SQL> alter profile default

  2  limit failed_login_attempts 2;

Profile altered.

SQL>

To demonstrate the limit, the user must then try to login twice with the wrong password:

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

SQL>

If he tries to login again he will see that the account is locked:

SQL> conn andrew/wrong_password

ERROR:

ORA-28000: the account is locked

SQL>

Note that this limit still applies even if resource_limit is set to false:

SQL> col value format a20

SQL> l

  1  select value from v$parameter

  2* where name = 'resource_limit'

SQL> /

VALUE

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

FALSE

SQL>

Also note that the failed login attempts need to be consecutive. A successful login attempt sets the count of failed login attempts back to zero:

SQL> conn / as sysdba

Connected.

SQL> alter user andrew account unlock;

User altered.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL>