Granting privileges to a role

You can grant table-level and routine-level access privileges to a role if you have the authority to grant these same privileges to login names or to PUBLIC. You can also grant type-level privileges to a role. A role cannot hold database-level privileges.

Important: The scope of a user-defined role (and of discretionary access privileges that the GRANT statement assigns to the role) is the current database. When the GRANT DEFAULT ROLE or SET ROLE statement activates a role, the role and its privileges take effect in the current database only. As a security precaution, discretionary access privileges that a user receives only from a role cannot provide access to tables outside the current database through a view or through the action of a trigger.
The syntax is more restricted for granting privileges to a role than to a user:
  • You can specify the AS grantor clause.

    In this way, whoever has the role can revoke these same privileges. For more information, see AS grantor clause.

  • You cannot include the WITH GRANT OPTION clause.

    A role cannot, in turn, grant the same access privileges to another user.

This example grants Insert privilege on the supplier table to the role payables: 
GRANT INSERT ON supplier TO payables;

Anyone who has been granted the payables role, and who successfully activates it by issuing the SET ROLE statement, can now insert rows into supplier.