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

Concept of ROWNUM, Why ROWNUM=2 gives "no rows selected

ROWNUM is a psuedocolumn , its value is not predefined in a table. A number is assigned in the form of 1,2,3,...N to every row returned by a query when its executed, that number is called as ROWNUM.  Its generated dynamically.

Example :- Consider a table having 5 rows. PFB output of below select queries :-

1. SQL> select * from amit;

        ID NAME
---------- ------------------------------------
         1
         2
         3
         4
         5

2. SQL> select * from amit where rownum=1;

        ID NAME
---------- ------------------------------------
         1

3. SQL> select * from amit where rownum<3;

        ID NAME
---------- ------------------------------------
         1
         2

4. SQL> select * from amit where rownum=2;

no rows selected

5. SQL> select * from amit where rownum>2;

no rows selected

We see that when we use Rownum in Where clause then it gives output only for rownum<3 and Rownum=1 . But when we we use rownum=2 and rownum>2 it gives output as "no rown selected". 

Reason for this behaviour of rownum is that its value is not predefined. when a select query fetch some output then a number is assingned in the form of 1,2,3...N to all rows fetched by query.First selected row is always assigned as rownum=1.

Rownum value is incremented after a query passes the where clause.

So when we use rownum=1 or rownum<3 in where clause then where condition is matched as rownum=1 at that time value of rownum is 1 so it passes the where condition and gives us the output. IF rownum < 3 has been used in where clause then it gives us output for rownum=1 as it passes where clasuse, now rownum value incremented and becomes 2 again it passes where clause (rownum<3 ). Now  rownum value incremented and becomes 3, when rownum value becomes 3 then it fails the where clause(rownum<3) and gives us only two rows as output.

But when We have rownum=2 or rownum>2 then numbers is assigned to the output as 1,2,3..N , when first row's rownum which is 1 is matched with rownum=2 or rownum>2 then at this time rownum has value as 1 and it fails the where condition(rownum=2 or rownum>2) criteria on first attempt and gives us output as "no rows selected" 

We have to remember this statement that "Rownum is not preassigned in table"

I hope this article helped you.

Regards,
Amit Rath