ORACLE密码策略验证程序-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; 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

-- 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; 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

-- 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;   
 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.

【编辑推荐】