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>
