ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle profile 使用笔记

oracle profile 使用笔记

原创 Linux操作系统 作者:is.x 时间:2011-05-15 16:40:01 0 删除 编辑

Oracle profile中的参数定义分为两部分:Resource_parameterPassword_parameter

 

可以通过dba_profiles查询

 

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

------------------------------ -------------------------------- -------- ----------------------------------------

DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED

DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED

DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED

DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED

DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED

DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED

DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED

DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED

DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED

DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED

DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED

 

其中,password resource总是可用,kernel resource需要设置系统参数resource_limit后才能生效。

 

SQL>alter system set resource_limit=true;

 

具体的resource_name对应的含义可见下表:

 

资源

描述

SESSIONS_PER_USER

在一个实例中,一个用户可以同时拥有的会话数量

CPU_PER_SESSION

一个会话可以使用的CPU时间,以百分之一秒为单位

CPU_PER_CALL

语法分析、执行或获取可以使用的CPU时间,以百分之一秒为单位

CONNECT_TIME

一个会话可以连接到一个数据库的分钟数

IDLE_TIME

一个会话可以连接到一个数据库而没有激活使用的分钟数

LOGICAL_READS_PER_SESSION

可以在一个会话中读取的数据库块数

LOGICAL_READS_PER_CALL

在语法分析、执行或获取期间可以读取的数据库块数

PRIVATE_SGA

SGASQL共享池中,一个会话可以分配的私有空间量(对于MTS

COMPOSITE_LIMIT

一个基于前面的限制的复合限制

FAILED_LOGIN_ATTEMPTS

将引起一个账号被锁定的连续注册失败次数

PASSWORD_LIFE_TIME

一个口令在其终止前可以使用的天数

PASSWORD_REUSE_TIME

一个口令在能够被重新使用之前所必须经过的天数

PASSWORD_REUSE_MAX

一个口令在能够被重新使用之前必须改变的次数

PASSWORD_LOCK_TIME

如果超过FAILED_LOGIN_ATTEMPTS设置值,一个账号将被锁定的天数

PASSWORD_GRACE_TIME

以天为单位的“宽限时间”,在宽限期内,在口令达到PASSWORD_LOGIN_TIME设置值时,仍能对其修改

PASSWORD_VERIFY_FUNCTION

一个函数名,用于判断口令的复杂性

 

这里比较特殊的是PASSWORD_VERIFY_FUNCTION,该参数定义的是一个函数名,如果不需要定义密码策略,可以设置为NULL

 

SQL>alter profile DEFAULT limit password_verify_function null;

 

如果需要设置密码策略,可以参照$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

 

oracle@ibmvs_a@/oracle/product/10.2.0/rdbms/admin $ cat utlpwdmg.sql

Rem

Rem $Header: utlpwdmg.sql 31-aug-2000.11:00:47 nireland Exp $

Rem

Rem utlpwdmg.sql

Rem

Rem  Copyright (c) Oracle Corporation 1996, 2000. All Rights Reserved.

Rem

Rem    NAME

Rem      utlpwdmg.sql - script. for Default Password Resource Limits

Rem

Rem    DESCRIPTION

Rem      This is a script. for enabling the password management features

Rem      by setting the default password resource limits.

Rem

Rem    NOTES

Rem      This file contains a function for minimum checking of password

Rem      complexity. This is more of a sample function that the customer

Rem      can use to develop the function for actual complexity checks that the

Rem      customer wants to make on the new password.

Rem

Rem    MODIFIED   (MM/DD/YY)

Rem    nireland    08/31/00 - Improve check for username=password. #1390553

Rem    nireland    06/28/00 - Fix null old password test. #1341892

Rem    asurpur     04/17/97 - Fix for bug479763

Rem    asurpur     12/12/96 - Changing the name of password_verify_function

Rem    asurpur     05/30/96 - New script. for default password management

Rem    asurpur     05/30/96 - Created

Rem

 

-- This script. sets the default password resource parameters

-- This script. needs to be run to enable the password features.

-- However the default resource parameters can be changed based

-- on the need.

-- A default password complexity function is also provided.

-- This function makes the minimum complexity checks like

-- the minimum length of the password, password not same as the

-- username, etc. The user may enhance this function according to

-- the need.

-- This function must be created in SYS schema.

-- connect sys/ as sysdba before running the script

 

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;

/

 

-- This script. alters the default parameters for Password Management

-- This means that all the users on the system have Password Management

-- enabled and set to the following values unless another profile is

-- created with parameter values set to different value or UNLIMITED

-- is created and assigned to the user.

 

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME 60

PASSWORD_GRACE_TIME 10

PASSWORD_REUSE_TIME 1800

PASSWORD_REUSE_MAX UNLIMITED

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 1/1440

PASSWORD_VERIFY_FUNCTION verify_function;

 

utlpwdmg.sql在创建好function后,修改了DEFAULT profile的相关参数。但在很多时候,我们希望对不同的用户定义不同的profile策略。下面给出一个简单的例子:

 

需要定义的参数如下:

 

IDLE_TIME 60               会话空闲60分钟

Failed_login_attempts10     密码尝试次数

PASSWORD_LIFE_TIME 90    密码生命周期

PASSWORD_LOCK_TIME   5/1440    密码锁定时间5分钟

PASSWORD_REUSE_MAX  4    密码更改4次以上才可重用

PASSWORD_REUSE_TIME  30    密码更改超过30天后可重用

PASSWORD_GRACE_TIME  30    密码过期后可延续使用30

PASSWORD_VERIFY_FUNCTION 

密码长度 >=8

密码要求字母+数字

 

具体步骤如下:

 

1.       创建verify_function

CREATE 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) < 8 THEN

      raise_application_error(-20002, 'Password length less than 8');

   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.

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

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

         END IF;

      END LOOP;

   END LOOP;

   IF ischar = FALSE THEN

      raise_application_error(-20003, 'Password should contain at least one digit, one character');

   END IF;

   <>  

   -- Everything is fine; return TRUE ;  

   RETURN(TRUE);

END;

/

 

2.       修改resource_limit参数

SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH;

 

3.       创建用户profile

CREATE PROFILE USER_PROFILE LIMIT

IDLE_TIME 60

PASSWORD_LIFE_TIME 90

PASSWORD_GRACE_TIME 30

PASSWORD_REUSE_TIME 30

PASSWORD_REUSE_MAX 4

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LOCK_TIME 5/1440

PASSWORD_VERIFY_FUNCTION verify_function;

 

4.       修改指定用户profile

SQL>alter user profile user_profile;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20750200/viewspace-695411/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-27

  • 博文量
    73
  • 访问量
    265258