Profile是Oracle提供的一种针对用户资源使用和密码管理的策略配置。借助Profile，可以实现特定用户资源上的限制和密码管理规则的应用。在实际的应用中，Profile可以帮助我们实现很多应用层面比较困难实现的需求。默认情况下，用户连接数据库，形成会话，使用CPU资源和内存资源是没有限制的。在一些应用并发量很大，特别是多个应用部署在同一个数据库服务器上的时候，依据应用对企业重要程度的部分，CPU和内存资源的分配一定是有所侧重的。 此外，用户的密码管理，可以是一个比较复杂的工作。比如，用户锁定之后，多长时间被自动释放、密码生命周期、登录尝试次数等等。这两个方面的问题，都可以借助Profile去解决。Profile相当于一个命名的安全策略集合，其中规定了资源使用的限制和密码使用的规则。Profile定义之后，是可以应用到每个用户上，对每个用户的安全活动进行限制。
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- -------------------------------- -------- -----------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
上面标红的项目为数据库用户密码复杂度要求函数，不同企业根据自身规章制度，对数据库用户密码复杂度要求不一，需要根据自身要求编写各自的复杂度函数，以下分享笔者所使用的复杂度限制函数源码，该函数限制策略为至少12位，且同时包含数字、字母和特殊字符。
CREATE OR REPLACE FUNCTION verify_function_11G_dx
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
issign boolean;
ispunct boolean;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
signarray varchar2(50);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
signarray:= '@!#$%^&*()_-=+*,.?;~`';
-- Check for the minimum length of the password
IF length(password) < 12 THEN
raise_application_error(-20001, 'Password length less than 12');
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','123456','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');
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 findsign;
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;
-- 2.1 Check for the sign
<<findsign>>
issign:=FALSE;
FOR i IN 1..length(signarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(signarray,i,1) THEN
issign:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF issign = FALSE THEN
raise_application_error(-20010, 'Password must contain at least one \
digit, and one character , and one sign');
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;
/
GRANT EXECUTE ON verify_function_11G_dx TO PUBLIC;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69994536/viewspace-2756708/ ，如需转载，请注明出处，否则将追究法律责任。
