Oracle帐户口令强度策略限制

Oracle帐户口令强度策略限制

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

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

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

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