ORACLE Profile Series 4--create profile

This blog is the fourth of the Oracle Profile series, which focuses on creating profiles and using profiles for resource and password control

CREATE Profile

Note:

Oracle recommends 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 toOracle Database Administrator ' s Guide.

Purpose

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

See Also:

Oracle Database Security GuideFor a detailed description and explanation of what to use password management and protection

Prerequisites

To create a profiles, you must has 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 isn't apply to password resources. Password resources is 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生效的前提条件是:

To create a profile successfully, the user must have Create Profiles permission

If you want to make the restrictions specified in profile work for the relevant user, first we need to assign the profile to the user, and then we need to turn on the Resource_limit function of the database. (You can specify the Resource_limit initialization parameter in the parameter file before the database starts, or use the ALTER system set resource_limit=true directly; to enable)

See Also:

• ALTER SYSTEM for information on enabling resource limits dynamically

• Oracle Database Reference for information on the RESOURCE_LIMIT parameter

• CREATE User Andalter 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        30 password_reuse_time;

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    connect_time    logical_reads_per_session  DEFAULT    logical_reads_per_call     +    PRIVATE_SGA                15K   composite_limit            

If you assign app_user the profiles to a user, and then the user was subject to the following limits in subsequent sessions:

• The user can has 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 and more than the seconds of CPU time.

• A single session cannot last for more than minutes.

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

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

• A Single session cannot allocate more than 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 was specified by the ALTER RESOURCE COST statement.

• Since app_user The profile omits a limit IDLE_TIME for and for password limits, the user was subject to the limits on these Resour Ces specified in the profile DEFAULT .

Setting profile Password limits:example The following statement creates the profile with app_user2 password limits values set:

CREATE profile App_user2 LIMIT   failed_login_attempts 5   password_life_time   password_reuse_time   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 the verification function provided or Designi Ng your own verification function.

ORACLE Profile Series 4--create profile