Oracle帐户口令强度策略限制

发发劳骚，移动的安全扫描TMD郁闷呀，各方面的问题。

1. 用户口令要求包含字母、数据和特殊字符三种类型

（1）创建密码检查函数

CREATE OR REPLACE FUNCTION 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) < 4 THEN      raise_application_error(-20002, 'Password length less than 4');   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;/

（2）创建密码检查profile及应用于业务用户
CREATE PROFILE profile_pwd LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
ALTER USER NDMC PROFILE profile_pwd; 




2. 用户口令要求数字和字母，不强制要求特殊字符


（1）创建密码检查函数

CREATE OR REPLACE FUNCTION verify_function(username varchar2,  password varchar2,  old_password varchar2)  RETURN boolean IS    n boolean;   m integer;   differ integer;   isdigit boolean;   ischar  boolean;   ispunct boolean;   db_name varchar2(40);   digitarray varchar2(20);   punctarray varchar2(25);   chararray varchar2(52);   i_char varchar2(10);   simple_password varchar2(10);   reverse_user varchar2(32);BEGIN    digitarray:= '0123456789';   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';   -- Check for the minimum length of the password   IF length(password) < 8 THEN      raise_application_error(-20001, 'Password length less than 8');   END IF;   -- Check if the password is same as the username or username(1-100)   IF NLS_LOWER(password) = NLS_LOWER(username) THEN     raise_application_error(-20002, 'Password same as or similar to user');   END IF;   FOR i IN 1..100 LOOP      i_char := to_char(i);      if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN        raise_application_error(-20005, 'Password same as or similar to user name ');      END IF;    END LOOP;   -- Check if the password is same as the username reversed      FOR i in REVERSE 1..length(username) LOOP     reverse_user := reverse_user || substr(username, i, 1);   END LOOP;   IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN     raise_application_error(-20003, 'Password same as username reversed');   END IF;   -- Check if the password is the same as server name and or servername(1-100)   select name into db_name from sys.v$database;   if NLS_LOWER(db_name) = NLS_LOWER(password) THEN      raise_application_error(-20004, 'Password same as or similar to server name');   END IF;   FOR i IN 1..100 LOOP      i_char := to_char(i);      if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN        raise_application_error(-20005, 'Password same as or similar to server name ');      END IF;    END LOOP;   -- 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 ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN      raise_application_error(-20006, 'Password too simple');   END IF;   -- Check if the password is the same as oracle (1-100)    simple_password := 'oracle';    FOR i IN 1..100 LOOP      i_char := to_char(i);      if simple_password || i_char = NLS_LOWER(password) THEN        raise_application_error(-20007, 'Password too simple ');      END IF;    END LOOP;   -- Check if the password contains at least one letter, one digit    -- 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(-20008, 'Password must contain at least one digit, one character');   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(-20009, 'Password must contain at least one \              digit, and one character');   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);     differ := abs(differ);     IF differ < 3 THEN       IF length(password) < length(old_password) THEN         m := length(password);       ELSE         m := length(old_password);       END IF;       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(-20011, 'Password should differ from the \            old password by at least 3 characters');       END IF;     END IF;   END IF;   -- Everything is fine; return TRUE ;      RETURN(TRUE);END;/

（2）创建密码检查profile及应用于业务用户
CREATE PROFILE profile_pwd LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
ALTER USER NDMC PROFILE profile_pwd;