DBA Arena

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;