7.4.1 Account Locking
When a particular user exceeds a designated number of failed login attempts, the server automatically locks that user account. You specify the permissible number of failed login attempts using the CREATE PROFILE statement. You can also specify the amount of time accounts remain locked.
In the following example, the maximum number of failed login attempts for the user johndoe is four, and the amount of time the account will remain locked is 30 days. The account will unlock automatically after the passage of 30 days.
CREATE PROFILE prof LIMIT
ALTER USER johndoe PROFILE prof;
If you do not specify a time interval for unlocking the account, then PASSWORD_LOCK_TIME assumes the value specified in a default profile. If you specify PASSWORD_LOCK_TIME as UNLIMITED, then the account must be explicitly unlocked using an ALTER USER statement. For example, assuming that PASSWORD_LOCK_TIME UNLIMITED is specified for johndoe, then the following statement must be used to unlock the account:
ALTER USER johndoe ACCOUNT UNLOCK;
After a user successfully logs into an account, the unsuccessful login attempt count for the user, if it exists, is reset to 0.
The security officer can also explicitly lock user accounts. When this occurs, the account cannot be unlocked automatically, and only the security officer should unlock the account. The CREATE USER or ALTER USER statements are used to explicitly lock or unlock user accounts. For example, the following statement locks the user account, susan:
ALTER USER susan ACCOUNT LOCK;