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

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.

  1. Assign the profile to the user
SQL> ALTER USER TEST PROFILE NOEXPIRY;

User altered.

  1. 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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google