Using user profiles to enforce password policies

A user profile controls user password policies and resource control. Every user has an allocated profile.

The DEFAULT profile will be assigned if another profile is not specified for a user. It is recommended that you use your own custom profiles to enforce password aging policies, strong passwords, and resource utilization. In this recipe, we will create a customized profile named CUSTPROF that establishes a strong password policy through the use of password related profile resources and the use of a password verification function.

Getting ready

All the steps will be performed on the HACKDB database.

How to do it...

The profile CUSTPROF will be assigned to the HR user in the following steps:

  1. Create a new profile named CUSTPROF using the following statement:
    SQL> create profile custprof limit password_reuse_max 15;
Profile created.
  2. To find information about the profile CUSTPROF, issue the following query:
    SQL> select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where profile='CUSTPROF';

PROFILE       RESOURCE_NAME                LIMIT
------------------------------ ------------------------------------------
CUSTPROF                       COMPOSITE_LIMIT                  DEFAULT
CUSTPROF                       SESSIONS_PER_USER                DEFAULT
CUSTPROF                       CPU_PER_SESSION                  DEFAULT
CUSTPROF                       CPU_PER_CALL                     DEFAULT
CUSTPROF                       LOGICAL_READS_PER_SESSION        DEFAULT
CUSTPROF                       LOGICAL_READS_PER_CALL           DEFAULT
CUSTPROF                       IDLE_TIME                        DEFAULT
CUSTPROF                       CONNECT_TIME                     DEFAULT
CUSTPROF                       PRIVATE_SGA                      DEFAULT
CUSTPROF                       FAILED_LOGIN_ATTEMPTS            DEFAULT
CUSTPROF                       PASSWORD_LIFE_TIME               DEFAULT
CUSTPROF                       PASSWORD_REUSE_TIME              DEFAULT
CUSTPROF                       PASSWORD_REUSE_MAX               15
CUSTPROF                       PASSWORD_VERIFY_FUNCTION         DEFAULT
CUSTPROF                       PASSWORD_LOCK_TIME               DEFAULT
CUSTPROF                       PASSWORD_GRACE_TIME              DEFAULT

16 rows selected.

SQL>
  3. Set password life time period to 50 days and the grace time to 10 days as follows:
    SQL> alter profile custprof limit PASSWORD_LIFE_TIME 50 PASSWORD_GRACE_TIME 10;

Profile altered.
SQL>
  4. Limit the failed login attempts to 15 and password lock time to 3 days by setting the following profile resources:
    SQL> alter profile custprof limit FAILED_LOGIN_ATTEMPTS 15 PASSWORD_LOCK_TIME 3;

Profile altered.
SQL>
  5. Set the password history policy resources, the number of days after which a password cannot be reused to 20 and the number of changes before which a password cannot be reused to 1.
    SQL> alter profile custprof limit PASSWORD_REUSE_TIME 20 PASSWORD_REUSE_MAX 1;

Profile altered.

SQL>
  6. Connect as the user system and create the password verify function, as follows:
    SQL> @?/rdbms/admin/utlpwdmg.sql

Function created.


Profile altered.


Function created.

SQL>
  7. Next we will modify the password verify function. The code is located in the downloadable code bundle of this chapter; here are listed only some parts along with the modified code lines. We will increase the minimum password size from 8 to 15 characters, and we will even make use of the special characters.
    create or replace FUNCTION verify_function_11G
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   isspecchar boolean;
.................................................
   reverse_user varchar2(32);
   specialchars varchar2(30);
BEGIN
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   specialchars:= '~@#$%^&*()[];;.><?|:_=+-'; -- add special characters as manadatory 
   -- Check for the minimum length of the password
   IF length(password) < 15 THEN
      raise_application_error(-20001, 'Password length less than 8'),
   END IF;
  
   3. Check for the special characters

   isspecchar:=FALSE;
   FOR i IN 1..length(specialchars) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(specialchars,i,1) THEN
             isspecchar:=TRUE;
             GOTO findspecialchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isspecchar = FALSE THEN
      raise_application_error(-20009, 'Password must contain at least one 
              digit, one character, and one special character such as ~@#$%^&*()[];;.><?|:_=+-'),
   END IF;
   <<findspecialchar>>
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
  8. Add the function to a profile by modifying the password_verify_function resource, as follows:
    SQL> alter profile custprof limit password_verify_function verify_function_11g;

Profile altered.
  9. Finally, assign the custprof profile to the user HR as follows:
    SQL> alter user hr profile custprof;
User altered.

SQL>

How it works...

It is recommended that you have separate profiles for a group of users, such as application users, administrative users, and so on. In this way, very sensitive users may have more restrictive password policies than others.

There's more...

Profiles can also be used to control user resource management by modifying the following resources:

CUSTPROF                       COMPOSITE_LIMIT                  DEFAULT
CUSTPROF                       SESSIONS_PER_USER                DEFAULT
CUSTPROF                       CPU_PER_SESSION                  DEFAULT
CUSTPROF                       CPU_PER_CALL                     DEFAULT
CUSTPROF                       LOGICAL_READS_PER_SESSION        DEFAULT
CUSTPROF                       LOGICAL_READS_PER_CALL           DEFAULT
CUSTPROF                       PRIVATE_SGA                      DEFAULT

And we can set the maximum connection idle time and connect time by modifying the following resources:

CUSTPROF                       IDLE_TIME                        DEFAULT
CUSTPROF                       CONNECT_TIME                     DEFAULT
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.223.160