ORACLE profile系列4，oracleprofile系列

    这篇博客是ORACLE profile系列的第四篇，主要说一下，如果创建profile和使用profile进行资源和密码控制

CREATE PROFILE

Note:

Oracle recommends that you use the Database Resource Manager rather than this SQL statement to establish resource limits. The Database Resource Manager offers a more flexible means of managing and tracking resource use. For more information on the Database Resource Manager, refer to Oracle Database Administrator's Guide.

Purpose

Use the CREATE PROFILE statement to create a profile, which is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits.

See Also:

Oracle Database Security Guide for a detailed description and explanation of how to use password management and protection

Prerequisites

To create a profile, you must have the CREATE PROFILE system privilege.

To specify resource limits for a user, you must:

• Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter does not apply to password resources. Password resources are always enabled.

• Create a profile that defines the limits using the CREATE PROFILE statement

• Assign the profile to the user using the CREATE USER or ALTER USER statement

##创建并使profile生效的前提条件是：

要想成功创建profile，用户必须具有create profile权限

如果想使profile中指定的限制对相关用户生效，首先我们需要把该profile指定给用户，其次我们需要开启数据库的resource_limit功能。(可以在数据库启动之前在参数文件中指定RESOURCE_LIMIT初始化参数，或者直接使用alter system set resource_limit=true;来启用)

See Also:

• ALTER SYSTEM for information on enabling resource limits dynamically

• Oracle Database Reference for information on the RESOURCE_LIMIT parameter

• CREATE USER and ALTER USER for information on profiles

Syntax

create_profile::=

Description of the illustration create_profile.gif

resource_parameters::=

Description of the illustration resource_parameters.gif

(size_clause::=

password_parameters ::=

Description of the illustration password_parameters.gif

 

 

Examples

Creating a Profile: Example The following statement creates the profile new_profile:

CREATE PROFILE new_profile
  LIMIT PASSWORD_REUSE_MAX 10
        PASSWORD_REUSE_TIME 30;

Setting Profile Resource Limits: Example The following statement creates the profile app_user:

CREATE PROFILE app_user LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000; 

If you assign the app_user profile to a user, then the user is subject to the following limits in subsequent sessions:

• The user can have any number of concurrent sessions.

• In a single session, the user can consume an unlimited amount of CPU time.

• A single call made by the user cannot consume more than 30 seconds of CPU time.

• A single session cannot last for more than 45 minutes.

• In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.

• A single call made by the user cannot read more than 1000 data blocks from memory and disk.

• A single session cannot allocate more than 15 kilobytes of memory in the SGA.

• In a single session, the total resource cost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the ALTER RESOURCE COST statement.

• Since the app_user profile omits a limit for IDLE_TIME and for password limits, the user is subject to the limits on these resources specified in the DEFAULT profile.

Setting Profile Password Limits: Example The following statement creates the app_user2 profile with password limits values set:

CREATE PROFILE app_user2 LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;

This example uses the default Oracle Database password verification function, verify_function. Refer to Oracle Database Security Guide for information on using this verification function provided or designing your own verification function.

 

create profile new_profile
limit password_reuse_max 10
password_reuse_time 30;

2．设置profile资源限制：
create profile app_user limit
sessions_per_user unlimited
cpu_per_session unlimited
cpu_per_call 3000
connect_time 45
logical_reads_per_session default
logical_reads_per_call 1000
private_sga 15k
composite_limit 5000000;
总的resource cost不超过五百万service units。计算总的resource cost的公式由alter resource cost语句来指定。

3．设置密码限制profile：
create profile app_users2 limit
failed_login_attempts 5
password_life_time 60
password_reuse_time 60
password_reuse_max 5
password_verify_function verify_function
password_lock_time 1/24
password_grace_time 10;

4．将配置文件分配给用户：
SQL> alter user dinya profile app_user;
用户已更改。
SQL>

SQL> alter user dinya profile default;
用户已更改。
SQL>
 

大概就是这些东西吧，供你参考。
CREATE PROFILE prof LIMIT
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 30;
ALTER USER ashwini PROFILE prof;
ALTER USER ashwini ACCOUNT UNLOCK;