GRANT reference

GRANT - grants permissions to a user, group or service account.

Syntax#

Flow chart showing the syntax of the GRANT keyword

Description#

  • GRANT [permissions] TO entity - grant database level permissions on database level to an entity
  • GRANT [permissions] ON ALL TABLES TO entity - grant table/column level permissions on database level to an entity
  • GRANT [permissions] ON [table] TO entity - grant table/column level permissions on table level to an entity
  • GRANT [permissions] ON [table(columns)] TO entity - grant column level permissions on column level to an entity

Grant database level permissions#

GRANT CREATE TABLE, SNAPSHOT TO john;
permissiontable_namecolumn_namegrant_optionorigin
CREATE TABLEfG
SNAPSHOTfG

Grant table level permissions for entire database#

GRANT ADD INDEX, REINDEX ON ALL TABLES TO john;
permissiontable_namecolumn_namegrant_optionorigin
ADD INDEXfG
REINDEXfG

Grant table level permissions on specific tables#

GRANT ADD INDEX, REINDEX ON orders, trades TO john;
permissiontable_namecolumn_namegrant_optionorigin
ADD INDEXtradesfG
REINDEXtradesfG
ADD INDEXordersfG
REINDEXordersfG

Grant column level permissions for entire database#

GRANT SELECT ON ALL TABLES TO john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTfG

Grant column level permissions on specific tables#

GRANT SELECT ON orders TO john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTordersfG

Grant column level permissions on specific columns#

GRANT SELECT ON orders(id, name), trades(id, quantity) TO john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTtradesidfG
SELECTtradesquantityfG
SELECTordersidfG
SELECTordersnamefG

Grant option#

If the WITH GRANT OPTION clause is present, then the target entity is allowed to grant the permissions to other entities. If the entity already has permissions matching those being granted, their grant option is overwritten.

GRANT SELECT ON ALL TABLES TO john WITH GRANT OPTION;
permissiontable_namecolumn_namegrant_optionorigin
SELECTtG
GRANT SELECT ON ALL TABLES TO john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTfG

Verification#

By default, GRANT does not check whether entities exist, making it possible to grant permissions to users, groups or service accounts that are later created.

To make sure that the target entity of the grant statement exists, use verification. The WITH VERIFICATION clause enables checks on the target entity and causes the GRANT statement to fail if the entity does not exist.

GRANT SELECT ON orders TO john WITH VERIFICATION;

Implicit permissions#

In QuestDB, the timestamp column of a table is crucial for time-series operations like ASOF and LT joins, SAMPLE BY and interval scans. If a user can access some columns but not the timestamp column, they cannot execute most queries.

Therefore when a table has a designated timestamp, granting SELECT or UPDATE permissions on any column will automatically extend those permissions to the timestamp column. These are known as implicit permissions, and they're indicated by an I in the origin column of the SHOW PERMISSIONS output.

For example, if you grant UPDATE permission on the id column of the products table, the timestamp column also receives UPDATE permission:

CREATE TABLE products(id int, name string, ts timestamp) timestamp(ts);
GRANT UPDATE ON products(id) TO john;
permissiontable_namecolumn_namegrant_optionorigin
UPDATEproductsidfG
UPDATEproductstsfI

Optimization#

When granting permissions on the table or column level, sometimes it might seem like there is no effect when cross-checking with the SHOW permissions command. If QuestDB detects that the permission is already granted on a higher level, it optimizes and removes any child permissions. Doing so keeps the access list model simple and permission checks faster.

For example, granting the same permission on the database and table level shows will show the permission on database level only:

GRANT INSERT ON ALL TABLES TO john;
GRANT INSERT ON products TO john;
permissiontable_namecolumn_namegrant_optionorigin
INSERTfG

Granting the same permission on the table and column level shows permission on the table level only:

GRANT SELECT ON products TO john;
GRANT SELECT ON products(id) TO john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTproductsfG

Grant ahead of table or column creation#

Grant permissions ahead of table or column creation:

GRANT SELECT ON countries TO john;
GRANT UPDATE ON countries(id) TO john;
GRANT UPDATE ON countries(description) TO john;

Such permissions do not show on SHOW PERMISSIONS output.

permissiontable_namecolumn_namegrant_optionorigin

However, when the table is created, then the applicable permissions appear:

CREATE TABLE countries (id INT, name STRING, iso_code STRING);
permissiontable_namecolumn_namegrant_optionorigin
SELECTcountriesfG
UPDATEcountriesidfG

When 'missing' columns are later added to the table, then more permissions appear:

ALTER TABLE countries ADD COLUMN description string;
permissiontable_namecolumn_namegrant_optionorigin
SELECTcountriesfG
UPDATEcountriesidfG
UPDATEcountriesdescriptionfG

Grant when table or column is dropped and recreated#

Granted permissions are not automatically revoked when related tables or columns are dropped. Instead, they have no effect until table or column is recreated.

CREATE TABLE countries (id INT, name STRING, iso_code STRING);
GRANT SELECT ON countries TO john;
GRANT UPDATE ON countries(iso_code) TO john;
permissiontable_namecolumn_namegrant_optionorigin
SELECTcountriesfG
UPDATEcountriesiso_codefG

Now, if the table is dropped, then permission stops being visible:

DROP TABLE countries;
permissiontable_namecolumn_namegrant_optionorigin

When the table is later recreated, permission are in full effect again :

CREATE TABLE countries (id INT, name STRING, iso_code int, alpha2 STRING);
permissiontable_namecolumn_namegrant_optionorigin
SELECTcountriesfG
UPDATEcountriesfG
note

Only the table and/or column name is used when applying permission. The type is ignored. In the example above iso_code was initially of string type, then recreated as int.

Owner grants#

In QuestDB there are no owners of database objects. Instead, there are owner grants.

An owner grant means:

  • if a user creates a table, the user automatically gets all table level permissions with the grant option on the table
  • if a user adds a new column to an existing table, the user automatically gets all column level permissions with the grant option on the column

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