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; |