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

Requires administrative privilege over the role being granted.

Task

When granted with administrative rights, a user can manage (grant, revoke, and drop) the role, as well as use any of the underlying system privileges of the role. When granted with administrative rights only, a user can manage the role, but not use its underlying system privileges. Finally, when granted with no administrative rights, a user can only use its underlying system privileges. If no administrative clause is specified, the role is granted with no administrative rights.

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 the 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.