CodeSnip: Enforcing Unique Password Strength in an Oracle Database Based Upon a Role
page 1 of 1
Published: 20 Dec 2005
Unedited - Community Contributed
Abstract
This code snippet will allow you to enforce a unique password strength policy based upon the role assigned to a user in an Oracle Database.
by Steven Swafford
Feedback
Average Rating: 
Views (Total / Last 10 Days): 18262/ 23

Acknowledgement

First of all I want to extend a huge thanks to Roger Rowe for assisting with this function. Roger is by far the most knowledgeable Oracle DBA I have ever had the honor of working with!

Introduction

I was recently in a situation where we needed to enforce a password strength policy for elevated users with an Oracle database such as system accounts, system administrators, backup operators, etc. In the context of this example I am demonstrating accounts with an eight- character password and the other account I previously mentioned that must have a fifteen character password.

Figure 1: Basic User Password Length

Figure 2: Elevated User Password Length

 

As you can see in the above two figures, the password strength varies via the role assigned. To create the function to enforce the desired password strength, execute the following PL/SQL statement. Note: Be sure you are logged in with the account sys/<password> as DBA.

Listing 1: Password Verify Function

CREATE OR REPLACE FUNCTION password_verify_function

(username varchar2,

  password varchar2,

  old_password varchar2)

  RETURN boolean IS

   n boolean;

   m integer;

   differ integer;

   isdigit boolean;

   ischar  boolean;

   ispunct boolean;

   digitarray varchar2(20);

   punctarray varchar2(25);

   chararray varchar2(52);

   nPwdLength number;

        

BEGIN

   digitarray:= '0123456789';

   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

   punctarray:='!"#$%&()``*+,-/:;<=>?_';

        

   -- Check if the password is same as the username

   IF NLS_LOWER(password) = NLS_LOWER(username) THEN

     raise_application_error(-20001, 'Password same as or similar to user');

   END IF;

 

  select decode(count(*),0,8,15)

  into nPwdLength

  from dba_role_privs a

  where a.granted_role = 'DBA'

  start with a.grantee = username

  connect by prior a.granted_role = a.grantee;

        

   -- Check for the minimum length of the password

   IF length(password) < nPwdLength THEN

      raise_application_error(-20002, 'Password length less than ' || nPwdLength);

   END IF;

 

   -- Check if the password is too simple. A dictionary of words may be

   -- maintained and a check may be made so as not to allow the words

   -- that are too simple for the password.

   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password',

        'oracle', 'computer', 'abcd') THEN

      raise_application_error(-20002, 'Password too simple');

   END IF;

 

   -- Check if the password contains at least one letter, one digit and one

   -- punctuation mark.

   -- 1. Check for the digit

   isdigit:=FALSE;

   m := length(password);

   FOR i IN 1..10 LOOP

      FOR j IN 1..m LOOP

         IF substr(password,j,1) = substr(digitarray,i,1) THEN

            isdigit:=TRUE;

             GOTO findchar;

         END IF;

      END LOOP;

   END LOOP;

   IF isdigit = FALSE THEN

      raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');

   END IF;

   -- 2. Check for the character

   <<findchar>>

   ischar:=FALSE;

   FOR i IN 1..length(chararray) LOOP

      FOR j IN 1..m LOOP

         IF substr(password,j,1) = substr(chararray,i,1) THEN

            ischar:=TRUE;

             GOTO findpunct;

         END IF;

      END LOOP;

   END LOOP;

   IF ischar = FALSE THEN

      raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');

   END IF;

   -- 3. Check for the punctuation

   <<findpunct>>

   ispunct:=FALSE;

   FOR i IN 1..length(punctarray) LOOP

      FOR j IN 1..m LOOP

         IF substr(password,j,1) = substr(punctarray,i,1) THEN

            ispunct:=TRUE;

             GOTO endsearch;

         END IF;

      END LOOP;

   END LOOP;

   IF ispunct = FALSE THEN

      raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');

   END IF;

 

   <<endsearch>>

   -- Check if the password differs from the previous password by at least

   -- 3 letters

   IF old_password IS NOT NULL THEN

     differ := length(old_password) - length(password);

 

     IF abs(differ) < 3 THEN

       IF length(password) < length(old_password) THEN

         m := length(password);

       ELSE

         m := length(old_password);

       END IF;

 

       differ := abs(differ);

       FOR i IN 1..m LOOP

         IF substr(password,i,1) != substr(old_password,i,1) THEN

           differ := differ + 1;

         END IF;

       END LOOP;

 

       IF differ < 3 THEN

         raise_application_error(-20004, 'Password should differ by at least 3 characters');

       END IF;

     END IF;

   END IF;

   -- Everything is fine; return TRUE ;

   RETURN(TRUE);

END;

At this point you may execute a password change via calling this function and passing in the appropriate parameters or execute the following to enforce this policy.

Listing 2: Alter Default Parameters for Password Management

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME 60

PASSWORD_GRACE_TIME 10

PASSWORD_REUSE_TIME 1800

PASSWORD_REUSE_MAX UNLIMITED

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 1/1440

PASSWORD_VERIFY_FUNCTION password_verify_function;

As you can see from the following screens, this policy works nicely.

Figure 3: Password Too Simple

Figure 4: Password Same As Or Similar To User

Figure 5: Password Should Contain At Least One Digit, One Character and One Punctuation

 

In closing I have displayed how to enforce a password policy based on a given role. In this case the database administrator is required to have at a minimum of fifteen characters whereas all other uses must have at a minimum of eight characters. You can easily modify these restrictions by changing the password strength from listing 1, for example:

Listing 3: Modified Password Restriction

select decode(count(*),0,4,6)

into nPwdLength

from dba_role_privs a

where a.granted_role = 'DBA'

start with a.grantee = username

connect by prior a.granted_role = a.grantee;

Notice in the above listing a DBA is required to have at a minimum of six characters whereas all other uses must have at a minimum of four characters. I hope that I have demonstrated how easy it can be to enforce a password policy on an Oracle database now it is your turn to prevent that user from using something like user/123456 which we all know happens more often than not.



User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-10-19 5:41:21 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search