oracle profile介绍

profile文件的介绍：

　Oracle系统中的profile可以用来对用户所能使用的数据库资源进行限制，使用Create Profile命令创建一个Profile，
用它来实现对数据库资源的限制使用，如果把该profile分配给用户，则该用户所能使用的数据库资源都在该profile的限制之内。

1、查看信息
select * from dba_users;--查看用户的参数
select * from dba_profiles;--显示所有profile文件及其限制
select * from user_password_limits;--描述指定给用户的profile中的口令限制情况
select * from user_resource_limits;--描述指定给用户的profile中的资源限制情况
select * from resource_cost;--列举每个资源的价值

2、profile的参数含义
COMPOSITE_LIMIT：一个会话的资源成本限制
SESSIONS_PER_USER：一个用户允许同时(并发)会话的总数，超过后系统禁止该用户的后续会话
CPU_PER_SESSION：定义了一个SESSION占用的CPU的时间（1/100 秒），当达到这个限制用户不能在此会话中执行任何操作，此时必须先断开连接，再连接才行
CPU_PER_CALL：限制每次调用SQL语句期间(parse、execute、fetch)可用的CPU时间总量，单位是百分之一秒。
              达到限制时语句以报错结束。不同于CPU_PER_SESSION的是，没达到CPU_PER_SESSION限制，还可以进行新的查询
LOGICAL_READS_PER_SESSION：一个会话允许读写的逻辑块的数量限制
LOGICAL_READS_PER_CALL：一次调用的SQL期间，允许读数据库块数限制
IDLE_TIME：一个连接会话的最长连接时间；当用户空闲时间超过IDLE_TIME指定的时间，Oracle将回滚当前事务，并且结束他的会话
CONNECT_TIME：一个连接会话的最长连接时间（分钟）；当用户会话时间超过CONNECT_TIME指       定的时间，Oracle将回滚当前事务，并且结束他的会话
PRIVATE_SGA：一个会话允许分配的最大SGA大小，使用K/M定义
FAILED_LOGIN_ATTEMPTS：当连续登陆失败次数达到该参数指定值时，用户被加锁；
PASSWORD_LIFE_TIME：口令的有效期（天），默认为UNLIMITED
PASSWORD_REUSE_TIME：口令被修改后原有口令隔多少天被重新使用，默认为UNLIMITED
PASSWORD_REUSE_MAX：口令被修改后原有口令被修改多少次才允许被重新使用
PASSWORD_VERIFY_FUNCTION：口令效验函数
PASSWORD_LOCK_TIME：帐户因FAILED_LOGIN_ATTEMPTS锁定时，加锁天数
PASSWORD_GRACE_TIME：口令修改的宽限期（天）

3、profile创建和参数修改
--创建一个简单profile，限制每个用户的会话数
SQL> create profile pf_all limit sessions_per_user 20;

--指定用户使用profile限制
为用户指定资源限制，必须：
动态地使用alter system或使用初始化参数resource_limit使资源限制生效。该改变对密码资源无效，密码资源总是可用。
SQL> show parameter resource_limit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
SQL> alter system set resource_limit=true;
系统已更改。
SQL> show parameter resource_limit;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TRUE
SQL> alter user tina profile pf_all;

--修改profile参数
SQL>alter PROFILE pf_all limit sessions_per_user 10;

--删除一个新的尚未分配给用户的PROFILE
SQL>DROP PROFILE pf_all;

--删除已创建的profile，如果已经指定了用户则要加上cascade关键字
SQL>DROP PROFILE pf_all cascade;
注：DEFAULT PROFILE不可以被删除

--设置密码可失败次数
SQL>alter profile default limit  failed_login_attempts unlimited;

--设置密码生命周期
SQL>alter profile default limit PASSWORD_LIFE_TIME unlimited;

4、创建在创建新用户与老用户密码修改时验证复杂度的函数   --以前同事写的
CREATE OR REPLACE FUNCTION SYS.tina_passwd_complex
/*（(1)长度：10位以上 (2)复杂度：必须为英文字母+特殊字符+数字  (3)密码不能与用户名相同 (4)密码需要至少有3个字符与之前的口令不相同）*/
(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) < 10 THEN
      raise_application_error(-20002, 'Password length less than 10');
   END IF;

   -- Check if the password is too simple.
   IF NLS_LOWER(password) IN ('database','user', 'password', 'oracle') 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;
/

5、创建profile
CREATE PROFILE pw_profile LIMIT
FAILED_LOGIN_ATTEMPTS 10        /*连续登录10次失败，则用户被锁*/
PASSWORD_LOCK_TIME 999          /*如果用户连续3次登录失败，账号被锁，指定被锁的时间长度*/
PASSWORD_GRACE_TIME 7           /*提示密码过期，仍能用原密码登录的天数*/
PASSWORD_LIFE_TIME 90          /*密码有效的天数，即需要用户至少90天修改一次密码*/
PASSWORD_REUSE_TIME UNLIMITED  /*需要多少天后，该密码可以被重用，这里天数不受限制*/
PASSWORD_REUSE_MAX 10          /*当前密码被重用之前，最少需要修改10次密码 */
PASSWORD_VERIFY_FUNCTION tina_passwd_complex  /*使用的密码规则验证的函数*/
SESSIONS_PER_USER 5
IDLE_TIME 10;

6、将所有profile均使用tina_passwd_complex函数，以使任何用户修改密码都必须符合复杂度函数
declare
  sql_alter varchar2(200);
begin
   for cur in(select distinct profile as profile_name from dba_profiles) loop
   sql_alter:='ALTER PROFILE '||cur.profile_name||' limit password_verify_function tina_PASSWD_COMPLEX';
   execute immediate sql_alter;       
   end loop;
end;
/
commit;

7.若不在需要，可删除函数和profile
SQL> drop function sys.tina_passwd_complex;

Function dropped.

SQL> drop profile pw_profile;

Profile dropped.

SQL> alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION null; --将所有涉及上面函数的profile都修改

Profile altered.