Granting a Role to a User or to Another Role

You must register a role in the database before the role can be used in a GRANT statement. For more information, see CREATE ROLE statement.

A DBA has the authority to grant a new role to another user. If a user receives a role WITH GRANT OPTION, that user can grant the role to other users or to another role. Users keep a role that was granted to them until the REVOKE statement breaks the association between their login names and the role name.
Important: The CREATE ROLE and GRANT statements do not activate the role. A non-default role has no effect until SET ROLE enables it. The grantor or the grantee of a role can issue the SET ROLE statement.
The following example shows the actions required to grant and activate the role payables to a group of employees who perform account payable functions. First the DBA creates role payables, then grants it to maryf. 
CREATE ROLE payables;
GRANT payables TO maryf WITH GRANT OPTION;
The DBA or maryf can activate the role with the following statement: 
SET ROLE payables;
User maryf has the WITH GRANT OPTION authorization to grant payables to other employees who pay accounts. 
GRANT payables TO charly, gene, marvin, raoul;
If you grant privileges for one role to another role, the recipient role has the combined set of privileges that have been granted to both roles. The following example grants the role petty_cash to the role payables: 
CREATE ROLE petty_cash;
SET ROLE petty_cash;
GRANT petty_cash TO payables;
After all of these statements execute successfully, if user raoul uses the SET ROLE statement to make payables his current role, then (aside from the effects of any REVOKE operations) he holds the following combined set of access privileges:
  • The privileges granted to the payables role
  • The privileges granted to the petty_cash role
  • The privileges granted individually to raoul
  • The privileges granted to PUBLIC

If you attempt to grant a role to yourself, either directly or indirectly, the database server generates an error. (For an important exception to this rule, however, see the description of the DBSECADM Clause.)

The database server also generates an error if you include the WITH GRANT OPTION keywords in a GRANT statement that assigns a role to another role.