Oracle帳戶口令強度策略限制

Oracle帳戶口令強度策略限制

Oracle帳戶口令強度策略限制

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;

--------------------------------------------------------------------------------

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle 11g 強制修改密碼 ORA-28001 的 處理方法

--------------------------------------------------------------------------------