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笔记 http://www.linuxidc.com/Linux/2013-07/86805.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htmOracle 11g 强制修改密码 ORA-28001 的处理方法 http://www.linuxidc.com/Linux/2012-10/71666.htm--------------------------------------------------------------------------------更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址