[Translated from mos] oracle Password Management Policy

[Translated from mos] oracle Password Management Policy

Oracle Password Management Policy

Reference Original:
Oracle Password Management Policy (docid 114930.1)

Details:
Password Management is created by using profile.

After the password expires, if the user has the ability to change the password independently from the end-user application (front-end business software), it is generally recommended to assign only one profile to These schemas, this profile has password aging an expiration features.
This usually means that the application (front-end business software) must use OCIPasswordChange () OCI call correctly, such as sqlplus

A profile can be created when the password parameter is specified, and then assigned to a user

SQL> create profile custom limit failed_login_attempts 20;
Profile created.

SQL> alter user scott profile custom;
User altered.

Oracle provides a script ($ ORACLE_HOME/rdbms/admin/utlpwdmg. SQL) to implement password management features on the DEFAULT profile.
Dba can use this script as an example to view how the password management feature is enabled.
Copy the script and customize it as needed. Test the script before production (or your own defined script)

In oracle database profile, seven password management parameters can be specified. The following are discussed respectively:

1. Account Locking

When a user exceeds the number of failed logins (FAILED_LOGIN_ATTEMPTS) assigned to him, oracle db automatically locks the user's account ), the lock duration is the time specified by PASSWORD_LOCK_TIME (the PASSWORD_LOCK_TIME is the resource in the profile.

Profile parameters:
FAILED_LOGIN_ATTEMPTS
PASSWORD_LOCK_TIME

2. Password Aging and Expiration
After the time specified in PASSWORD_LIFE_TIME expires, the password will be expire, and the user or dba must change the password. A grace period (in days, that is, the period specified by PASSWORD_GRACE_TIME) can be set to allow users to change their passwords after the password is expired until the grace period ends.
The user enters grace period based on the time when their password expired and they log on to the database for the first time. During grace period, a warning message is displayed after each user logs on to the database until grace period expired. During grace period, you must change the password. If you do not change the password during grace period, the account is expired and cannot be logged in until the password is modified.

Note: The password cannot be locked, even because it exceeds the life time and later grace time. However, the user cannot log in unless the password is changed.

Profile parameters:
PASSWORD_LIFE_TIME
PASSWORD_GRACE_TIME

3. Password History

The user cannot reuse the original password. The time interval is (PASSWORD_REUSE_TIME. The interval can be specified in days,
Or a number of password changes the user must make before the current password can be reused (PASSWORD_REUSE_MAX ).
---> It seems that this PASSWORD_REUSE_MAX indicates that the password must be changed many times before it can be reused. That is to say, PASSWORD_REUSE_MAX indicates the number of times the password is changed?

4. Password Complexity Verification
DBAs can use PL/SQL to establish their own password verification routines (password verification program), and then oracle db can use this routine to check the password complexity.

Profile parameter:
PASSWORD_VERIFY_FUNCTION

The PL/SQL functions owned by sys must follow the following format:
Routine_name (userid_parameter IN VARCHAR2, password_parameter IN VARCHAR2, old_password_parameter IN VARCHAR2) RETURN BOOLEAN

The default password verification function is in the $ ORACLE_HOME/rdbms/admin/utlpwdmg. SQL file. This file can be used as an example or modified as needed.

This function can be associated with the profile.
Alter profile default limit password_verify_function <routine_name>;

The following describes how to disable the password verification function on a default profile:
SQL> alter profile default limit password_verify_function null;

Once complex password verification is enabled, users can modify their own passwords in many ways:

Method 1: sqlplus password command
SQL> connect scott/tiger
Connected.
SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:
Password changed
SQL>

Method 2: alter user command:
SQL> ALTER USER & MYUSERNAME IDENTIFIED BY & NEWPASSWORD REPLACE & OLDPASSWORD;

The alter user syntax using the replace keyword is part of the bug 1231172 solution. Therefore, this syntax can be used on all currently supported release.

Method 3: the front-end business software uses OCIPasswordChange () call.

The following is an example:
-- A default password complexity function is provided.
-- This sample function makes no checks and always returns true.
-- The logic in the function shocould be modified as required.
-- See $ ORACLE_HOME/rdbms/admin/utlpwdmg. SQL for an idea of kind
-- Of logic that can be used.
-- This function must be created in SYS schema.
-- Connect sys/as sysdba before running this.

-- This function will not check the provided password. It is just an example and
-- Will return true for any password. For a real password verification routine see
-- Script $ ORACLE_HOME/rdbms/admin/utlpwdmg. SQL.

Create or replace function always_true (username varchar2,
Password varchar2, old_password varchar2) RETURN boolean IS
BEGIN
RETURN (TRUE );
END;
/

-- This script alters the default parameters for Password Management.
-- This means that all the users on the system have Password Management
-- Enabled and set to the following values unless another profile is
-- Created with parameter values set to different value or UNLIMITED
-- Is created and assigned to the user.

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60 -- (days)
PASSWORD_GRACE_TIME 10 -- (days)
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3 -- (times)
PASSWORD_LOCK_TIME 1/1440 -- (days)
PASSWORD_VERIFY_FUNCTION always_true;