user1612078 user1612078 - 2 months ago 17
SQL Question

Grant privilege to role with admin option Vs Grant role to user with admin option?

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:


  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 any one please help understand why CASE2 works and why not CASE1?

Answer

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.