Hello Tom,
I am little confused between following two CASES, though I know the result but I am not very convinced with the reason behind it.
(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:1. A ‘select any table’ privilege is granted to ROLE1 without admin option
connect /as sysdba
grant select any table to ROLE1;
2. Grant ROLE1 to USER1 with admin option
connect /as sysdba
grant ROLE1 to USER1 with admin option;
3. 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:1. A ‘select any table’ privilege is granted to ROLE1 with admin option
connect /as sysdba
grant select any table to ROLE1 with admin option;
2. Grant ROLE1 to USER1 irrespective of whether you grant it w/ or w/o admin option
connect /as sysdba
grant ROLE1 to USER1;
3. 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 you please help understand why CASE2 works and why not CASE1?
Thanks.
So here's the output
SQL> connect sys/*** as sysdba
Connected.
SQL> create user USER1 identified by xyz;
User created.
SQL> create user USER2 identified by abc;
User created.
SQL> grant connect to user1;
Grant succeeded.
SQL> create role ROLE1;
Role created.
SQL> grant select any table to ROLE1;
Grant succeeded.
SQL> grant ROLE1 to USER1 with admin option;
Grant succeeded.
SQL> connect USER1/xyz
Connected.
SQL> grant select any table to USER2;
grant select any table to USER2
*
ERROR at line 1:
ORA-01031: insufficient privileges
================================
SQL> connect sys/*** as sysdba
Connected.
SQL> create user USER1 identified by xyz;
User created.
SQL> create user USER2 identified by abc;
User created.
SQL> grant connect to user1;
Grant succeeded.
SQL> grant connect to user2;
Grant succeeded.
SQL> create role ROLE1;
Role created.
SQL> grant select any table to ROLE1 with admin option;
Grant succeeded.
SQL> grant ROLE1 to USER1;
Grant succeeded.
SQL> connect USER1/xyz
Connected.
SQL> grant select any table to USER2;
Grant succeeded.
In the first example, you are allowing the *role* to be onwardly granted to other people. So you could do "grant role1 to user2" as user1.
In the second example, you are allowing the "select any privilege" to be onwardly granted. So lets say later you granted 'execute any procedure' to role1. Then user1 could not grant this privilege to user2, even though they (user1) have it via role1.
You might be thinking - why the difference ? Because think of examples where plsql is involved. Roles are not enabled during definer rights plsql execution, so having a role with a certain privilege and having that privilege directly are quite different things.
Hope this helps.