Oracle数据库密码相关参数设置
Linux系统下以oracle用户登录系统，运行以下命令
$ sqlplus /nolog
SQL> conn / as sysdba
Connected.oracle登录命令
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 3
DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006
DEFAULT PASSWORD_LIFE_TIME PASSWORD 60
DEFAULT PASSWORD_GRACE_TIME PASSWORD 10
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD 1800
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION
参数说明：
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 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 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 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
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论