Overview
Password Management is setup by DBAs using Oracle Profiles. A general recommendation is to assign only those schemas a profile with password aging and expiration features if the users have the ability to independently change their password from their end-user application when the password expires, usually, this means the application they use must have correctly implemented the OCIPasswordChange() OCI call such as sqlplus.
A Profile is setup with the required password parameters and then assigned to a user:
SQL> create profile custom limit failed_login_attempts 20; Profile created.
SQL> alter user [user] profile custom; User altered.
Oracle provides the script $ORACLE_HOME/rdbms/admin/utlpwdmg.sql to setup password management features on the DEFAULT profile. DBAs can use it as a sample to see how the password management features are enabled. Copy the utlpwdmg.sql script and customize it to your own needs and then test it before implementing in production.
Currently there are 7 password management parameters that can be specified in a database profile. Each password management feature discussed below includes a reference to the relevant profile parameters.
1. Account Locking
When a user exceeds a designated number of failed login attempts (FAILED_LOGIN_ATTEMPTS), the server automatically locks that user’s account for a specified time period (PASSWORD_LOCK_TIME).
Profile parameters:
FAILED_LOGIN_ATTEMPTS PASSWORD_LOCK_TIME
2. Password Aging and Expiration
When the specified amount of time passes (PASSWORD_LIFE_TIME) the password expires, and the user or DBA must change the password. A grace period in days (PASSWORD_GRACE_TIME) can be set allowing the user time to change their password after it has expired. Users enter the grace period upon the first attempt to login to a database account after their password has expired. During the grace period, a warning message appears each time users try to log in to their accounts, and continues to appear until the grace period expires. Users must change the password within the grace period. If the password is not changed within the grace period, the account expires and no further logins to that account are allowed until the password is changed.
Note that a password cannot and will not be locked as a result of exceeding the lifetime and subsequent grace time, however, the user will not be able to login until the password is changed.
Profile parameters:
PASSWORD_LIFE_TIME PASSWORD_GRACE_TIME
3. Password History
A time interval during which users cannot reuse a password (PASSWORD_REUSE_TIME). This can be specified as either a time interval in days or a number of password changes the user must make before the current password can be reused (PASSWORD_REUSE_MAX).
Profile parameters:
PASSWORD_REUSE_TIME PASSWORD_REUSE_MAX
4. Password Complexity Verification
DBAs can create their own password verification routines using PL/SQL and then instruct the server to use this routine to check the complexity of the passwords while they are set.
Profile parameter:
PASSWORD_VERIFY_FUNCTION
The SYS owned PL/SQL function must adhere to 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 present in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. This can be used as an example and modified according to your needs. This function can be associated with a profile this way:
sql> alter profile default limit password_verify_function [routine_name];
To disable the verify function of a given profile, set it to NULL:
SQL> alter profile default limit password_verify_function null;
Once complexity checking is enabled, a user can change his/her password in a number of different ways:
– Using the sqlplus ‘password’ command:
SQL> connect [user] Connected. SQL> password Changing password for [user] Old password: New password: Retype new password: Password changed SQL>
– Using the ALTER USER statement:
SQL> ALTER USER [MYUSERNAME] IDENTIFIED BY [NEWPASSWORD] REPLACE [OLDPASSWORD];
The ALTER USER syntax using the REPLACE keyword was added as part of the fix to Bug 1231172 so this syntax will work in all currently supported releases. Any custom application using the OCIPasswordChange() call. This can be used by application developers to develop customer-friendly screens, when developing such an application it is important to generate the proper responses to the following exceptions associated with password management feature.
Example of using all Password Management features previously discussed:
-- A default password complexity function is provided. -- This sample function makes no checks and always returns true. -- The logic in the function should 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;