Granting a Role to Users

When a role has been defined and has been granted privileges, you can grant that role to one or more users, who then acquire the privileges that have been assigned to the role.

To permit users to grant the role to others, add WITH ADMIN OPTION to the GRANT statement when you grant the role to the users.

The syntax is as follows:

GRANT {rolename [, rolename …]} TO {PUBLIC
 | {[USER] username [, [USER] username …]} } [WITH ADMIN OPTION];

The following example creates the DOITALL role, grants ALL privileges on DEPARTMENTS to this role, and grants the DOITALL role to RENEE, who then has SELECT, DELETE, INSERT, UPDATE, and REFERENCES privileges on DEPARTMENTS.

CREATE ROLE DOITALL;
GRANT ALL ON DEPARTMENTS TO DOITALL;
GRANT DOITALL TO RENEE;