Operations with privileges

  • GRANT — Grant privileges

  • REVOKE — Revoke privileges

General schema of operations with privileges and roles
General schema of operations with privileges and roles

Grant privileges

Scheme Privileges

GRANT <schema_privileges> ON SCHEMA <schema_name> TO [ROLE | USER] <name>;

Grants the role or user the specified privileges to the specified schema.

<schema_privileges> — is a comma-separated list of privileges.

Available privileges on schemas:

  • ALL — all possible actions on schemas.

  • ADMIN (alias of ALL) — all possible actions on schemas.

  • MONITOR — allows access to metainformation about the schema.

  • USAGE — allows access to objects in the schema.

  • CREATE TABLE — allows to create tables in the schema.

  • CREATE VIEW — allows to create views in the schema.

The ROLE and USER keywords before the target role name or user name are optional.
See example

Let’s grant the junior_admin role the MONITOR and MODIFY privileges on the main_schema schema.

GRANT MONITOR, MODIFY
    ON SCHEMA main_schema
    TO ROLE junior_admin;

Table and view privileges

Direct syntax:

GRANT <table_privileges>
    ON (TABLE | VIEW) <table_name>
    TO [ROLE | USER] <name>;

Temporal syntax:

GRANT <table_privileges>
    ON ALL [EXISTING] [[AND] FUTURE] (TABLES | VIEWS | TABLES AND VIEWS)
    IN [SCHEMA] <schema_name>
    TO [ROLE | USER] <name>;

Grants a role or user the specified privileges on a table or view within the specified schema.

<table_privileges> — is a comma-separated list of privileges.

Available privileges on tables and views:

  • ALL — all possible actions on tables.

  • ADMIN (alias of ALL) — all possible actions on tables.

  • SELECT — allows to read data from a table.

  • INSERT — allows to add new rows to the table.

  • UPDATE — allows to modify existing rows in a table.

  • DELETE — allows to delete rows from the table.

  • MODIFY — allows to change the table structure.

  • OWNERSHIP — allows to change the owner of a table.

  • TRUNCATE — allows to delete all rows from a table.

  • DROP — allows to delete a table.

To grant privilege to all existing (or those to be created in the future) objects within a schema, the temporal syntax is used.

  • The EXISTING modifier restricts privilege granting to existing objects only.

  • The FUTURE modifier restricts privilege granting to objects that will be created in the future.

If no modifier is specified, the action applies to all objects — both existing objects and objects to be created in the future.

The ROLE and USER keywords before the target role name or user name are optional.
See example

Grant the junior_admin role the SELECT and INSERT privileges on all existing tables and views within the main_schema schema.

GRANT SELECT, INSERT
    ON EXISTING TABLES AND VIEWS
    IN SCHEMA main_schema
    TO ROLE junior_admin;

Catalogue Privileges

GRANT <catalog_privileges> ON CATALOG TO [ROLE | USER] <name>;

Grants the role or user the specified privileges to the directory.

<catalog_privileges> — is a comma-separated list of privileges.

Available catalogue privileges:

  • ALL — all possible actions on the catalogue.

  • ADMIN (alias of ALL) — all possible actions on the catalogue.

  • CREATE USER — creating users.

  • CREATE ROLE — creating roles.

  • CREATE WORKER POOL — creating computing pools.

  • CREATE SCHEMA — creating schemas.

Temporal modifiers:

  • The EXISTING modifier restricts privilege granting to existing objects only.

  • The FUTURE modifier restricts the granting of privileges to objects that will be created in the future.

The ROLE and USER keywords before the target role name or user name are optional.

Compute pool privileges

GRANT <worker_pool_privileges> ON WORKER POOL <worker_pool_name> TO [ROLE | USER] <name>;

Grants a role or user the specified privileges on a compute pool.

<worker_pool_privileges> — is a comma-separated list of privileges.

Available privileges on compute pools:

  • ALL — all possible actions on the compute pool.

  • ADMIN (alias of ALL) — all possible actions on the compute pool.

  • ALTER — modify the compute pool (permission to execute the command ALTER WORKER POOL).

  • USAGE — using the compute pool (permission to execute the command USE WORKER POOL).

  • MONITOR — monitoring of the computing pool.

See example

Give the user tengri_user the USAGE and MONITOR privileges on the compute_xl compute pool.

GRANT USAGE, MONITOR
    ON WORKER POOL compute_xl
    TO tengri_user;

Revoke privileges

REVOKE <schema_privileges>
    ON SCHEMA <schema_name> FROM [ROLE | USER] <name>;

REVOKE <table_privileges>
    ON (TABLE | VIEW) <table_name> [IN SCHEMA <schema_name>] FROM [ROLE | USER] <name>;

REVOKE <catalog_privileges>
    ON CATALOG FROM [ROLE | USER] <name>;

REVOKE <worker_pool_privileges>
    ON WORKER POOL <worker_pool_name> FROM [ROLE | USER] <name>;

Revokes the specified privileges on the specified object from the role or user.

<schema_privileges>, <table_privileges>, <catalog_privileges>, and <worker_pool_privileges> — these are comma-separated privilege lists. The specific privileges depend on the target object.

The EXISTING modifier restricts privilege revocation to existing objects only.

The FUTURE modifier restricts privilege revocation to objects that will be created in the future.

See example

Let’s revoke the Junior_admin role’s SELECT and INSERT privileges on all existing tables and views within the main_schema schema.

REVOKE SELECT, INSERT
    ON EXISTING TABLES AND VIEWS
    IN SCHEMA main_schema
    FROM ROLE junior_admin;