Granting privileges to roles

You can use GRANT statements to assign privileges to a role, just as you assign privileges to users. See Database privileges for information about which privileges can be granted.

Granting a privilege to a role immediately affects active user sessions. When you grant a privilege to a role, it becomes immediately available to all users with that role enabled.

The following example creates two roles and assigns them different privileges on the same table.

1. Create table applog:

   => CREATE TABLE applog (id int, sourceID VARCHAR(32), data TIMESTAMP, event VARCHAR(256));
   

2. Create roles logreader and logwriter:

   => CREATE ROLE logreader;
   CREATE ROLE
   => CREATE ROLE logwriter;
   CREATE ROLE
   

3. Grant read-only privileges on applog to logreader, and write privileges to logwriter:

   => GRANT SELECT ON applog TO logreader;
   GRANT PRIVILEGE
   => GRANT INSERT ON applog TO logwriter;
   GRANT PRIVILEGE
   

Revoking privileges from roles

Use REVOKE statements to revoke a privilege from a role. Revoking a privilege from a role immediately affects active user sessions. When you revoke a privilege from a role, it is no longer available to users who have the privilege through that role.

For example:

=> REVOKE INSERT ON applog FROM logwriter;
REVOKE PRIVILEGE