Profiles and resource management

Benefits of Profile

You can enforce a limit on resource utilization using resource limit parameters. Also you can maintain database security by using password management feature.
Resource Parameters


• SESSIONS_PER_USER
Specify the number of concurrent sessions to which you want to limit the user.

• CPU_PER_SESSION
Specify the CPU time limit for a session, expressed in hundredth of seconds.


• CPU_PER_CALL
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.


• 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.


• LOGICAL_READS_PER_SESSION
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.


• LOGICAL_READS_PER_CALL
Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).


• PRIVATE_SGA
Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.


• COMPOSITE_LIMIT
Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.


Creating Profile

Profiles only take effect when resource limits are "turned on" for the database as a whole.



• Specify the RESOURCE_LIMIT initialization parameter.


RESOURCE_LIMIT = TRUE


Let check the parameter value.

SQL> show parameter resource_limit NAME TYPE VALUE ------------------------------------ ----------- --------- resource_limit boolean FALSE
Its mean resource limit is off,we ist have to enable it.


• Use the ALTER SYSTEM statement to turn on resource limits.

SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; 

System altered. 

SQL> show parameter resource_limit 

NAME TYPE VALUE

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

 resource_limit boolean TRUE
• Resource limit specifications pertaining to passwords are always in effect.
Now I'm going to create a profile with the name my_profile. 

SQL> CREATE PROFILE my_profile LIMIT SESSIONS_PER_USER 2 IDLE_TIME 5 CONNECT_TIME 10;
Profile created. In the above example i created simple profile which will handle SESSIONS_PER_USER <<


NOTE: Both parameters take values in min.


Now I'm creating a test user to check the functionality of this profile.

SQL> create user Michel identified by michel default tablespace users temporary tablespace temp; 

User created. 

SQL> alter user Michel profile my_profile; 

User altered.
With the above statement i assigned the profile my_profile to user Michel.

Let see how our profile will work.


I already opened 2 sessions with the user name Michel but when i tried for third session it thorwed this error.

 sqlplus Michel SQL*Plus: Release 11.1.0.6.0 - Production on Mon Nov 26 15:57:23 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. Enter password: ERROR: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
You noticed when i tried to open third session it gave me error.


Lets go to 2nd step IDLE_TIME.Here we go again 

SQL> select * from tab; 

select * from tab * ERROR at line 1: ORA-02396: exceeded maximum idle time, please connect again Because i was idle more than 5 min so thats why Oracle server kill mine session.


We can check the resource parameter of our profile by querying DBA_PROFILES.

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

PROFILE RESOURCE_NAME RESOURCE LIMIT 

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

MY_PROFILE COMPOSITE_LIMIT KERNEL DEFAUL 

MY_PROFILE SESSIONS_PER_USER KERNEL 2

MY_PROFILE IDLE_TIME KERNEL 5 

MY_PROFILE CONNECT_TIME KERNEL 10 

. . .
Assigning Profile

Profile can be assign in two ways either during USER creation or by using ALTER statement.

Case 1:

SQL> create user orafaq identified by pass profile my_profile; 

User created.

We can check it by using this query.

SQL> select username,profile from dba_users where username='ORAFAQ';
USERNAME PROFILE

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

 ORAFAQ MY_PROFILE

CASE 2: 

SQL> drop user orafaq cascade; 

User dropped. 

SQL> create user orafaq identified by pass;

User created.

SQL> 

alter user orafaq profile my_profile; 

User altered. 

Altering Profile


Profiles can be altered with the ALTER PROFILE command. • A DBA must have the ALTER PROFILE system privilege to use this command. • When a profile limit is adjusted, the new setting overrides the previous setting for the limit, but these changes do not affect current sessions in process. See the example below

SQL> ALTER PROFILE accountant LIMIT CPU_PER_CALL default LOGICAL_READS_PER_SESSION 20000 SESSIONS_PER_USER 1;
Dropping Profile


Profiles no longer required can be dropped with the DROP PROFILE command.
The DEFAULT profile cannot be dropped.
The CASCADE clause revokes the profile from any user account to which it was assigned – the CASCADE clause MUST BE USED if the profile has been assigned to any user account.
When a profile is dropped, any user account with that profile is reassigned the DEFAULT profile. See example below:

SQL> DROP PROFILE accountant;

ERROR at line 1: ORA-02382:

 profile ACCOUNTANT has users assigned, cannot drop without CASCADE

 SQL> DROP PROFILE accountant CASCADE;

Password Management
Password Parameters


FAILED_LOGIN_ATTEMPTS :

Maximum times the user is allowed in fail login before locking the user account * 10

PASSWORD_LIFE_TIME :

Number of days the password is valid before expiry * 108 days

PASSWORD_REUSE_TIME :

Number of day after the user can use the already used password * UNLIMITED

PASSWORD_REUSE_MAX :

Number of times the user can use the already used password * UNLIMITED

PASSWORD_LOCK_TIME :

Number of days the user account remains locked after failed login * 1 day

PASSWORD_GRACE_TIME :Number of grace days for user to change password * 7 days


PASSWORD_VERIFY_FUNCTION :

PL/SQL that can be used for password verification * NO DEFAULT SETTING


SEC_CASE_SENSITIVE_LOGON :

To control the case sensitivity in passwords * TRUE
Enabling Password Management
Restrictions on password parameters
How profiles are stored


Profiles can be viewed via the DBA_PROFILE views (available only with DBA role): 

SQL> describe DBA_PROFILES 

 Name Null? Type

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

PROFILE NOT NULL VARCHAR2(30) 

RESOURCE_NAME NOT NULL VARCHAR2(32)

 RESOURCE_TYPE VARCHAR2(8)

 LIMIT VARCHAR2(40)

There are only two RESOURCE TYPEs, "KERNEL" and "PASSWORD"; 

and 16 RESOURCE NAMEs which can be given a limit; the limit in the DEFAULT profile is written in the 3rd column (as of 11gR2): 

RESOURCE_NAME RESOURCE_TYPE LIMIT

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

COMPOSITE_LIMIT KERNEL UNLIMITED

CONNECT_TIME KERNEL UNLIMITED

CPU_PER_CALL KERNEL UNLIMITED 

CPU_PER_SESSION KERNEL UNLIMITED 

IDLE_TIME KERNEL UNLIMITED 

LOGICAL_READS_PER_CALL KERNEL UNLIMITED

LOGICAL_READS_PER_SESSION KERNEL UNLIMITED 

PRIVATE_SGA KERNEL UNLIMITED

SESSIONS_PER_USER KERNEL UNLIMITED

FAILED_LOGIN_ATTEMPTS PASSWORD 10 

PASSWORD_GRACE_TIME PASSWORD 7 

PASSWORD_LIFE_TIME PASSWORD UNLIMITED

PASSWORD_LOCK_TIME PASSWORD 1 

PASSWORD_REUSE_MAX PASSWORD UNLIMITED 

PASSWORD_REUSE_TIME PASSWORD UNLIMITED 

PASSWORD_VERIFY_FUNCTION PASSWORD NULL

Derived from
http://www.orafaq.com/wiki/Profiles_and_password_management

Thank you DreamzZ for the contribution.
Regards, Shaamil.