Privilege operations

GRANT — Grant privileges
REVOKE — Revoke privileges

Grant privileges

Scheme Privileges

GRANT <schema_privileges> ON SCHEMA <schema_name> TO [ROLE] <role_name>;

Grants the role the specified privileges to the specified schema.

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

Available privileges on schemas:

ADMIN — allows deletion of the schema and distribution of schema privileges.
MONITOR — allows access to metainformation about the schema.
USAGE — allows access to objects in the scheme.
MODIFY — allows modification of schema properties.
CREATE TABLE — allows you to create tables in the schema.
CREATE VIEW — allows to create views in the schema.

The ROLE keyword before the target role name is 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] <role_name>;

Temporal syntax:

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

Grants roles 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:

SELECT — allows you to read data from a table.
INSERT — allows you to add new rows to the table.
UPDATE — allows you to modify existing rows in the table.
DELETE — allows to delete rows from the table.
MODIFY — allows to change the table structure.
OWNERSHIP — allows to change the owner of the 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, all objects, both existing and future, are affected.

The ROLE keyword before the target role name is 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] <role_name>;

Grants the role the specified directory privileges.

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

Available privileges per catalogue:

ADMIN — any action on the catalogue.
CREATE USER — creating users.
CREATE WORKER POOL — creating computing pools.
CREATE SCHEMA — creating schemas.

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.

The ROLE keyword before the target role name is optional.

Compute pool privileges

GRANT <worker_pool_privileges> ON WORKER POOL <worker_pool_name> TO [ROLE] <role_name>;

Grants the role the specified compute pool privileges.

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

Available privileges on compute pools:

ALL — all possible actions on the compute pool.
ADMIN — modify the compute pool (privileges to execute the command ALTER WORKER POOL).
USAGE — using the computing pool (rights to execute the command USE WORKER POOL).
MONITOR — monitoring of the computing pool.

See example

Grant the junior_analyst role the USAGE and MONITOR privileges on the compute_xl compute pool.

GRANT USAGE, MONITOR
    ON WORKER POOL compute_xl
    TO junior_analyst;

Revoke privileges

REVOKE <schema_privileges>
    ON SCHEMA <schema_name> FROM ROLE <role_name>;

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

REVOKE <catalog_privileges>
    ON CATALOG FROM ROLE <role_name>;

REVOKE <worker_pool_privileges>
    ON WORKER POOL <worker_pool_name> FROM ROLE <role_name>;

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

<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;