Restrictions on Oracle profile user resources

I. Description

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

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.

The profile in the Oracle system can be used to restrict the database resources that users can use. The Create Profile command is used to Create a Profile to restrict the use of database resources, if the profile is assigned to the user, all the database resources that the user can use are limited by the profile.

Prerequisites

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

To specify resource limits for a user, you must:

(1). 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.

(2). Create a profile that defines the limits using the create profile statement

(3). Assign the profile to the user using the create user or alter user statement

For descriptions of other parameters, refer to the official website information in the above link.

Ii. PROFILE management

Proflie is a set of names for password restrictions and resource restrictions. When an oracle database is created, oracle automatically creates a PROFILE named DEFAULT, and the initialized DEFAULT has no password or resource restrictions.

Use PROFILE has the following main issues:

1. If you set only some passwords or resource limit options when creating a PROFILE, other options will automatically use the DEFAULT value (corresponding options of DEFAULT)

2. If you do not specify the PROFILE option when creating a user, oracle will automatically allocate the DEFAULT option to the corresponding database user.

3. One user can only allocate one PROFILE. To manage the user's passwords and resources at the same time, you must specify both the password and resource options when creating the PROFILE.

4. When you use PROFILE to manage passwords, the password management option is always in the active state. However, if you use PROFILE to manage resources, you must activate resource restrictions.

 

2.1 conditions:

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

 

Specify resource limits for users. required:

1. dynamically use alter system or use the initialization parameter resource_limit to make the resource limit take effect. This change is invalid for password resources and password resources are always available.

SQL> show parameter resource_limit

NAME TYPE VALUE

-----------------------------------------------------------------------------

Resource_limit boolean FALSE

SQL> alter system set resource_limit = true;

System altered.

SQL> show parameter resource_limit

NAME TYPE VALUE

-----------------------------------------------------------------------------

Resource_limit boolean TRUE

SQL>

 

2. Use create profile to create a profile that defines the limits on database resources.

SQL> create profile test

2 limit

3 idle_time 30-unit: minute

4 connect_time 480;

Profile created.

 

CONNECT_TIME Specify the total elapsed time limit for a session, expressed in minutes.

IDLE_TIME Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

 

3. Use the create user or alter user command to allocate the profile to the user.

SQL> create user qs identified by qs profile test; -- specify the profile when creating a user

User created.

SQL> alter user qs profile test; -- modify the user's profile

User altered.

SQL>

SQL> alter user dinya profile default; -- restore the default value

 

4. Modify the PROFILE

SQL> alter profile test limit idle_time 60;

Profile altered.

 

5. Delete PROFILE

SQL> drop profile test;

SQL> drop profile test cascade;

 

Note:

1. the cascade option must be added when you delete an allocated profile.

2. You must have the create profile permission to create a profile.

3. DEFAULT is the DEFAULT profile and cannot be deleted.

 

6. Information Acquisition

You can query the profile information from the dba_profiles table and dba_users table, for example:

 

SQL> set wrap off;

SQL> select profile from dba_users where username = 'qs ';

PROFILE

------------------------------

TEST

SQL> select * from dba_profiles where profile = 'test ';

 

PROFILE RESOURCE_NAME RESOURCE LIMIT

-----------------------------------------------------------------------------

TEST COMPOSITE_LIMIT KERNEL DEFAULT

TEST SESSIONS_PER_USER KERNEL DEFAULT

TEST CPU_PER_SESSION KERNEL DEFAULT

TEST CPU_PER_CALL KERNEL DEFAULT

TEST LOGICAL_READS_PER_SESSION KERNEL DEFAULT

TEST LOGICAL_READS_PER_CALL KERNEL DEFAULT

TEST IDLE_TIME KERNEL 60

TEST CONNECT_TIME KERNEL 480

TEST PRIVATE_SGA KERNEL DEFAULT

TEST FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT

TEST PASSWORD_LIFE_TIME PASSWORD DEFAULT

 

PROFILE RESOURCE_NAME RESOURCE LIMIT

-----------------------------------------------------------------------------

TEST PASSWORD_REUSE_TIME PASSWORD DEFAULT

TEST PASSWORD_REUSE_MAX PASSWORD DEFAULT

TEST PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT

TEST PASSWORD_LOCK_TIME PASSWORD DEFAULT

TEST PASSWORD_GRACE_TIME PASSWORD DEFAULT

 

16 rows selected.

1 2 Next Page