Profiles In Oracle

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 MQM_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 MQM_profile LIMIT SESSIONS_PER_USER  10;

How to assign a Profile to a user :-

a. Assigning a profile along with user creation :-
Create user MQM identified by MQM profile MQM_profile;

b. Assigning a profile after user creation :-
Alter user MQM profile MQM_profile;