When you Create a Role it is Granted to you Automatically

I noticed recently that, when you create a role, it is automatically granted to you with the admin option. You can see what I mean in the example below, which I tested in Oracle 11.2:

 

SQL> conn andrew/reid

Connected.

SQL> create role blah

  2  /

 

Role created.

 

SQL> select grantee, admin_option

  2  from dba_role_privs

  3  where granted_role = 'BLAH'

  4  /

 

GRANTEE                        ADMIN_OPTION

------------------------------ ------------

ANDREW                         YES

 

SQL>

With Admin Option

This post was tested on Oracle 9. You can grant system privileges with or without the admin option. The default is to grant the system privilege without the admin option. As a first step, grant select any dictionary to public to allow the test users to look at dba_sys_privs:
 

SQL> col grantee format a10
SQL> col privilege format a15
SQL> conn / as sysdba
Connected.
SQL> grant select any dictionary to public
  2  /

Grant succeeded.

SQL>

Now create the first test user and allow it to connect to the database:

SQL> create user a identified by a
  2  /

User created.

SQL> grant create session to a
  2  /

Grant succeeded.

SQL>

You can see if a privilege has been granted with the admin option by looking at the admin_option column in dba_sys_privs: 

SQL> desc dba_sys_privs
Name                    Null?    Type
----------------------- -------- ----------------
GRANTEE                 NOT NULL VARCHAR2(30)
PRIVILEGE               NOT NULL VARCHAR2(40)
ADMIN_OPTION                     VARCHAR2(3)

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  NO

SQL> 

If a user has been granted a system privilege without the admin option, he cannot grant that privilege to other users:

SQL> create user b identified by b
  2  /

User created.

SQL> conn a/a
Connected.
SQL> grant create session to b
  2  /
grant create session to b
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

To get round this, you need to grant the system privilege to user A with the admin option:

SQL> conn / as sysdba
Connected.
SQL> grant create session to a with admin option
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  YES

SQL> conn a/a
Connected.
SQL> grant create session to b
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  YES
B          CREATE SESSION  NO

SQL>

User A can even grant create session to user B with the admin option:

SQL> grant create session to b with admin option
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  YES
B          CREATE SESSION  YES

SQL> 

Which might not be a great idea as user B can then revoke create session from user A:

SQL> conn b/b
Connected.
SQL> revoke create session from a
  2  /

Revoke succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
B          CREATE SESSION  YES

SQL> 

If you revoke a system privilege, the users it has passed that system privilege to will retain it:

SQL> conn / as sysdba
Connected.
SQL> create user c identified by c
  2  /

User created.

SQL> conn b/b
Connected.
SQL> grant create session to c with admin option
  2  /

Grant succeeded.

SQL> conn / as sysdba
Connected.
SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
B          CREATE SESSION  YES
C          CREATE SESSION  YES

SQL> revoke create session from b
  2  /

Revoke succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
C          CREATE SESSION  YES

SQL> 

Finally, to revoke the with admin option, you have to revoke the system privilege and grant it again without the admin option:

SQL> conn / as sysdba
Connected.
SQL> revoke create session from c
  2  /

Revoke succeeded.

SQL> grant create session to c
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
C          CREATE SESSION  NO

SQL>