Unlimited failed logon attempts is certainly convenient for developers and users to access the database, but it leaves a chance for brute force attack to crack the password, which may not a good practice for DBA.
In this post, we introduce a balanced way to trade off some degrees of convenience for a more secure environment.
Two attributes should be set for user's profile.
- FAILED_LOGIN_ATTEMPTS
- PASSWORD_LOCK_TIME
FAILED_LOGIN_ATTEMPTS defines how many times of consecutive failed login attempts are allowed before locking the account. Yes, the account is locked once the number of consecutive failed attempts meets the value.
An error ORA-28000: The account is locked will be thrown if user keeps trying to connect to the account. Subsequent attempts don't reset the initial lock time, no matter what password they carried.
PASSWORD_LOCK_TIME define a period of time before an account can be unlocked automatically by a successful connection, if the account lock was triggered by FAILED_LOGIN_ATTEMPTS.
When the account was locked, it might result in a halt on all normal operations and some chaos in the company. This could be abused by some users who expect that the account locking will be triggered after several unsuccessful attempts.
Alter Profile Limit
To go back to normal automatically in a reasonable waiting period, we combine both features and make a statement to restrict user's attempts.
In the above example, we allow 10 attempts at most before locking the account, but the lock will be automatically released in 1 hour. Assuming that user's profile is DEFAULT.
Normally, users will stop and beware of the incorrect password at their 3 to 5 attempts. So 10 FAILED_LOGIN_ATTEMPTS should be enough for them to remember the correct password.
A shorter PASSWORD_LOCK_TIME can also be considered in order to provide enough room for daily batch jobs. The value expression of PASSWORD_LOCK_TIME can be one of the followings.
| Unit | Expression |
|---|---|
| Days | n |
| Hours | n/24 |
| Minutes | n/1440 |
For example, 10 minutes which is expressed as 10/1440 in the statement.
In summary, such password management makes a balanced policy which considers failed logons and account lock time, and is very different from forcing users to change their password.