Oracle Password Management Policy

maoj2008 发表于 2011-12-22 08:54

<pre>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.6ORA-28011 Password Expiry Date is Reached But Reset to NULL Note:139676.1 ORA-28007: the password cannot be reused Note:1083889.6ORA-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

页: [1]

Chinaunix's Archiver

Oracle Password Management Policy