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 [table] FROM entity - revoke table level permissions from an entity
  • REVOKE [permissions] ON [table(columns)] FROM entity - revoke column level permissions from an entity

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

Permission level readjustment#

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

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

Examples#

REVOKE command itself doesn't return any result, thus the effects of running SQL commands that follow are shown with SHOW PERMISSIONS john.

Revoke database level permissions#

REVOKE CREATE TABLE, DROP TABLE, SELECT FROM john;

Revoke table level permissions#

REVOKE ADD INDEX, REINDEX ON orders, trades FROM john;

Revoke column level permissions#

REVOKE INSERT ON orders(id, name), trades(id, quantity) FROM john WITH GRANT OPTION;

Revoke implicit column permissions#

While granting SELECT, INSERT or UPDATE permission on a column also grants the same permission on designated timestamp:

CREATE TABLE products(id INT, ts TIMESTAMP, name STRING) TIMESTAMP(ts);
GRANT UPDATE ON products(id) TO john;
GRANT UPDATE, INSERT ON products(name) TO john;
permissiontable_namecolumn_namegrant_optionorigin
INSERTproductsnamefG
INSERTproductstsfI
UPDATEproductsidfG
UPDATEproductsnamefG
UPDATEproductstsfI

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

REVOKE INSERT on products(name) FROM john;
permissiontable_namecolumn_namegrant_optionorigin
UPDATEproductsidfG
UPDATEproductsnamefG
UPDATEproductstsfI

However, if there's even a single column with permission left, implicit permission is kept:

REVOKE UPDATE ON products(id) FROM john;
permissiontable_namecolumn_namegrant_optionorigin
UPDATEproductsnamefG
UPDATEproductstsfI

Revoke on column level optimized away by database-level permission#

REVOKE SELECT ON products(id) FROM john;

Revoke permission with level readjustment#

Assuming we've orders, trades and products tables, revoking database level permissions on table level, e.g.

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

replaces database permission with table level permissions on all existing tables, except the one being revoked.

permissiontable_namecolumn_namegrant_optionorigin
SELECTordersfG
SELECTproductsfG

That means permission won't apply to any newly-created tables, including new_tab but does apply to columns added to tables that existed at time of revoke , e.g. products.update_ts :

CREATE TABLE new_tab( id INT );
ALTER TABLE products ADD COLUMN update_ts TIMESTAMP;
permissiontable_namecolumn_namegrant_optionorigin
SELECTordersfG
SELECTproductsfG

Following table level revoke replaces table level permission on products table with per-column permissions (excluding id column):

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

Revoke permissions inherited from group#

Permissions of groups the user is member of are applied after user permissions, thus it is not possible to revoke them on user level.

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:

  • user has to be removed from all groups he/she inherits it from or
  • all groups that have the permission need the permission revoked.
REVOKE SELECT on products from admins;
-- or
REMOVE USER john from admins;

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