Profile in Oracle

Profiles are a means to limit resources a user can use.We can set up limits on the system resources used by setting up profiles with defined limits on resources.  Profiles are very useful in large, complex organizations with many users.  It allows us to regulate the amount of  resources used by each database user by creating and assigning profiles to users.

The limits resource can be categories into two parts  :  
1.) Kernel Limits
2.) Password Limits

1.) Kernel Limits : Kernel Resources covers the following options to limit the kernels resources.

• Composite_limits : it  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. composite_limit  <value | unlimited | default>

       e.g;    SQL> alter profile P1 LIMIT composite_limit 5000000;

• CONNECT_TIME : it specify the total elapsed time limit for a session, expressed in minutes. connect_time <value |  unlimited | default> . e.g ,

                  SQL> alter profile P1  LIMIT connect_time 600;

• CPU_PER_CALL : Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds. cpu_per_call  < value |  unlimited | default > .  e.g;

                  SQL>  alter profile P1  LIMIT cpu_per_call 3000;

• CPU_PER_SESSION : Specify the CPU time limit for a session,expressed in hundredth of seconds. cpu_per_session   <value |  unlimited | default> .e.g ;

                  SQL>  alter profile  P1  LIMIT   cpu_per_session   UNLIMITED ; 

• 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 . idle_time  <value | unlimited | default> . e.g;

                SQL>  alter profile  P1  LIMIT   idle_time  20 ; 

• LOGICAL_READS_PER_CALL : Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch). logical_reads_per_call  <value |  unlimited | default> .e.g;

                 SQL> ALTER PROFILE  P1  LIMIT logical_reads_per_call 1000;

• 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_session <value | unlimited | default>  e.g;

               SQL> ALTER PROFILE  P1   LIMIT logical_reads_per_session UNLIMITED;

• PRIVATE_SGA : Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA). private_sga  <value | unlimited | default>  Only valid with TP-monitor.  e.g;

               SQL> ALTER PROFILE  P1   LIMIT private_sga 15K ;

• SESSIONS_PER_USER : Specify the number of concurrent sessions to which you want to limit the user. sessions_per_user <value | unlimited | default>  e.g;

             SQL> ALTER PROFILE  P1   LIMIT sessions_per_user 1;

2 .)  Password Limits : Password Limits covers the following option to restrict the Password resources. Parameters that set lengths of time are interpreted in number of days .

• FAILED_LOGIN_ATTEMPTS : Specify the number of failed attempts to log in to the user account before the account is locked. failed_login_attempts <value | unlimited | default> e.g;

            SQL> ALTER PROFILE  P1  LIMIT failed_login_attempts 3;

--  to count failed log in attempts , fire the below command

             SQL> SELECT name, lcount FROM user$ WHERE lcount <> 0;

• PASSWORD_GRACE_TIME  :  The number of days during which a login is alowed but a  warning is issued. password_gracetime <value | unlimited | default> . e.g;

             SQL> ALTER PROFILE  P1  LIMIT password_grace_time 10;

• PASSWORD_LOCK_TIME  :  The number of days an account will be locked after the specified number of  consecutive failed login attempts defined by  FAILED_LOGIN_ATTEMPTS.    password_lock_time  <value | unlimited | default>  .  e.g;

               SQL> ALTER PROFILE  P1  LIMIT password_lock_time 30;

• PASSWORD_REUSE_MAX :Times a password can be reused .password_reuse_max  <value | unlimited | default> .  e.g;

          SQL> ALTER PROFILE  P1  LIMIT password_reuse_max 0 ; 

• PASSWORD_REUSE_TIME : Days between password reuses.password_reuse_time  <value | unlimited | default> .  e.g ; 

          SQL> ALTER PROFILE  P1  LIMIT password_reuse_time 0 ; 

To view existing profiles fire the below query :

SQL> select  profile, resource_name, limit FROM dba_profiles order by  profile, resource_name ; 

How to create the profile   :

SQL Create profile CREATE PROFILE <profile_name>  LIMIT

           <profile_item_name> <value>

           <profile_item_name> <value>

            ....;

e.g; 

SQL> create profile P1 limit 

 password_life_time 90

password_grace_time 15

password_reuse_time  0

password_reuse_max  0

failed_login_attempts  4

password__login_atempts  4

password_lock_time  2

cpu_per_call  2

private_sga 500K

logical_read_per_call   1000;

How to assign profile to user : we can assign the profile to user while creating the user or by altering the user. Here is demo to assign a profile to user .Say we have profile P1 and we assign to user ABC.

SQL> create user abc identified by abc 

default tablespace users  

 quota unlimited on users 

profile P1;

or  

SQL> alter user abc identified by abc  profile P1;

Enjoy     :-)