5
Sep

Enforcing password complexity for database users

It is recommended to enforce checks and response actions for failed logins, password complexity, password expiration and password reuse.
 
Password complexity can be implemented through profiles.
 
The script for password management implementation utlpwdmg.sql can be used to create a new function to verify password complexity rules.
 
Verify Function is a quick and easy way to enforce quality of database passwords—for example, they should contain a certain number of characters, should not be identical to the username, and so on.
 
By executing this script utlpwdmg.sql, it will attach the function to the profile DEFAULT, which is the default profile for all users.
 

The following password complexity is set with the 'utlpwdmg.sql' script.
 

  • Password length less than 8
  • Password same as or similar to user
  • Password same as username reversed
  • Password same as or similar to server name
  • Password too simple
  • Password must contain at least one digit, one character
  • Password must contain at least one digit, and one character
  • Password should differ from the old password by at least 3 characters
  •  
    Oracle 11g includes a new view called DBA_USERS_WITH_DEFPWD, which displays all users which have a default password set.
    SELECT * FROM dba_users_with_defpwd;
     
    Current password settings:
    SELECT * FROM user_password_limits;
     

    Password Parameters:

     

  • FAILED_LOGIN_ATTEMPTS 3 -- When a user exceeds a designated number of failed login attempts. Account locked after 3 failed logins.
  •  

  • PASSWORD_LOCK_TIME 5 -- Number of days account is locked for. UNLIMITED requires explicit unlock by DBA.
  •  

  • PASSWORD_LIFE_TIME 45 -- When the specified amount of time passes the password expires, and the user or DBA must change the password. Password expires after 45 days.
  •  

  • PASSWORD_GRACE_TIME 3 -- 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.
    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.
     

  • PASSWORD_REUSE_TIME 1 -- A time interval during which users cannot reuse a password. UNLIMITED means never.
  •  

  • PASSWORD_REUSE_MAX 10 -- The number of password changes the user must make before a password can be reused. UNLIMITED means never.
  •  
    PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX

     
    These two parameters must be set in conjunction with each other.
     

  • PASSWORD_REUSE_TIME specifies the number of days before which a password cannot be reused.
  •  

  • PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. For these parameter to have any effect, you must specify an integer for both of them.
  •  
    If you specify an integer for both of these parameters, then the user cannot reuse a password until the password has been changed the number of times specified for PASSWORD_REUSE_MAX during the number of days specified for PASSWORD_REUSE_TIME.
     
    If you specify an integer for either of these parameters and specify UNLIMITED for the other, then the user can never reuse a password.
     
    Setting the password complexity
     
    Check the current values for the DEFAULT profile in the database.
     
    set pagesize 1000;
    col profile for a22;
    col RESOURCE_NAME for a25;
    col limit for a22;
    select * from dba_profiles where profile ='DEFAULT' and resource_name in ('PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME','PASSWORD_REUSE_TIME','PASSWORD_REUSE_MAX','FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME','PASSWORD_VERIFY_FUNCTION');

    PROFILE RESOURCE_NAME RESOURCE LIMIT
    ---------------------- ------------------------- -------- ----------------------
    DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
    DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
    DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
    DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
    DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
    DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006
    DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED

    7 rows selected.


    Run the script in the database to create the function 'verify_function_11g'

    cd $ORACLE_HOME/rdbms/admin
     
    sqlplus / as sysdba
     
    SQL> @utlpwdmg.sql

    Function created.

    Profile altered.

    Function created.


    These are the default values that get set after running the utlpwdmg.sql script.

    set pagesize 1000;
    col profile for a22;
    col RESOURCE_NAME for a25;
    col limit for a22;
    select * from dba_profiles where profile ='DEFAULT' and resource_name in ('PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME','PASSWORD_REUSE_TIME','PASSWORD_REUSE_MAX','FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME','PASSWORD_VERIFY_FUNCTION');

    PROFILE RESOURCE_NAME RESOURCE LIMIT
    ---------------------- ------------------------- -------- ----------------------
    DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
    DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
    DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
    DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G
    DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
    DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
    DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED



    You can change these profile values as per your requirements.

    ALTER PROFILE DEFAULT LIMIT
    PASSWORD_LIFE_TIME 45
    PASSWORD_REUSE_MAX 10
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;



    set pagesize 1000;
    col profile for a22;
    col RESOURCE_NAME for a25;
    col limit for a22;
    select * from dba_profiles where profile ='DEFAULT' and resource_name in ('PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','FAILED_LOGIN_ATTEMPTS','PASSWORD_VERIFY_FUNCTION');

    PROFILE RESOURCE_NAME RESOURCE LIMIT
    ---------------------- ------------------------- -------- ----------------------
    DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
    DEFAULT PASSWORD_LIFE_TIME PASSWORD 45
    DEFAULT PASSWORD_REUSE_MAX PASSWORD 10
    DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G


    Create a new user.
    create user shakeeb identified by Shakeeb123;
     
    grant connect,resource to shakeeb;
     
    Change the password.
    alter user shakeeb identified by abcd1234;
     
    Try to change the password to the first set password i.e Shakeeb123
    alter user shakeeb identified by Shakeeb123;
     
    ERROR at line 1:
    ORA-28007: the password cannot be reused

    Password complexity for a particular profile.

     

    Modify the utlpwdmg.sql file to delete everything after the function is created i.e from the block which will alter the default profile.

    Run the script in the database to create the function 'verify_function_11g'


    cd $ORACLE_HOME/rdbms/admin
    sqlplus / as sysdba
    SQL> @utlpwdmg.sql

     
    Verify whether the DEFAULT profile has been altered in any way while running the utlpwdmg.sql script.
     
    The DEFAULT profile should look unchanged like below:

    set pagesize 1000;
    col profile for a22;
    col RESOURCE_NAME for a25;
    col limit for a22;
    select * from dba_profiles where profile ='DEFAULT' and resource_name in ('PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME','PASSWORD_REUSE_TIME','PASSWORD_REUSE_MAX','FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME','PASSWORD_VERIFY_FUNCTION');

    PROFILE RESOURCE_NAME RESOURCE LIMIT
    ---------------------- ------------------------- -------- ----------------------
    DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
    DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
    DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
    DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
    DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
    DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006
    DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED

    7 rows selected.

    Create a new profile with the password complexity values.

    CREATE PROFILE CUSTOM_AUDIT LIMIT
    PASSWORD_LIFE_TIME 45
    PASSWORD_REUSE_MAX 10
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;



    This 'CUSTOM_AUDIT' profile should look like below:

    set pagesize 1000;
    col profile for a22;
    col RESOURCE_NAME for a25;
    col limit for a22;
    select * from dba_profiles where profile ='CUSTOM_AUDIT' and resource_name in ('PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME','PASSWORD_REUSE_TIME','PASSWORD_REUSE_MAX','FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME','PASSWORD_VERIFY_FUNCTION');

    PROFILE RESOURCE_NAME RESOURCE LIMIT
    ---------------------- ------------------------- -------- ----------------------
    CUSTOM_AUDIT FAILED_LOGIN_ATTEMPTS PASSWORD 3
    CUSTOM_AUDIT PASSWORD_LIFE_TIME PASSWORD 45
    CUSTOM_AUDIT PASSWORD_REUSE_TIME PASSWORD DEFAULT
    CUSTOM_AUDIT PASSWORD_REUSE_MAX PASSWORD 10
    CUSTOM_AUDIT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G
    CUSTOM_AUDIT PASSWORD_LOCK_TIME PASSWORD DEFAULT
    CUSTOM_AUDIT PASSWORD_GRACE_TIME PASSWORD DEFAULT

    7 rows selected.

    Create a user to test the password complexity working.
     
    grant connect,resource to shakeeb identified by shakeeb;

    Grant succeeded.
     

    SQL> select username,profile from dba_users where username='SHAKEEB';

    USERNAME PROFILE
    ------------------------------ ----------------------
    SHAKEEB DEFAULT



    Assign this user the new 'CUSTOM_AUDIT' profile created.
     
    alter user shakeeb profile CUSTOM_AUDIT;
     
    select username,profile from dba_users where username='SHAKEEB';

    USERNAME PROFILE
    ------------------------------ ----------------------
    SHAKEEB CUSTOM_AUDIT

     
    SQL> alter user shakeeb identified by shakeeb1;
    alter user shakeeb identified by shakeeb1
    *
    ERROR at line 1:
    ORA-28003: password verification for the specified password failed
    ORA-20005: Password same as or similar to user name

     
    SQL> alter user shakeeb identified by abcd1234;

    User altered.
     
    SQL> alter user shakeeb identified by Shakeeb;
    alter user shakeeb identified by Shakeeb
    *
    ERROR at line 1:
    ORA-28003: password verification for the specified password failed
    ORA-20001: Password length less than 8

     
    SQL> alter user shakeeb identified by Shakeebxx;
    alter user shakeeb identified by Shakeebxx
    *
    ERROR at line 1:
    ORA-28003: password verification for the specified password failed
    ORA-20008: Password must contain at least one digit, one character
     

    SQL> alter user shakeeb identified by Shakeeb123;

    User altered.
     
    SQL> alter user shakeeb identified by abcd1234;
    alter user shakeeb identified by abcd1234
    *
    ERROR at line 1:
    ORA-28007: the password cannot be reused
     
    SQL> alter user shakeeb identified by welcome123;

    User altered.
     
    SQL> conn shakeeb/welcome123
    Connected.
     
    SQL> show user
    USER is "SHAKEEB"

    Remove the password complexity

     
    ALTER PROFILE DEFAULT LIMIT
    PASSWORD_GRACE_TIME UNLIMITED
    PASSWORD_LIFE_TIME UNLIMITED
    PASSWORD_REUSE_TIME UNLIMITED
    PASSWORD_REUSE_MAX UNLIMITED
    FAILED_LOGIN_ATTEMPTS UNLIMITED
    PASSWORD_REUSE_TIME .0006
    PASSWORD_VERIFY_FUNCTION NULL;

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Back to Top