I am little confused between following two CASES, though I know the result but I am not very convinced with the reason behind it. Need help from database experts.
(Say) A role ROLE1 is created along with USER1 and USER2 by dba
connect /as sysdba
create user USER1 identified by xyz;
create user USER2 identified by abc;
create role ROLE1;
CASE 1:
A ëselect any tableí privilege is granted to ROLE1 without admin option
connect /as sysdba
grant select any table to ROLE1;
Grant ROLE1 to USER1 with admin option
connect /as sysdba
grant ROLE1 to USER1 with admin option;
Can USER1 now grant ‘select any privilege’ to other users?
connect USER1/xyz
grant select any table to USER2;
I got ORA-01031: insufficient privileges error here, which means it is not the right way.
CASE 2:
A ëselect any tableí privilege is granted to ROLE1 with admin option
connect /as sysdba
grant select any table to ROLE1 with admin option;
Grant ROLE1 to USER1 irrespective of whether you grant it w/ or w/o admin option
connect /as sysdba
grant ROLE1 to USER1;
Can USER1 now grant ‘select any privilege’ to other users?
connect USER1/xyz
grant select any table to USER2;
This works perfectly without reporting any error.
Can any one please help understand why CASE2 works and why not CASE1?
CASE1:
grant ROLE1 to USER1 with admin option; means you can grant ROLE1 to other users, regardless what have been granted to ROLE1. Thus grant select any table to USER2; does not work.
However, grant ROLE1 to USER2; should work.
CASE2:
You get permission (through role ROLE1, like DBA role gets it) to grant select any table to other users. So, grant select any table to USER2; works.
On the other hand grant ROLE1 to USER2; should fail.
