Adding a User-Defined Role to a User or Role

Add membership in a user-defined role to a user or role (grantee), with or without administrative rights.

Prerequisites

Administrative privilege over the role being granted.

Task

A user-defined role can be granted with or without administrative rights. When granted with administrative rights (that is using the WITH ADMIN Option), a user can manage (grant, revoke, and drop) the role, as well as use any of the underlying system and object-level privileges of the role. When granted with administrative rights only (using the WITH ADMIN ONLY option), a user can manage the role, but cannot use its underlying system and object-level privileges. When granted without any administrative rights, a user can use its underlying system and object-level privileges, but cannot manage the role.

When a user is granted membership in a role, the user inherits all underlying system privileges and roles of the role, including any object-level permissions on tables, views, and procedures.

When a role is granted to another role, all members of the role being granted (the child role) automatically become members of the receiving role (parent role), and inherit all underlying system privileges and roles of the parent role, including those on tables, views, and procedures. Existing members of the parent role do not become members of the child role or inherit any of its underlying system privileges and roles.

To grant a user-defined role to a grantee, execute one of these statements:

Grant Type	Statement
Membership in the role along with full administrative rights to the role	GRANT ROLE `role_name` TO `grantee [,...]` WITH ADMIN OPTION
Administrative rights to the role only	GRANT ROLE `role_name` TO `grantee [,...]` WITH ADMIN ONLY OPTION
Membership in the role, but with no administrative rights to the role	GRANT ROLE `role_name` TO `grantee [,...]` WITH NO ADMIN OPTION

Grant Type

Statement

Membership in the role

along with full administrative rights

to the role

GRANT ROLE role_name TO grantee [,...]

WITH ADMIN OPTION

Administrative rights

to the role only

GRANT ROLE role_name TO grantee [,...]

WITH ADMIN ONLY OPTION

Membership in the role,

but with no administrative rights

to the role

GRANT ROLE role_name TO grantee [,...]

WITH NO ADMIN OPTION

Example:

There are three users: User1, User2, User3.
There are four roles: Role1, Role2, Role3, Role4.
There are two system privileges: Priv1, Priv2.
Role1 is granted Priv1 and Role3.
User2 and User3 are members of Role1.
Role2 is granted Priv2 and Role4.
User3 is a member of Role2.

You execute the following statement:

GRANT ROLE Role1 TO User1 WITH ADMIN OPTION

User1 becomes a member of Role1.

As a member of Role1, User1 inherits Priv1 and (indirectly) all system privileges and roles from Role3.

User1 can also administer Role1.

You execute the following statement:

GRANT ROLE Role2 TO Role1 WITH ADMIN OPTION

Role1 becomes a member of Role2.

As members of Role1, User2, User3, and User1 (from previous grant) inherit the following from Role2: Priv2 and (indirectly) all system privileges and roles of Role4.

As a member of Role2, User3 does not become a member of Role1 and does not inherit any system privileges or roles of Role1.

User1, User2, and User3 can administer Role2.