Automatically Locking a User Account After a Failed Login

Oracle Database can lock a user's account after a specified number of consecutive failed log-in attempts. You can set the PASSWORD_LOCK_TIME user's profile parameter to configure the account to unlock automatically after a specified time interval or to require database administrator intervention to be unlocked. The database administrator also can lock accounts manually, so that they must be unlocked explicitly by the database administrator.

SQL> CREATE PROFILE prof LIMIT

     FAILED_LOGIN_ATTEMPTS 10

     PASSWORD_LOCK_TIME 30;

SQL> ALTER USER DHANA PROFILE prof;

Above query sets the maximum number of failed login attempts for the user DHANA to 10 (the default), and the amount of time the account locked to 30 days. The account will unlock automatically after30 days.

SQL> SELECT * FROM DBA_PROFILES
          WHERE resource_name = 'PASSWORD_LOCK_TIME';

If you do not specify a time interval for unlocking the account,  then  PASSWORD_LOCK_TIME assumes the value specified in a default profile. (The recommended value is 1 day.) If you specify PASSWORD_LOCK_TIME as UNLIMITED, then you must explicitly unlock the account by using an ALTER USER statement.

SQL> ALTER USER DHANA ACCOUNT UNLOCK;

After a user successfully logs into an account, Oracle Database resets the unsuccessful login attempt count for the user, if it is non-zero, to zero.

Controlling Password Aging and Expiration

SQL> CREATE PROFILE prof LIMIT

     FAILED_LOGIN_ATTEMPTS 4

     PASSWORD_LOCK_TIME 30

     PASSWORD_LIFE_TIME 180

     PASSWORD_GRACE_TIME 3;

SQL> ALTER USER DHANA PROFILE prof;

SQL> SELECT * FROM DBA_PROFILES  

     where resource_name IN                 

     ('PASSWORD_LOCK_TIME','PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME');