GRANT

Use the GRANT command to grant specific privileges to a user.

Syntax

Grant database privileges to a user.

GRANT {{CONNECT | CREATE}[, ...]| ALL [PRIVILEGES]}
ON DATABASE database_name [, ...]
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

Grant schema privileges to a user.

GRANT {{USAGE | CREATE}[, ...]| ALL [PRIVILEGES]}
ON SCHEMA schema_name [, ...]
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

Grant table privileges to a user.

GRANT {{SELECT | UPDATE | INSERT | DELETE} [, ...]| ALL [PRIVILEGES]}
ON { TABLE table_name [, ...]
    | ALL TABLES IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

Grant view privileges to a user.

GRANT {SELECT | INSERT | DELETE | UPDATE | ALL [PRIVILEGES]}
ON {VIEW view_name [, ...]
    | ALL VIEWS IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

Grant connection privileges to a user.

GRANT { USAGE | ALL [PRIVILEGES]}
ON { CONNECTION conn_name [, ...]
    | ALL CONNECTIONS IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

Grant secret privileges to a user.

GRANT { USAGE | ALL [PRIVILEGES]}
ON { SECRET secret_name [, ...]
    | ALL SECRETS IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

Grant function privileges to a user.

GRANT { EXECUTE | ALL [PRIVILEGES]}
ON { FUNCTION function_name [, ...]
    | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

For sources, sinks and materialized views, only the SELECT privilege can be assigned and revoked.

Grant source privileges to a user.

GRANT { SELECT | ALL [PRIVILEGES]}
ON {  source_or_table_name [, ...]
    | ALL SOURCES IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

Grant sink privileges to a user.

GRANT { SELECT | ALL [PRIVILEGES]}
ON {  sink_name [, ...]
    | ALL SINKS IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

Grant materialized view privileges to a user.

GRANT {SELECT | ALL [PRIVILEGES]}
ON {MATERIALIZED VIEW mv_name [, ...]
    | ALL MATERIALIZED VIEWS IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

Parameters

Parameter or clause	Description
WITH GRANT OPTION clause	The WITH GRANT OPTION clause allows the grantee to grant the privilege to other users.
GRANTED BY clause	The specified user after the GRANTED BY clause must be the current user. By default, the current user is root.

Grant privileges on future objects

To grant privileges that apply automatically to future objects created by a user, use the ALTER DEFAULT PRIVILEGES command. It sets default privileges without manually granting permissions for each new object.

Example

Grant all privileges for all sources in schema1 to user user1.

GRANT ALL PRIVILEGES
ON ALL SOURCES IN SCHEMA schema1
TO user1 GRANTED BY user;

Grant the SELECT privilege for materialized view schema1.mv1 to user user1. user1 is able to grant the SELECT privilege to other users.

GRANT SELECT
ON MATERIALIZED VIEW schema1.mv1
TO user1 WITH GRANT OPTION GRANTED BY user;

Grant the SELECT privileges for source s1 to user user1.

GRANT SELECT
ON SOURCE s1
TO user1;