GRANT reference

GRANT - grants permission to user, group or service account.

Syntax

Description

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

Designated timestamp column of a table is central to many time-series functionalities, e.g. ASOF and LT join, sample by, interval scan. User having access to some columns of a table except designated timestamp would not be able to execute most of the queries, making it unusable. That is why if target table has designated timestamp then granting SELECT or UPDATE permission on any table column also grants it on designated timestamp. Such permissions are called 'implicit' and are marked with I value in origin column of SHOW PERMISSIONS output.

Grant option

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

Verification

By default, GRANT doesn't check whether tables or entities exist, making it possible to grant permissions ahead of table creation. WITH VERIFICATION enables such checks and causes GRANT to fail if target tables or entities don't exist.

Optimization

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

Examples

GRANT command itself does not return any result, thus the effects of running SQL commands that follow are shown with SHOW PERMISSIONS john.

Grant database level permissions

GRANT CREATE TABLE, DROP TABLE TO john;

permission	table_name	column_name	grant_option	origin
CREATE TABLE			f	G
DROP TABLE			f	G
SELECT			f	G

Grant table level permissions

GRANT ADD INDEX, REINDEX ON orders, trades TO john;

permission	table_name	column_name	grant_option	origin
ADD INDEX	trades		f	G
REINDEX	trades		f	G
ADD INDEX	orders		f	G
REINDEX	orders		f	G

Grant column level permissions

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

permission	table_name	column_name	grant_option	origin
INSERT	trades	id	t	G
INSERT	trades	quantity	t	G
INSERT	orders	id	t	G
INSERT	orders	name	t	G

Grant permissions overwriting existing grant option

GRANT SELECT TO john;

permission	table_name	column_name	grant_option	origin
SELECT			f	G

GRANT SELECT TO john WITH GRANT OPTION;

permission	table_name	column_name	grant_option	origin
SELECT			t	G

Grant implicit column permissions

Granting UPDATE on products table grants same permission on table's designated timestamp:

CREATE TABLE products(id int, ts timestamp, name string) timestamp(ts);

GRANT UPDATE ON products(id) TO john;

permission	table_name	column_name	grant_option	origin
UPDATE	products	id	f	G
UPDATE	products	ts	f	I

Grant on column level optimized away by database-level permission

Granting same permission on database and table level shows permission on database level only:

GRANT INSERT TO john;

GRANT INSERT ON products TO john;

permission	table_name	column_name	grant_option	origin
INSERT			f	G

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

GRANT SELECT ON products TO john;

GRANT SELECT ON products(id) TO john;

permission	table_name	column_name	grant_option	origin
SELECT	products		f	G

Grant ahead of table or column creation

It is possible to grant permissions ahead of table or column creation, e.g.

GRANT SELECT ON countries TO john;

GRANT INSERT ON countries(id) TO john;

GRANT INSERT ON countries(description) TO john;

Such permissions don't show on SHOW PERMISSIONS output.

permission	table_name	column_name	grant_option	origin

However, when table is created, applicable permissions start appearing:

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

permission	table_name	column_name	grant_option	origin
SELECT	countries		f	G
INSERT	countries	id	f	G

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

ALTER TABLE countries ADD COLUMN description string;

permission	table_name	column_name	grant_option	origin
SELECT	countries		f	G
INSERT	countries	id	f	G
INSERT	countries	description	f	G

Grant when table or column is dropped and recreated

Granted permissions aren't 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;

permission	table_name	column_name	grant_option	origin
SELECT	countries		f	G
UPDATE	countries		f	G

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

DROP TABLE countries;

permission	table_name	column_name	grant_option	origin

When table is later recreated, permission goes into effect again :

CREATE TABLE countries (id INT, name STRING, iso_code int, alpha2 STRING);

permission	table_name	column_name	grant_option	origin
SELECT	countries		f	G
UPDATE	countries		f	G

note

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

Creating table grants all permissions to its creator

Creating a table as non-superadmin user grants all permissions on it to creator:

create table users ( id int, name string );

permission	table_name	column_name	grant_option	origin
SELECT	users		t	G
INSERT	users		t	G
UPDATE	users		t	G
DROP TABLE	users		t	G
RENAME TABLE	users		t	G
TRUNCATE TABLE	users		t	G
ADD COLUMN	users		t	G
DROP COLUMN	users		t	G
RENAME COLUMN	users		t	G
ALTER COLUMN CACHE	users		t	G
ADD INDEX	users		t	G
DROP INDEX	users		t	G
REINDEX	users		t	G
ATTACH PARTITION	users		t	G
DETACH PARTITION	users		t	G
DROP PARTITION	users		t	G
SET TABLE PARAM	users		t	G
SET TABLE TYPE	users		t	G
RESUME WAL	users		t	G
BACKUP TABLE	users		t	G
VACUUM TABLE	users		t	G
ADD COLUMN OVER ILP	users		t	G
INSERT OVER ILP	users		t	G

Creating table grants all permissions to its creator

Creating a column as non-superadmin user grants all permissions on it to creator, even if the only permission user has is ADD COLUMN:

-- as admin

CREATE TABLE addresses( id INT, city STRING );

GRANT ADD COLUMN ON addresses TO john;

-- as john

ALTER TABLE addresses ADD COLUMN street STRING ;

permission	table_name	column_name	grant_option	origin
ADD COLUMN	addresses		f	G
SELECT	addresses	street	t	G
INSERT	addresses	street	t	G
UPDATE	addresses	street	t	G
DROP COLUMN	addresses	street	t	G
RENAME COLUMN	addresses	street	t	G
ALTER COLUMN CACHE	addresses	street	t	G
ADD INDEX	addresses	street	t	G
DROP INDEX	addresses	street	t	G
REINDEX	addresses	street	t	G