Operations with privileges

  • GRANT — Grant privileges

  • REVOKE — Revoke privileges

  • SHOW GRANTS — Show a list of all privileges

  • SHOW OWNER — Show the owner of the object

grants
General schema of operations with privileges and roles

Grant privileges

Privileges on catalog

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

Grants the specified privileges on the catalog to a role or user.

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

Available catalog privileges:

  • ADMIN — all possible operations with the catalog, including granting privileges on it.

  • ALL — all possible operations with the catalog, except granting privileges on it.

  • CREATE USER — creating users.

  • CREATE ROLE — creating roles.

  • CREATE WORKER POOL — creating compute pools.

  • CREATE TABLE — creating tables.

  • CREATE VIEW — creating views.

  • CREATE SCHEMA — creating schemas.

  • MONITOR — retrieving catalog metadata.

Privileges on external catalogs

GRANT <external_catalog_privileges> ON EXTERNAL CATALOG <external_catalog_name> TO [ROLE | USER] <name>;

Grants the specified privileges on an external catalog to a role or user.

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

Available privileges for an external catalog:

  • OWNERSHIP — allows the owner of the external catalog to be changed.

  • SELECT — allows SELECT queries to be made on the external catalog.

Privileges on worker pools

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

Grants the specified privileges on a worker pool to a role or user.

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

Available worker pool privileges:

  • ADMIN — all possible operations with the worker pool, including granting privileges on it.

  • ALL — all possible operations with the worker pool, except granting privileges on it.

  • OWNERSHIP — changing the owner of a worker pool.

  • USAGE — using a worker pool.

  • MONITOR — monitoring a worker pool.

See example

Let’s grant the user tengri_user the USAGE and MONITOR privileges on the worker pool compute_xl.

GRANT USAGE, MONITOR
    ON WORKER POOL compute_xl
    TO tengri_user;

Privileges on schemas

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

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

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

Available schema privileges:

  • ADMIN — all possible operations with the schema, including granting privileges on it.

  • ALL — all possible operations with the schema, except granting privileges on it.

  • OWNERSHIP — allows the schema owner to be changed.

  • CREATE TABLE — allows you to create tables within a schema.

  • CREATE VIEW — allows you to create views within a schema.

  • ALTER — allows you to modify schema properties.

  • USAGE — grants access to objects within a schema.

  • MONITOR — grants access to schema metadata.

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;

Privileges on tables and views

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 the specified privileges to a role or user on a table or view within the specified schema.

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

Available privileges on tables and views:

  • ALL — allows all possible operations with the table except granting privileges on it.

  • OWNERSHIP — allows the table owner to be changed.

  • ALTER — allows table properties to be modified.

  • DROP — allows to delete a table.

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

  • SELECT — allows to read data from a table.

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

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

  • DELETE — allows to delete rows from a table.

  • MONITOR — allows to view a table’s metadata without reading data from it.

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

  • The EXISTING modifier restricts the granting of privileges to existing objects only.

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

If no modifier is specified, the action applies to all objects — both those that already exist and those that will be created in the future.

See example

Let’s grant the junior_admin role 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;

Privileges on users

GRANT <user_privileges> ON USER <user_name> TO [ROLE | USER] <name>;

Grants the specified user privileges to a role or user.

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

Available user privileges:

  • ADMIN — all possible operations with the user, including granting privileges on it.

  • ALL — all possible operations with the user, except granting privileges on it.

  • CREATE API TOKEN — allows you to create an API token on behalf of another user.

  • MONITOR — allows viewing a user’s metadata.

  • OWNERSHIP — allows changing a user’s owner.

Privileges on roles

GRANT OWNERSHIP ON ROLE <role_name> TO [ROLE | USER] <name>;

Grants a role or user the OWNERSHIP privilege for a role.

  • OWNERSHIP — allows the role owner to be changed.

Other role privileges are not supported.

Privileges on API tokens

GRANT OWNERSHIP ON API TOKEN <token_name> TO [ROLE | USER] <name>;

Grants a role or user the OWNERSHIP privilege on an API token.

  • OWNERSHIP — allows the owner of an API token to be changed.

Other privileges relating to API tokens are not supported.

Privileges on identity providers

GRANT OWNERSHIP ON IDP <idp_name> TO [ROLE | USER] <name>;

Grants a role or user the OWNERSHIP privilege for an identity provider.

  • OWNERSHIP — allows the owner of an identity provider to be changed.

Other privileges relating to identity providers are not supported.

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

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 <external_catalog_privileges>
    ON EXTERNAL CATALOG <external_catalog_name> FROM [ROLE | USER] <name>;

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

REVOKE <user_privileges>
    ON USER <user_name> FROM [ROLE | USER] <name>;

REVOKE OWNERSHIP
    ON ROLE <role_name> FROM [ROLE | USER] <name>;

REVOKE OWNERSHIP
    ON API TOKEN <api_token_name> FROM [ROLE | USER] <name>;

REVOKE OWNERSHIP
    ON IDP <identity_provider_name> FROM [ROLE | USER] <name>;

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

<..._privileges> — these are comma-separated privilege lists. The specific privileges depend on the target object.

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;

Show a list of all privileges

SHOW GRANTS ON <table_name | schema_name>;

Lists the privileges on the specified object (table or schema) for the current user.

See example 
SHOW GRANTS ON demo.weekdays;
+------------+---------------------------------+
| role       | privilege                       |
+------------+---------------------------------+
| test_user  | {SELECT on TABLE demo.weekdays} |
+------------+---------------------------------+
To see the privileges on an object for all users, you can use system table system.privileges.

Show the owner of the object

SHOW OWNER OF <table_name | schema_name>;

Show the owner (user name) of the specified object (table or schema).

See also