ORACLE密碼策略驗證程序

Oracle密碼策略非常重要，下面就為您詳細介紹Oracle密碼策略驗證程序，如果您對Oracle密碼策略方面感興趣的話，不妨一看。

密碼字符串要求：

-- Check if the passWord is same as the username
-- Check for the minimum length of the passWord
-- Check if the passWord contains at least one letter, one digit and one
-- Check if the password differs from the previous passWord by at least

CREATE OR REPLACE FUNCTION SYS.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);

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;

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