REVOKE reference

REVOKE - revoke permission from user, group or service account.

Syntax#

Flow chart showing the syntax of the REVOKE keyword

Description#

  • REVOKE [permissions] FROM entity - revoke database level permissions from an entity
  • REVOKE [permissions] ON ALL TABLES FROM entity - revoke table/column level permissions on database level from an entity
  • REVOKE [permissions] ON [table] FROM entity - revoke table/column level permissions on table level from an entity
  • REVOKE [permissions] ON [table(columns)] FROM entity - revoke column level permissions on column level from an entity

Revoke database level permissions#

REVOKE CREATE TABLE FROM john;

Revoke table level permissions for entire database#

REVOKE ADD INDEX, REINDEX ON ALL TABLES FROM john;

Revoke table level permissions on specific tables#

REVOKE ADD INDEX, REINDEX ON orders FROM john;

Revoke column level permissions for entire database#

REVOKE SELECT ON ALL TABLES FROM john;

Revoke column level permissions on specific tables#

REVOKE SELECT ON orders, trades FROM john;

Revoke column level permissions on specific columns#

REVOKE SELECT ON orders(id, name) FROM john;

Implicit permissions#

If the target table has implicit timestamp permissions, then revoking SELECT or UPDATE permission on all other table columns also revokes it on the designated timestamp column:

CREATE TABLE products(id INT, name STRING, ts TIMESTAMP) TIMESTAMP(ts);
GRANT SELECT ON products(id) TO john;
GRANT SELECT, UPDATE ON products(name) TO john;
permissiontable_namecolumn_namegrant_optionorigin
UPDATEproductsnamefG
UPDATEproductstsfI
SELECTproductsidfG
SELECTproductsnamefG
SELECTproductstsfI

Revoking a permission from all columns revokes the implicitly granted permission from the designated timestamp column:

REVOKE UPDATE ON products(name) FROM john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTproductsidfG
SELECTproductsnamefG
SELECTproductstsfI

However, if there is even a single column which still has the permission, then the implicit permission is kept:

REVOKE SELECT ON products(id) FROM john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTproductsnamefG
SELECTproductstsfI

Permission level readjustment#

If the user has a database- or table-level permission, then revoking it on a lower level triggers permission level re-adjustment. Permission is switched to lower level and materialized:

  • database level permission is pushed to table level, so e.g. SELECT will not apply to any new tables
  • table level permission is pushed to column level, so e.g. SELECT will not apply to any new table columns

For example, assume we have the following tables: orders, trades and products, and revoking a permission from a table which was granted on database level previously.

GRANT SELECT ON ALL TABLES TO john;
REVOKE SELECT ON trades FROM john;

Database level permission is replaced with table level on all existing tables, except the one being revoked.

permissiontable_namecolumn_namegrant_optionorigin
SELECTordersfG
SELECTproductsfG

As a consequence permission, which was granted for all tables previously, will not apply to any newly-created tables:

CREATE TABLE new_tab( id INT );
permissiontable_namecolumn_namegrant_optionorigin
SELECTordersfG
SELECTproductsfG

Permission level re-adjustment can also happen from the table level to the column level. For example, the following column level revoke replaces the table level permission on the products table with column level permissions:

REVOKE SELECT on products(id) FROM john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTordersfG
SELECTproductsnamefG

Revoke permissions inherited from group#

Permissions of groups are applied after user permissions, thus it is not possible to revoke them directly from the user.

CREATE group admins;
GRANT SELECT on products to admins;
ADD USER john to admins;
REVOKE SELECT on products from john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTproductsfG

To do so, either:

  • the user has to be removed from the group where the permission is inherited from
  • or the permission has to be revoked from the group
REVOKE SELECT on products FROM admins;
-- or
REMOVE USER john FROM admins;

โญ Something missing? Page not helpful? Please suggest an edit on GitHub.