"Translated from MOS article" Oracle Password Management Policy

Oracle Password Management Policy

Reference text:
Oracle Password Management Policy (Doc ID 114930.1)

Details:
Password management is established by using profile.

When the password expires, if the user is able to change the password independently from the end-user application (foreground business software), the usual recommendation is to assign only those schemas a profile, which has password aging an Expiration features.
Typically this means that application (front Office software) must correctly use Ocipasswordchange () OCI call, such as Sqlplus

A profile can be created when the password parameter is specified, and the profile is 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 the password management features on the DEFAULT profile.
The DBA can use it (the script) as an example to see how the Password management feature is enabled.
Copy the script and customize the script to suit your needs, test the script (or the script you define yourself) before you make the production

In Oracle database profile, there are 7 password-managed parameters that can be specified. The following are discussed separately:

1. Account Locking

When a user exceeds the number of failed logins assigned to him (failed_login_attempts), Oracle DB automatically lock the user's account, which lasts for a duration of Password_lock_ Time (the Password_lock_time is the resource in the profile) specified.

Profile parameters:
Failed_login_attempts
Password_lock_time

2. Password Aging and expiration
When the time specified in Password_life_time is exceeded, the password is 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 the user to change their password after the password expired until grace period.
User access to grace period is based on the moment when their password expired and the first time they landed in db. During grace period, a warning message is displayed every time a user logs in to DB, and the message continues to appear until grace period expired. In grace period period, the user must change the password, if the grace period period does not change the password, then the account expired and will not be allowed to log in until the password is modified.

Note: Passwords are not and cannot be locked, even because they exceed life time and later Grace time. However, the user cannot login unless the password is changed.

Profile parameters:
Password_life_time
Password_grace_time

3. Password History

The time interval at which the user cannot reuse the original password 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 the meaning of this password_reuse_max is: Before the current password can be reused, the user must change many times the password, that is, Password_reuse_max is the specified number of password change?

4. Password Complexity Verification
DBAs can use PL/SQL to establish their own password verification routines (password Validator), and then allow Oracle DB to use that routine to check the complexity of the password.

Profile parameter:
Password_verify_function

The PL/SQL function owned by SYS must conform to the following format:
Routine_name (Userid_parameter in VARCHAR2, Password_parameter in VARCHAR2, Old_password_parameter in VARCHAR2) RETURN BO Olean

The default password validation function is in the $ORACLE _home/rdbms/admin/utlpwdmg.sql file. This file can be used as an example or modified according to your needs.

The function can be used by the profile association.
Alter profile default limit password_verify_function <routine_name>;

Disable the password validation function on a default profile as follows:
sql> alter profile default limit password_verify_function null;

Password complex authentication once enabled, users can modify their own passwords in a number of ways:

First method: Sqlplus's password command
Sql> Connect Scott/tiger
Connected.
sql> Password
changing password for SCOTT
Old Password:
New Password:
Retype new Password:
Password changed
Sql>

The second method: alter the user command:
Sql> ALTER USER &myusername identified by &newpassword REPLACE &OLDPASSWORD;

The ALTER user syntax for using the Replace keyword is part of fixing the bug 1231172 scenario, so the syntax can be used on all currently supported release.

The third method: The foreground business software uses Ocipasswordchange () call.

Here 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 should is modified as required.
--See $ORACLE _home/rdbms/admin/utlpwdmg.sql for a idea of kind
--of logic that can is used.
--This function must is created in SYS schema.
--Connect Sys/as SYSDBA before running this.

-This function would not check the provided password. It is just an example and
--would 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, the users on the system has Password Management
--enabled and set to the following values unless another profiles 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--(days)
Password_grace_time-(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;