Security Roles   «Prev  Next»

Objective Allow users to administer role.


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.


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.