Managing Roles   «Prev  Next»

Lesson 5The WITH ADMIN OPTION clause
ObjectiveAllow other users to manage system privileges.


Usually the DBA is the only person who can grant system privileges. However, if you trust a user enough, you can allow them to administer one or more system privileges on their own. To grant a privilege to a user, and allow that user to further grant the privilege to other users, add the WITH ADMIN OPTION clause to the end of your GRANT command.
For example:

Jeff can now create users on his own, and allow them to connect to the database. Jeff may also pass the admin option on to those users. Roll your cursor over the following MouseOver to see pop-up explanations of this process.

Create user, create sys with Admin
Create user, create sys with Admin
  1. The DBA grants CREATE SESSION and CREATE USER to Jeff, and includes the WITH ADMIN OPTION. Jeff may now grant those privileges to other users.
  2. Jeff grants the two privileges to Jenny, and includes the admin option. Jenny may now grant those privileges to other users
  3. Jeff grants the two privileges to Ashley, but without the admin option. Ashley gets the privileges, but cannot pass them on.

When someone takes advantage of the admin option to grant a privilege, that privilege stays with the user even if it is revoked from the user who originally did the granting. With respect to the preceding diagram, revoking CREATE SESSION from JEFF would not affect JENNY and ASHLEY.

Format for the grant Command

Here is the format for the grant command for system privileges:
grant {system privilege | role}
[, {system privilege | role},. . .]
to {user | role} [, {user | role}]. . .
[with admin option]

By using the grant command, you can grant any system privilege or role to another user, to another role, or to public. The with admin option clause permits the grantee to bestow the privilege or role on other users or roles. The grantor can revoke a role from a user as well.