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