ORACLE profile series 4 and oracleprofile Series
This blog is the fourth article in the ORACLE profile series. It mainly describes how to create a profile and use it for resource and password control.
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 management and tracking resource use. for more information on the Database Resource Manager, refer to Oracle Database Administrator's Guide.
Purpose
UseCREATE
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 haveCREATE
PROFILE
System privilege.
To specify resource limits for a user, you must:
Enable resource limits dynamically withALTER
SYSTEM
Statement or with the initialization parameterRESOURCE_LIMIT
. This parameter does not apply to password resources. Password resources are always enabled.
Create a profile that defines the limits usingCREATE
PROFILE
Statement
Assign the profile to the user usingCREATE
USER
OrALTER
USER
Statement
# Prerequisites for creating and making the profile take effect:
To create a profile, you must have the create profile permission.
If you want to apply the restrictions specified in the profile to relevant users, you must first specify the profile to the user, and then enable the resource_limit function of the database. (You can specify the RESOURCE_LIMIT initialization parameter in the parameter file before starting the database, or directly use alter system set resource_limit = true; To enable RESOURCE_LIMIT)
See Also:
Alter system for information on enabling resource limits dynamically
Oracle Database Reference for information onRESOURCE_LIMIT
Parameter
Create user andALTER USER for information on profiles
Syntax
Create_profile: =
Description of the authentication create_profile.gif
Resource_parameters: =
Description of the procedure resource_parameters.gif
(Size_clause: =
Password_parameters: =
Description of the authentication password_parameters.gif
Examples
Creating a Profile: Example The following statement creates the profilenew_profile
:
CREATE PROFILE new_profile LIMIT PASSWORD_REUSE_MAX 10 PASSWORD_REUSE_TIME 30;
Setting Profile Resource Limits: Example The following statement creates the profileapp_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 assignapp_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 inDEFAULT
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 byALTER
RESOURCE
COST
Statement.
Sinceapp_user
Profile omits a limitIDLE_TIME
And for password limits, the user is subject to the limits on these resources specified inDEFAULT
Profile.
Setting Profile Password Limits: Example The following statement createsapp_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.
What is the role of profile in Oracle?
Create profile new_profile
Limit password_reuse_max 10
Password_reuse_time 30;
2. Set profile resource restrictions:
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 15 k
Composite_limit 5000000;
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.
3. Set Password restriction 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
Pass word_lock_time 1/24
Password_grace_time 10;
4. Allocate the configuration file to the user:
SQL> alter user dinya profile app_user;
The user has changed.
SQL>
SQL> alter user dinya profile default;
The user has changed.
SQL>
How to view the profile in oracle and what permissions does default have?
These are some of them for your reference.
Create profile prof LIMIT
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 30;
Alter user ashwini PROFILE prof;
Alter user ashwini account unlock;