Oracle Password Management Policy

maoj2008

Purpose:
~~~~~~~~
To understand Oracle's Password Management Policy features.
The available options are:

1. Account Locking
2. Password Aging and Expiration
3. Password History,
4. Password Complexity Verification.


Scope & Application:
~~~~~~~~~~~~~~~~~~~~
Oracle DBAs that require added security for password management.


Password Management Policy 
--------------------------
Password Management is setup by DBAs using Oracle Profiles. A Profile is setup 
with the required password parameters and then assigned to a user. Oracle 
provides a script $ORACLE_HOME/rdbms/admin/utlpwdmg.sql to setup password 
management features on the DEFAULT profile. Connect as SYS before running 
this script. DBAs can use it as a sample to see how the password management 
features are enabled.

Tip : Copy the utlpwdmg.sql script and customize it to your own needs
      using the Oracle developed verify function as a starting template.

Tip : Create a new profile an experiment with the feature first, for
      example: create profile custom limit PASSWORD_VERIFY_FUNCTION verify_function;
      To assign this profile to a user, use the following syntax:

      SQL> alter user scott profile custom;

There are currently 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 life time 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.

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


Once complexity checking is enabled, a user can change his/her password 
in a number of different ways:

4.1. Use the sqlplus 'password' command, for example:

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

4.2. Use the ALTER USER statement, for example:

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.

4.3. Any custom application using the OCIPasswordChange() call, see note 139748.1
for an example, 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.

ORA-28000 "the account is locked"
ORA-28001 "the password has expired"
ORA-28002 "the password will expire within %s days"
ORA-28003 "password verification for the specified password failed"
ORA-28007 "the password cannot be reused"
ORA-28008 "invalid old password"

Profile parameters: PASSWORD_VERIFY_FUNCTION 

Tip : To disable the verify function of a given profile, set it to NULL,
      for example: 

      SQL> alter profile default limit password_verify_function null;


Example 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/<password> as sysdba before running this.

CREATE OR REPLACE FUNCTION allways_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 allways_true;


Related Documents:
~~~~~~~~~~~~~~~~~~
Note:124648.1   ORA-28003 ORA-20001 ORA-20002 ORA-20003 ORA-20004 After Running UTLPWDMG.SQL
Note:98481.1    How to Keep the Same Password when Expiry Time is Reached and Change is Required
Note:162818.1   ORA-28002 On User Connection Immediately After PASSWORD_LIFE_TIME Changed
Note:1079860.6  ORA-28011 Password Expiry Date is Reached But Reset to NULL
Note:139676.1   ORA-28007: the password cannot be reused
Note:1083889.6  ORA-00931: missing identifier when PASSWORD_VERIFY_FUNCTION = UNLIMITED
Note:260111.1   How to interpret the ACCOUNT_STATUS column in DBA_USERS
Note:139748.1   Demonstrates the use of the new Oracle OCI8 OCIPasswordChange function 

bug:1231172     ENHANCEMENT: Add "REPLACE oldpassword" clause to ALTER USER command

Oracle