Oracle profile 对账号密码做限制

1、创建profile

CREATE PROFILE Account_PROFILE LIMIT
   SESSIONS_PER_USER UNLIMITED
   CPU_PER_SESSION UNLIMITED
   CPU_PER_CALL UNLIMITED
   CONNECT_TIME UNLIMITED
   IDLE_TIME UNLIMITED
   LOGICAL_READS_PER_SESSION UNLIMITED
   LOGICAL_READS_PER_CALL UNLIMITED
   COMPOSITE_LIMIT UNLIMITED
   PRIVATE_SGA UNLIMITED
   FAILED_LOGIN_ATTEMPTS 4    --指定账号失败的登陆次数为4次后锁定账号
   PASSWORD_LIFE_TIME 76      --指定账号密码过期的天数为76天
   PASSWORD_REUSE_TIME UNLIMITED
   PASSWORD_REUSE_MAX UNLIMITED
   PASSWORD_LOCK_TIME 3      --指定账号登陆失败被锁定后锁定的天数为3天
   PASSWORD_GRACE_TIME 14 --指定账号密码过期后还可以继续使用的天数为14天
   PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;   --指定账号密码复杂度验证的函数为 VERIFY_FUNCTION_11G，密码复杂度验证函数必须位于sys schema下

2、应用profile

ALTER USER user_name PROFILE account_profile;

3、构建账号密码复杂度验证函数，对账号密码长度、复杂度进行控制

CREATE OR REPLACE FUNCTION sys.verify_function_11G
(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';
    punctarray:='!"#$%&()``*+,-/:;<=>?_';

   -- Check for the minimum length of the password
    IF length(password) < 8 THEN   --密码字符长度必须大于或等于8位
       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','oracle') 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 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 endsearch;
          END IF;
       END LOOP;
    END LOOP;
    IF ischar = FALSE THEN
       raise_application_error(-20009, 'Password must contain at least one digit,one character and one punctuation');
    END IF;
    <<endsearch>>
   -- 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 endsearchpunct;
          END IF;
       END LOOP;
    END LOOP;
    IF ispunct = FALSE THEN
       raise_application_error(-20003, 'Password must contain at least one digit,one character and one punctuation');
    END IF;

<<endsearchpunct>>

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

oracle 提供了函数构建模板，可以根据需要自行修改：

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

4、查看profile

select * from dba_profiles