sábado, 28 de agosto de 2010

Função para verificação de senha - Oracle

CREATE OR REPLACE  FUNCTION "SYS"."SYS_PASSWD_VALIDATION" 
    (username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   digitarray varchar2(32);
   chararray varchar2(52);
   digi integer;


BEGIN
   digitarray:= '0123456789!"#$%&()``*+,-/:;<=>?_';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';


   -- Check if the password is same as the username
   IF password = username THEN
     raise_application_error(-20001, 'Password must differ from userid');
   END IF;


   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20002, 'Password length less than 8');
   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', 'abcdefgh') 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


   digi:=0;
   m := length(password);
   FOR i IN 1..length(digitarray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            digi:=digi+1;
            IF digi >1 THEN
               GOTO findchar;
            END IF;
         END IF;
      END LOOP;
   END LOOP;
   IF digi < 2 THEN
      raise_application_error(-20003, 'Password should contain at least two alphanumeric characters');
   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 endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              alphabetic character');
   END IF;


   <<endsearch>>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password = '' THEN
      raise_application_error(-20004, 'Old password is null');
   END IF;
   -- Everything is fine; return TRUE ;
   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;
          else
dbms_output.put_line('OLD:'||substr(password,i,1));
dbms_output.put_line('NEW:'||substr(old_password,i,1));
          END IF;
      END LOOP;
      IF differ < 2 THEN
          raise_application_error(-20004, 'Password should differ by at \
            least 2 characters');
      END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;

Nenhum comentário:

Postar um comentário