You Asked

Hi,

Could you Explain the exact Difference between the ROLE and Privilage with an example,also explain how they affect the user granted with roles and privilages?

Thank you.

and Chris said...

Privileges control the ability to run SQL statements. A role is a group of privileges. Granting a role to a user gives them the privileges contained in the role.

You can use roles to simplify access management. If you have a set of privileges you'll want to give to several users:

- Create the role
- Grant the privileges to it
- Grant the role to the users

This ensures they all have the same access. For example, the following grants U2 access to:

- U1.T1 directly
- U1.T2 & U1.T3 via a role

create role r1;

grant create session, create table, unlimited tablespace 
  to u1 identified by u1;
grant create session, create procedure, r1 
  to u2 identified by u2;

conn u1/u1

create table t1 (
  x int
);
create table t2 (
  x int
);
create table t3 (
  x int
);

insert into t1 values (1);
insert into t2 values (2);
insert into t3 values (3);
commit;

grant select on t1 to u2;
grant select on t2 to r1;
grant select on t3 to r1;

conn u2/u2

select * from u1.t1;

X  
1  

select * from u1.t2;

X  
2 

select * from u1.t3;

X  
3

Note: To access an object in PL/SQL, you need to grant the privilege directly to the them. U2 only has access to T2 via the role. So the create procedure fails:

conn u2/u2

create or replace procedure p is
begin
  for c in (select * from u1.t2) loop
    null;
  end loop;
end p;
/
sho err

Errors for PROCEDURE U2.P:

LINE/COL ERROR
-------- -----------------------------------------------
3/13     PL/SQL: SQL Statement ignored
3/30     PL/SQL: ORA-00942: table or view does not exist

To make it work, you need to connect back at U1 and grant U2 direct access to T2:

conn u1/u1

grant select on t2 to u2;

conn u2/u2

create or replace procedure p is
begin
  for c in (select * from u1.t2) loop
   null;
 end loop;
end p;
/
sho err

No errors.

For further reading, see:

http://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG004

Is this answer out of date? If it is, please let us know via a Comment