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

How to setup password never expire for oracle user in Oracle

Sometimes for a test user in oracle , we often get this error :-

SQL> conn scott/tiger
ERROR:
ORA-28002: the password  will expire in 7 days
Connected

We want that as its a test user, its password never expire. We have to make a change in the profile associated with this user.

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

PROFILE
------------------------------------------------------------------------------------------
PROFILE

SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                   RESOURCE_NAME                  RESOURCE_TYPE            LIMIT

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

PROFILE                   PASSWORD_LIFE_TIME             PASSWORD                 10

we need to change the limit of password_life_time parameter to unlimited.

SQL> alter profile PROFILE limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                   RESOURCE_NAME                  RESOURCE_TYPE            LIMIT

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

PROFILE                   PASSWORD_LIFE_TIME             PASSWORD                 UNLIMITED

Check that the expirary date column is null of desired user in DBA_USERS view

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from  dba_users where USERNAME='SCOTT';

USERNAME                       ACCOUNT_STATUS            EXPIRY_DATE        PROFILE

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

SCOTT                            OPEN                                                         PROFILE

Similarly if we want failed login attempts has to be unlimited for a test user then alter the profile associated with the test user :-

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

Profile altered

I hope this article helped you.

Regards,

Amit Rath