6.6. Implementing Standard Password Security Features

For users who are configured for database authentication, password security rules are enforced with profiles and password-complexity rules with verification functions. Profiles have a set of standard rules that define how long a password can remain valid, the elapsed time, the number of password changes before a password can be reused, the number of failed login attempts that will lock the account, and how long the account will remain locked.

If you want a parameter to inherit the setting from the DEFAULT profile, set the parameter's value to the keyword DEFAULT. Explicitly assign password rules to a profile using the CREATE PROFILE or ALTER PROFILE statement. These profile assignment statements support the following clauses to configure the standard password rules:

FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME The FAILED_LOGIN_ATTEMPTS parameter specifies how many times in a row the user can fail password authentication.

If this limit is breached, the account is locked for PASSWORD_LOCK_TIME days. If the PASSWORD_ LOCK_TIME parameter is set to UNLIMITED and a user exceeds FAILED_LOGIN_ATTEMPTS, the account must be manually unlocked. You can set these parameters in a CREATE PROFILE or ALTER PROFILE statement like this:

-- lock account for 10 minutes if 3 consecutive logins fail
CREATE PROFILE agent LIMIT
   FAILED_LOGIN_ATTEMPTS 3
   PASSWORD_LOCK_TIME 10/1440;

-- remove failed login restrictions
ALTER PROFILE student LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

-- manually unlock an account
ALTER USER scott ACCOUNT UNLOCK;

PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME The PASSWORD_LIFE_TIME parameter specifies the maximum number of days that a password can remain in force, and the PASSWORD_GRACE_TIME is the number of days after the first successful login following password expiration, during which the user will be reminded to change their password, but allowed to log in. After the PASSWORD_GRACE_TIME limit is reached, the user must change their password. If you set PASSWORD_LIFE_TIME to a value and set PASSWORD_GRACE_TIME to UNLIMITED, users will be reminded to change their password every time they log in, but never forced to actually do so. You can set these two parameters in a CREATE PROFILE or ALTER PROFILE statement like this:

-- limit the password lifetime to 90 days
-- during the last 14 days the user will be reminded
-- to change the password
CREATE PROFILE agent LIMIT
   PASSWORD_LIFE_TIME 90 - 14
   PASSWORD_GRACE_TIME 14;

-- set no limit to password lifetime
ALTER PROFILE student LIMIT
    PASSWORD_LIFE_TIME UNLIMITED
    PASSWORD_GRACE_TIME DEFAULT;

PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX The PASSWORD_REUSE_TIME parameter specifies the minimum number of days that must transpire before a password can be reused. PASSWORD_REUSE_MAX specifies the minimum number of password changes that must occur before a password can be reused. If you specify a value for one of these two parameters and UNLIMITED for the other, passwords can never be reused. If you set both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX to UNLIMITED, these parameters are essentially disabled. You can set these password parameters in a CREATE PROFILE or ALTER PROFILE statement like this:

-- require at least 4 password changes and 1 year
-- before a password may be reused.
CREATE PROFILE agent LIMIT
   PASSWORD_REUSE_TIME 365
   PASSWORD_REUSE_MAX 4;

-- remove password reuse constraints
ALTER PROFILE student LIMIT
    PASSWORD_REUSE_TIME UNLIMITED
    PASSWORD_REUSE_MAX UNLIMITED;

Several password attributes are durations expressed in days. These durations are normally set with integer values, such as 30, 90, or 365 days. But decimal values are supported as well. You can set the password timeout to 5 minutes (5/1,440 days) or 5 seconds (5/86,400 days). Using a fractional number of days is a great way to try out combinations of values and observe the results of setting these password rules.

PASSWORD_VERIFY_FUNCTION The PASSWORD_VERIFY_FUNCTION parameter lets you codify additional rules that will be verified when a password is changed. These rules usually verify password complexity such as minimal password length or check that a password does not appear in a dictionary. The PASSWORD_VERIFY_FUNCTION must be created under the user SYS and must have three pass parameters of type VARCHAR2. These pass parameters must contain the username in the first parameter, the new password in the second, and the old password in the third. You can set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this:

-- use a custom password function
CREATE PROFILE agent LIMIT PASSWORD_VERIFY_FUNCTION my_function;

-- disable use of a custom function
ALTER PROFILE student LIMIT PASSWORD_VERIFY_FUNCTION DEFAULT;