Oracle 11g User Account Status LOCKED(TIMED) with FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME Parameters

advertisements

_____________________________________________________________________________________________________________________

At least few times you might have noticed the account status is with LOCKED(TIMED). In this topic we will discuss about how it is happens.

See one example here.

SQL> SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES

  2  WHERE  PROFILE='DEFAULT' AND RESOURCE_NAME IN ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME');

RESOURCE_NAME                    LIMIT

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

FAILED_LOGIN_ATTEMPTS            1

PASSWORD_LOCK_TIME               1

SQL> alter user test profile DEFAULT;

User altered.

SQL> select username, account_status from dba_users where username ='TEST';

USERNAME                       ACCOUNT_STATUS

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

TEST                           OPEN

SQL> CONNECT TEST/A

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> CONNECT TEST/A

ERROR:

ORA-28000: the account is locked

After 2nd failure attempt the account got locked.

SQL> CONNECT / AS SYSDBA

Connected.

SQL> select username, account_status from dba_users where username ='TEST';

USERNAME                       ACCOUNT_STATUS

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

TEST                           LOCKED(TIMED)

Explanation

Oracle 10g onwards for DEFAULT profile all the resource parameter values are UNLIMITED except FAILED_LOGIN_ATTEMPTS. One more parameter needs to be checked for this timed account lock which is PASSWORD_LOCK_TIME. PASSWORD_LOCK_TIME parameter determines how many days/time the account should be locked mode after n number of failure attempts. These two parameters makes the account status to LOCKED(TIMED) when you try with wrong passwords.

To avoid this particular kind of unexpected locking you have to create a new profile with FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME values as UNLIMITED and assign this new profile to the user. It is not recommended to change the resource values to the DEFAULT profile.

1. Create A new profile

SQL> CREATE PROFILE NOEXPIRY LIMIT

  COMPOSITE_LIMIT UNLIMITED

  PASSWORD_LIFE_TIME UNLIMITED

  PASSWORD_REUSE_TIME UNLIMITED

  PASSWORD_REUSE_MAX UNLIMITED

  PASSWORD_VERIFY_FUNCTION NULL

  PASSWORD_LOCK_TIME UNLIMITED

  PASSWORD_GRACE_TIME UNLIMITED

  FAILED_LOGIN_ATTEMPTS UNLIMITED;

Profile created.

2. Assign the profile to the user

SQL> ALTER USER TEST PROFILE NOEXPIRY;

User altered.

3. Unlock the account.

SQL> ALTER USER TEST ACCOUNT UNLOCK;

User altered.

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='SCOTT';

USERNAME                       ACCOUNT_STATUS

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

TEST                          OPEN

I think this topic helped you. J

_____________________________________________________________________________________________________________________

Website Stats