Implement Password Policy in Oracle Database

Script to implement password policy in Oracle database

CREATE OR REPLACE FUNCTION VERIFY_FUNCTION_11G (username varchar2, password varchar2, old_password varchar2)

  RETURN boolean IS
     
     m                    integer;
     differ               integer;
     isdigit              boolean;
     islowerchar          boolean;
     isupperchar          boolean;
     ispunct              boolean;
     db_name              varchar2(40);
     digitarray           varchar2(20);
     punctarray           varchar2(25);
     lowerchararray       varchar2(30);
     upperchararray       varchar2(30);
     i_char               varchar2(10);
     simple_password      varchar2(10);
     reverse_user         varchar2(32);
     lv_count             number;

BEGIN
   digitarray     := '0123456789';
   lowerchararray := 'abcdefghijklmnopqrstuvwxyz'; 
   upperchararray := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray     := '!"#$%&()''*+,-/:;<=>?_';
   lv_count       := 0;

   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20001, 'Password length less than 8');
   END IF;

   -- Check if the password is same as the username or username(1-100)
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20002, 'Password same as or similar to user');
   END IF;
   
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to user name ');
      END IF;
   END LOOP;
   -- Check if the password is same as the username reversed
   FOR i in REVERSE 1..length(username) LOOP
     reverse_user := reverse_user || substr(username, i, 1);
   END LOOP;
   
   IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
     raise_application_error(-20003, 'Password same as username reversed');
   END IF;
   -- Check if the password is the same as server name and or servername(1-100)
   select name into db_name from sys.v$database;
   
   IF NLS_LOWER(db_name) = NLS_LOWER(password) THEN
      raise_application_error(-20004, 'Password same as or similar to server name');
   END IF;
   
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to server name ');
      END IF;
   END LOOP;

   -- 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 ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
      raise_application_error(-20006, 'Password too simple');
   END IF;
   -- Check if the password is the same as oracle (1-100)
    simple_password := 'oracle';
    FOR i IN 1..100 LOOP
      i_char := to_char(i);
      IF simple_password || i_char = NLS_LOWER(password) THEN
        raise_application_error(-20007, 'Password too simple ');
      END IF;
    END LOOP;
   -- Check if the password contains at least one letter, one digit
   -- 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;
             EXIT;  -- Exit from Loop
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = TRUE THEN
     lv_count := lv_count + 1;
   END IF;
   
   -- 2. Check for the lower case character
   islowerchar:=FALSE;
   FOR i IN 1..length(lowerchararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(lowerchararray,i,1) THEN
            islowerchar:=TRUE;
            EXIT;  -- Exit from Loop
         END IF;
      END LOOP;
   END LOOP;
   
   IF islowerchar = TRUE THEN
      lv_count := lv_count + 1;
   END IF;
   -- 3. Check for the upper case character
   isupperchar:=FALSE;
   FOR i IN 1..length(upperchararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(upperchararray,i,1) THEN
              isupperchar:=TRUE;
              EXIT;  -- Exit from Loop
         END IF;
      END LOOP;
   END LOOP;
   
   IF isupperchar = TRUE THEN
      lv_count := lv_count + 1;
   END IF;
      
   -- 4. Check for the Special character
   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;
              EXIT;  -- Exit from Loop
         END IF;
      END LOOP;
   END LOOP;
   
   IF ispunct = TRUE THEN
      lv_count := lv_count + 1;
   END IF;
   
   IF lv_count < 3 THEN
      raise_application_error(-20008, 'Password must contain one Upper case , one Lower case , one digit and one special character (Any three of four)');
   END IF;
   -- 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);
     differ := abs(differ);
     IF differ < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;
       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(-20011, 'Password should differ from the old password by at least 3 characters');
       END IF;
     END IF;
   END IF;
   
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/