这篇博客是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

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:

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:

• for information on enabling resource limits dynamically

• for information on the RESOURCE_LIMIT parameter

• and for information on profiles

Syntax

create_profile::=

 

 

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 for information on using this verification function provided or designing your own verification function.