Home > Oracle DBA > The difference between granting direct privileges to a user and granting same privileges within a role

The difference between granting direct privileges to a user and granting same privileges within a role

November 17, 2013

The difference between granting direct privileges to a user and granting same privileges within a role

a) When granting a role to a user the user have to re-login to get use of that role.
b) When granting a privilege to a user the user can use it immediately. NO need to re-login.
c) Roles doesn’t give the granted user the right to create objects based on the role given to him.

For example: if there is a role includes a SELECT privilege on table X1 on schema X and this
role granted to user Y, User Y can select from table X1, but once user Y wants to create a
view on his schema selecting from table X1, he will get ORA-01031: insufficient privileges. in
this case you have to grant user Y a direct select privilege on table X1 to be able to create
a view based on that table

The following example will illustrate this point:

–By user SYS
–create a new role HR1, grant to it select privilege on hr.employees, then grant this role to user SH:
SQL> create role hr1;
SQL> grant select on hr.employees to hr1;
SQL> grant hr1 to SH;

–By user SH
–now try to test the role granted to SH:
SQL> select * from hr.employees;
multiple rows returned…

–but when user SH tries to create a view based on the SELECT right he inherit it from the role:
SQL> create view empl as select * from hr.employees;
ERROR at line 1:
ORA-01031: insufficient privileges

–By user SYS
–User SH must have a direct privilege grant (not within a role) to be able to create objects based on it:
SQL> grant select on hr.employees to SH;

–By SH
–now user SH can create objects based on select privilege he has on hr.employees table:
SQL> create view empl as select * from hr.employees;
View created.

I hope that was informative.

Advertisements
Categories: Oracle DBA
%d bloggers like this: