Oracle数据库密码相关参数设置

数据库的用户密码管理是非常有意义的对于IT安全的审计管理都起到了重要的作用。再次介绍一下oracle关于用户名密码的管理。

 

Linux系统下以oracle用户登录系统，运行以下命令

$ sqlplus /nolog

 

SQL> conn / as sysdba

Connected.

 

SQL> @?/rdbms/admin/utlpwdmg.sql

Function created.

Profile altered.

 

查看当前默认的概要文件中密码参数相关设置

SQL> set wrap off

SQL> col profile format a10

SQL> select * from dba_profiles

where profile='DEFAULT' and resource_type='PASSWORD';

 

PROFILE    RESOURCE_NAME                    RESOURCE LIMIT
---------- -------------------------------- -------- ---------------------------
DEFAULT    FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT    PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT    PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G
DEFAULT    PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT    PASSWORD_GRACE_TIME              PASSWORD 7

7 rows selected.

 

参数说明：

FAILED_LOGIN_ATTEMPTS：最大登录失败次数

PASSWORD_LOCK_TIME：登录超过有效次数后的锁定时间(天)

PASSWORD_LIFE_TIME：密码有效时间(天)

PASSWORD_GRACE_TIME：密码失效后的锁定时间(天)

PASSWORD_REUSE_MAX：密码历史记录保留次数

PASSWORD_REUSE_TIME：密码历史记录保留时间(天)

PASSWORD_VERIFY_FUNCTION：密码复杂度验证函数

 

可按以下格式修改密码参数

SQL> ALTER PROFILE DEFAULT LIMIT

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 1/1440

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_GRACE_TIME 10

PASSWORD_REUSE_TIME 1800

 

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_VERIFY_FUNCTION verify_function;

 

默认的密码复杂度验证函数 verify_function 代码如下：

可修改或添加代码以实现相应的密码复杂度验证，例如将蓝色字体中的4改成6并重新运行，则要求密码长度必须在6位以上

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

   <>

   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

   <>

   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;

 

   <>

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