REVOKE

Synopsis

Use the REVOKE statement to remove access privileges from one or more roles.

Syntax

revoke_table ::= REVOKE [ GRANT OPTION FOR ] 
                 { { SELECT
                     | INSERT
                     | UPDATE
                     | DELETE
                     | TRUNCATE
                     | REFERENCES
                     | TRIGGER } [ , ... ]
                   | ALL [ PRIVILEGES ] }  ON 
                 { [ TABLE ] table_name [ , ... ]
                   | ALL TABLES IN SCHEMA schema_name [ , ... ] }  
                 FROM { [ GROUP ] role_name | PUBLIC } [ , ... ] 
                 [ CASCADE | RESTRICT ]

revoke_table_col ::= REVOKE [ GRANT OPTION FOR ]  
                     { { SELECT | INSERT | UPDATE | REFERENCES } ( 
                       column_names ) [ ,(column_names ... ]
                       | ALL [ PRIVILEGES ] ( column_names ) }  ON 
                     [ TABLE ] table_name [ , ... ] FROM 
                     { [ GROUP ] role_name | PUBLIC } [ , ... ] 
                     [ CASCADE | RESTRICT ]

revoke_seq ::= REVOKE [ GRANT OPTION FOR ] 
               { { USAGE | SELECT | UPDATE } [ , ... ]
                 | ALL [ PRIVILEGES ] }  ON 
               { SEQUENCE sequence_name [ , ... ]
                 | ALL SEQUENCES IN SCHEMA schema_name [ , ... ] }  
               FROM { [ GROUP ] role_name | PUBLIC } [ , ... ] 
               [ CASCADE | RESTRICT ]

revoke_db ::= REVOKE [ GRANT OPTION FOR ] 
              { { CREATE | CONNECT | TEMPORARY | TEMP } [ , ... ]
                | ALL [ PRIVILEGES ] } ON DATABASE database_name 
              [ , ... ]  FROM { [ GROUP ] role_name | PUBLIC } 
              [ , ... ] [ CASCADE | RESTRICT ]

revoke_domain ::= REVOKE [ GRANT OPTION FOR ] 
                  { USAGE | ALL [ PRIVILEGES ] } ON DOMAIN domain_name 
                  [ , ... ]  FROM { [ GROUP ] role_name | PUBLIC } 
                  [ , ... ] [ CASCADE | RESTRICT ]

revoke_schema ::= REVOKE [ GRANT OPTION FOR ] 
                  { { CREATE | USAGE } [ , ... ]
                    | ALL [ PRIVILEGES ] } ON SCHEMA schema_name 
                  [ , ... ]  FROM { [ GROUP ] role_name | PUBLIC } 
                  [ , ... ] [ CASCADE | RESTRICT ]

revoke_type ::= REVOKE [ GRANT OPTION FOR ] 
                { USAGE | ALL [ PRIVILEGES ] } ON TYPE type_name 
                [ , ... ]  FROM { [ GROUP ] role_name | PUBLIC } 
                [ , ... ] [ CASCADE | RESTRICT ]

revoke_role ::= REVOKE [ ADMIN OPTION FOR ] role_name [ , ... ] FROM 
                role_name [ , ... ] [ CASCADE | RESTRICT ]

revoke_table

REVOKEGRANTOPTIONFOR,SELECTINSERTUPDATEDELETETRUNCATEREFERENCESTRIGGERALLPRIVILEGESONTABLE,table_nameALLTABLESINSCHEMA,schema_nameFROM,GROUProle_namePUBLICCASCADERESTRICT

revoke_table_col

REVOKEGRANTOPTIONFORSELECTINSERTUPDATEREFERENCES(column_names),(column_names)ALLPRIVILEGES(column_names)ONTABLE,table_nameFROM,GROUProle_namePUBLICCASCADERESTRICT

revoke_seq

REVOKEGRANTOPTIONFOR,USAGESELECTUPDATEALLPRIVILEGESONSEQUENCE,sequence_nameALLSEQUENCESINSCHEMA,schema_nameFROM,GROUProle_namePUBLICCASCADERESTRICT

revoke_db

REVOKEGRANTOPTIONFOR,CREATECONNECTTEMPORARYTEMPALLPRIVILEGESONDATABASE,database_nameFROM,GROUProle_namePUBLICCASCADERESTRICT

revoke_domain

REVOKEGRANTOPTIONFORUSAGEALLPRIVILEGESONDOMAIN,domain_nameFROM,GROUProle_namePUBLICCASCADERESTRICT

revoke_schema

REVOKEGRANTOPTIONFOR,CREATEUSAGEALLPRIVILEGESONSCHEMA,schema_nameFROM,GROUProle_namePUBLICCASCADERESTRICT

revoke_type

REVOKEGRANTOPTIONFORUSAGEALLPRIVILEGESONTYPE,type_nameFROM,GROUProle_namePUBLICCASCADERESTRICT

revoke_role

REVOKEADMINOPTIONFOR,role_nameFROM,role_nameCASCADERESTRICT

Semantics

Any role has the sum of all privileges assigned to it. So, if REVOKE is used to revoke SELECT from PUBLIC, then it does not mean that all roles have lost SELECT privilege. If a role had SELECT granted directly to it or inherited it via a group, then it can continue to hold the SELECT privilege.

If GRANT OPTION FOR is specified, only the grant option for the privilege is revoked, not the privilege itself. Otherwise, both the privilege and the grant option are revoked.

Similarly, while revoking a role, if ADMIN OPTION FOR is specified, then only the admin option for the privilege is revoked.

If a user holds a privilege with grant option and has granted it to other users, then revoking the privilege from the first user will also revoke it from dependent users if CASCADE is specified. Otherwise, the REVOKE will fail.

When revoking privileges on a table, the corresponding column privileges (if any) are automatically revoked on each column of the table, as well. On the other hand, if a role has been granted privileges on a table, then revoking the same privileges from individual columns will have no effect.

Examples

  • Revoke SELECT privilege for PUBLIC on table 'stores'
yugabyte=# REVOKE SELECT ON stores FROM PUBLIC;
  • Remove user John from SysAdmins group.
yugabyte=# REVOKE SysAdmins FROM John;

See also