How does the profile password security setting work?

Oracles profile settings for password security can be somewhat confusing at first, particularly when you consider the ramification of the default values.

Here are some of my notes on the matter:

The PASSWORD_LOCK_TIME sets the number of days an account will be locked after the specified number of consecutive failed login attempts. After the time passes, then the account becomes unlocked. If you specify PASSWORD_LOCK_TIME as UNLIMITED, then the account must be explicitly unlocked using an ALTER USER statement.

The FAILED_LOGIN_ATTEMPTS specifies the number of failed attempts to log in to the user account before the account is locked. The default is three failed attempts.

The PASSWORD_LIFE_TIME specifies the number of days the same password can be used for authentication.
The default setting of UNLIMITED will cause the database to issue a warning but let the user continue to connect indefinitely.

The PASSWORD_GRACE_TIME specifes the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires and no further logons to the account is allowed. If you also set a value for PASSWORD_GRACE_TIME, then the password expires if it is not changed within the grace period, and further connections are rejected.

The PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused. UNLIMITED means never, you can always reuse the password.

The PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. UNLIMITED means never, you are ignoring the setting altogether.

These two parameters PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX must be set in conjunction with each other. For these parameter to have any effect, you must specify an integer for both of them.

* If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.
For example, if you specify PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse the password after 30 days if the password has already been changed 10 times.
* If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.
* If you specify DEFAULT for either parameter, then Oracle Database uses the value defined in the DEFAULT profile. By default, all parameters are set to UNLIMITED in the DEFAULT profile.
If you have not changed the default setting of UNLIMITED in the DEFAULT profile, then the database treats the value for that parameter as UNLIMITED.
* If you set both of these parameters to UNLIMITED, then the database ignores both of them.


The PASSWORD_VERIFY_FUNCTION clause lets a PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement.
Oracle Database provides a default script, but you can create your own routine or use third-party software instead.

* For function, specify the name of the password complexity verification routine.
    * Specify NULL to indicate that no password verification is performed.

Example:

The first time scott tries to log in to the database after 90 days (this can be any day after the 90th day, that is, the 70th day, 100th day, or another day), he receives a warning message that his password will expire in three days.
If three days pass, and he does not change his password, then the password expires.
After this, he receives a prompt to change his password on any attempt to log in, and cannot log in until he does so.

CREATE PROFILE prof 
 LIMIT
 FAILED_LOGIN_ATTEMPTS 4 <-- number of login attempts is 4
 PASSWORD_LOCK_TIME 30   <-- account will be locked for 30 days on the 5th attempt to logon
 PASSWORD_LIFE_TIME 90   <-- the password is valid for 90 days
 PASSWORD_GRACE_TIME 3;  <-- after 90 days, the user has 3 days to change it. 

ALTER USER scott PROFILE prof;

Another example, where I alter the default profile to require the following minimum of any new user:

ALTER PROFILE "DEFAULT" LIMIT
 PASSWORD_LIFE_TIME 90     <-- Rewnewal of password every 90 days
 PASSWORD_GRACE_TIME 10    <-- I give the user 10 days grace period
 PASSWORD_REUSE_MAX 12     <-- Ensure at least 12 different passwords before reusing a password
 PASSWORD_REUSE_TIME 90    <-- The number of days that must pass before a password can be reused
 FAILED_LOGIN_ATTEMPTS 5   <-- Allow 5 attempts to logon
 PASSWORD_LOCK_TIME .0208  <-- After 5 unsuccessful logons, lock account for 30 min, then allow new attempts
 PASSWORD_VERIFY_FUNCTION VERIFY_PASSWORD; <-- enforce password complexity as desired

To reveal if the account was locked as a consequence of a violation of the FAILED_LOGIN_ATTEMPTS, look at the STATUS column of the DBA_USERS view: If the value shows "LOCKED(TIMED)" then you know you're dealing with a user who has tried to log on too many times.