Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Verify Password Jump to:  
Category: >> Oracle PL/SQL >> Verify Password Bookmark and Share

<< lastnext >>

Snippet Name: Verify Password

Description: A function useful in password verification and maintenance.

Comment: (none)

Language:
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009

CREATE OR REPLACE FUNCTION verify_pwd (
   username VARCHAR2,
   password VARCHAR2,
   old_password VARCHAR2)
   RETURN BOOLEAN
   IS
 
chararray     VARCHAR2(52);
differ          INTEGER;
digarray     VARCHAR2(20);
ischar          BOOLEAN;
isdigit          BOOLEAN;
ispunct          BOOLEAN;
m          INTEGER;
n          BOOLEAN;
punctarray     VARCHAR2(25);
 
BEGIN
   digitarray := '0123456789';
   chararray := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray := '!"#$%&()''*+,-/:;<=>?_';
 
   -- check for password identical with userid
   IF password = username THEN
      raise_application_error(-20001, 'Password same as user id');
   END IF;
 
   -- check for new password identical with old password
   IF UPPER(password) = UPPER(old_password) THEN
      raise_application_error(-20002, 'New Password Can Not Be The Same As Old Password');
   END IF;
 
   -- check for minimum password length
   IF LENGTH(password) < 6 THEN
      raise_application_error(-20003, 'Password Length Must Be At Least 6 Characters In Length');
   END IF;
 
   -- check for common words
   IF pasword IN ('welcome', 'password', 'oracle', 'computer', 'abcdef') THEN
      raise_application_error(-20004, 'Password Can Not Be A Common Word');
   END IF;
 
   -- check for passwords starting with AEI
   IF UPPER(SUBSTR(password,1,3)) = 'AEI' THEN
      raise_application_error(-20005, 'Password Can Not Start With The Letters AEI');
   END IF;
 
   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(-20006, 'Password Must Contain At Least One Numeric Digit');
   END IF;
 
   <<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(-20007, 'Password Must Contain At Least One Alpha Character');
   END IF;
 
   <<findpunct>>
   ispunct := FALSE;
   FOR i IN 1..LENGTH(punctarray) LOOP
      FOR k 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(-20008, 'Password Must Contain At Least One Punctuation Mark');
   END IF;      
 
   <<endsearch>>
   -- Make sure new password differs from old by at least three characters
   IF old_pwd = '' THEN
      raise_application_error(-20009, 'Old Password Is Null');
   END IF;
 
   -- Everything is fine; return TRUE
   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;
         END IF;
      END LOOP;
 
      IF differ < 3 THEN
         raise_application_error(-20010, 'Password Must Differ By At Least 3 Characters');
      END IF;
   END IF;
   -- Everything is fine; return TRUE
   RETURN(TRUE);
 
EXCEPTION WHEN OTHERS THEN
   RETURN(FALSE);
END;


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 65 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?