Security Roles   «Prev  Next»

Lesson7 The WITH ADMIN OPTION
Objective Allow users to administer role.

WITH ADMIN OPTION

In Oracle, to enable a user to administer a role that they have been granted, you must grant the role to the user with the ADMIN OPTION. This option gives the user the ability to grant and revoke the role to/from other users.
Here's the syntax for this action:
GRANT role_name TO username WITH ADMIN OPTION;

Replace role_name with the name of the role that you want the user to administer and username with the name of the user. Once this statement is executed, the user will have the ability to grant and revoke the specified role to/from other users.
For instance, to grant a role named manager to a user named scott with admin option, you would use:
GRANT manager TO scott WITH ADMIN OPTION;

This would allow scott to subsequently grant or revoke the manager role to/from other users. scott can do this with the following syntax:
-- To grant the role to another user
GRANT manager TO other_username;

-- To revoke the role from another user
REVOKE manager FROM other_username;

Please remember that the ability to administer roles should be given sparingly and judiciously, as it confers significant powers on the user to control access to database resources.
As always, all of these steps require that you have the necessary administrative privileges in Oracle. If you're not an administrator, you'll need to request these privileges from your database administrator.
Just as you can give a user the right to re-grant privileges that they have been granted, you can give a user the ability to administer a role that they have been granted.

WITH ADMIN OPTION

When you grant a role to a user, you use the same syntax as when you grant a privilege to the user. With a privilege, you can include the keywords WITH GRANT OPTION, allowing the user to re-grant the privilege to others.
You can use the WITH GRANT OPTION with a role, authorizing the same function as with a privilege grant. You can also use the WITH ADMIN OPTION for a role. This gives the user the ability to not only re-grant the role, but to perform administrative functions for the role, such as altering the role or dropping the role.
The WITH ADMIN OPTION is an extremely powerful facility. Because the normal use of roles is to simplify administration of an entire security scheme, giving someone the ability to change the rights granted by a role should be done only with great care.
The next lesson shows how roles do and do not affect the privileges granted to stored code.

How to Grant System Privileges

As with object privileges, you use the GRANT statement to confer system privileges on either a user or a role. Unlike object privileges, the optional keywords WITH ADMIN OPTION are required to additionally allow the grantee to confer these privileges on other users and roles. For example, to give the CREATE USER, ALTER USER, and DROP USER privileges to the role APPL_DBA, you execute the following statement:

GRANT create user, alter user, drop user 
TO appl_dba;

System and role privileges require the wording WITH ADMIN OPTION. Object privileges require the wording WITH GRANT OPTION. Because the function is so similar but the syntax is different, be sure you know
  1. when to use ADMIN and
  2. when to use GRANT.