Profiles in Oracle

Profiles are used for restricting access to Oracle Database. Default Profile of Oracle Database is "DEFAULT". We can also create our own profile with our own user defined restrictions.

In order to use Profiles limits on a Oracle User, Parameter resource_limit must be set to true.

Once we assign a profile to user then that user cannot exceeds limits defined in that profile.

There are two type of parameters in Profile :-

1. Resource Parameters :-  Parameters related to sessions, CPU, connect_time, idle_time, Logical_reads , private_sga comes under this                                                        category.

  a. Session_per_user :- Specify number of concurrent sessions for a user.
  b. Cpu_per_session :- Specify CPU time limit for a session, expressed in hundredth of second.
  c. Cpu_per_call       :- Specify CPU time limit for a call,  expressed in hundredth of second.
  d. Connect_time      :- Specify the time limit for a session, expressed in minutes.
  e. Logical_reads_per_session :- Specify number of data blocks reads in a session.
  f.  Logical_reads_per_cal       :- Specify number of data blocks read for a call to process a sql statement.
  g. Private_SGA       :-  Specify the amount of private space a session can have.

2. Password Parameters :- Parameters sets length of time are defined in number of days, however we can specify minutes(n/1440) or                                                         seconds (n/86400) also.

   a. Failed_login_attempts :- Specify number of failed attempts before a account is locked.
   b. Password_life_time    :-  Specify the life time of a password in number of days.
   c. Password_lock_time  :-  Specify number of days account will be locked after failed login attempts.
   d. Password_grace_time :- Specify the grace period given to a user after it exceeds failed login attempts, if in grace time user has not        changed its password, it will be lock.
   e. Password_verify_function :- its a PL/SQL function which checks that complexity of a password.
   f. Password_reuse_time and Password_reuse_max :- both are used in conjunction, three values can be possible for both of them :-
       1. Both can be set as integer. For eg Password_reuse_time is 50 and Password_reuse_max is 5. In this case user can reuse old            password after 50 days and after changing 5 times.
      2. One is set to integer and another to unlimited, In this case user cannot reuse a password.
      3. Both set to unlimited then database ignores both of them.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE

SQL> alter system set resource_limit=TRUE;

System altered.

Profile creation :-

CREATE PROFILE AMIT_PROFILE LIMIT
  SESSIONS_PER_USER unlimited
  CPU_PER_SESSION DEFAULT
  CPU_PER_CALL DEFAULT
  CONNECT_TIME unlimited
  IDLE_TIME unlimited
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL DEFAULT
  COMPOSITE_LIMIT DEFAULT
  PRIVATE_SGA DEFAULT
  FAILED_LOGIN_ATTEMPTS unlimited
  PASSWORD_LIFE_TIME unlimited
  PASSWORD_REUSE_TIME unlimited
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_LOCK_TIME 1
  PASSWORD_GRACE_TIME 7
  PASSWORD_VERIFY_FUNCTION NULL;

A resource in a profile can have three different values :-

1. UNLIMITED :- when a resource has value as UNLIMITED , then user can use unlimited amount of this resource.

2. DEFAULT :- If value of a resource is DEFAULT, then that resource is assigned value as it has in DEFAULT profile

3. Number (1,2,3) :- If a value is assigned to a resource, then that resource cannot exceeds that value.

How to Alter a Profile :-

Alter Profile DBA LIMIT <profile_item_name> <value> ;
eg:- Alter Profile Amit_profile LIMIT SESSIONS_PER_USER  10;

How to assign a Profile to a user :-

a. Assigning a profile along with user creation :-
   Create user Amit identified by amit profile Amit_profile;

b. Assigning a profile after user creation :-
   Alter user Amit profile Amit_profile;

I hope this article helped you.

Regards,
Amit Rath