| Lesson 5 | The WITH ADMIN OPTION clause |
| Objective | Allow other users to manage system privileges. |
WITH ADMIN OPTION clause
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
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.
WITH ADMIN OPTION clause to the end of your GRANT command. For example:
GRANT CREATE USER, CREATE SESSION TO jeff WITH ADMIN OPTION;
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.
- 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.
- Jeff grants the two privileges to Jenny, and includes the admin option. Jenny may now grant those privileges to other users
- 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:
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.
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.
