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