GRANT PRIVILEGES

Organization Privileges

Syntax

GRANT [ 
      CREATE_DATABASE 
      | CREATE_STORE 
      | CREATE_SCHEMA_REGISTRY
      | CREATE_DESCRIPTOR_SOURCE 
      | CREATE_FUNCTION_SOURCE | CREATE_FUNCTION
      | CREATE_QUERY
      | MANAGE_MEMBERS
      | MANAGE_GRANTS
      | ALL PRIVILEGES
      , ...
      ]
ON ORGANIZATION
TO ROLE role_name [, ...]
[WITH GRANT OPTION];

Description

Grants Organization privileges to one or more roles.

The current role requires one of the following privileges:

Ownership of Organization
MANAGE_GRANTS privilege on Organization
Privilege granted to the current role WITH GRANT OPTION.

Arguments

CREATE_DATABASE

Allow role to create databases under the organization.

CREATE_STORE

Allow role to define stores under the organization.

CREATE_SCHEMA_REGISTRY

Allow role to define schema registries under the organization.

CREATE_DESCRIPTOR_SOURCE

Allow role to upload descriptor sources to the organization.

CREATE_FUNCTION_SOURCE

Allow role to upload UDF and UDAF sources to the organization.

CREATE_FUNCTION

Allow role to define a new UDF or UDAF under the organization. The role will also require USAGE privileges to the function source.

CREATE_QUERY

Allow role to launch a new query under the organization. The role also has additional privileges on database, schema, relations, and stores to launch the query.

MANAGE_MEMBERS

Allow role to manage roles, invitations, and users.

MANAGE_GRANTS

Allow role to manage all privilege grants within the organization.

ALL PRIVILEGES

Grants all the privileges listed above to the role.

role_name [, ...]

One or more roles to grant the privileges to.

WITH GRANT OPTION

Grants privileges that allow the role to grant those same privileges to other roles.

Example

<no-db>/<no-store># GRANT CREATE_DATABASE, CREATE_STORE ON ORGANIZATION TO rol1, rol2;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "create_database,           |
|                 |          | create_store" on "MR main" granted to    |
|                 |          | "rol1, rol2"                             |
+-----------------+----------+------------------------------------------+
<no-db>/<no-store># DESCRIBE ROLE rol1;
+--------------+---------+-----------------+--------------------+---------------+
|  Type        |  Name   |  Privilege      |  With Grant Option |  Granted By   |
+==============+=========+=================+====================+===============+
| role         | public  | usage           | false              | orgadmin      |
+--------------+---------+-----------------+--------------------+---------------+
| organization | MR main | create_database | false              | securityadmin |
+--------------+---------+-----------------+--------------------+---------------+
| organization | MR main | create_store    | false              | securityadmin |
+--------------+---------+-----------------+--------------------+---------------+

<no-db>/<no-store># GRANT CREATE_DATABASE, CREATE_STORE ON ORGANIZATION TO rol1, rol2 WITH GRANT OPTION;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "create_database,           |
|                 |          | create_store" on "MR main" granted to    |
|                 |          | "rol1, rol2"                             |
+-----------------+----------+------------------------------------------+
<no-db>/<no-store># DESCRIBE ROLE rol1;
+--------------+---------+-----------------+--------------------+---------------+
|  Type        |  Name   |  Privilege      |  With Grant Option |  Granted By   |
+==============+=========+=================+====================+===============+
| role         | public  | usage           | false              | orgadmin      |
+--------------+---------+-----------------+--------------------+---------------+
| organization | MR main | create_database | true               | securityadmin |
+--------------+---------+-----------------+--------------------+---------------+
| organization | MR main | create_store    | true               | securityadmin |
+--------------+---------+-----------------+--------------------+---------------+

Database Privileges

GRANT [
      USAGE 
      | CREATE
      | ALL PRIVILEGES
      , ...
      ]
ON DATABASE database_name
TO ROLE role_name [, ...]
[WITH GRANT OPTION];

Description

Grants database privileges to one or more roles.

Arguments

USAGE

Allow role to list and use the database. The role also requires additional privileges on schema as well as relations to use them.

CREATE

Allow role to create schemas under the database.

ALL PRIVILEGES

Grants all the privileges listed above to the role.

database_name

The name of the database to granted privileges on.

role_name [, ...]

One or more roles to grant the privileges to.

WITH GRANT OPTION

Grants privileges that allow the role to grant the same privileges to other roles.

Example

<no-db>/<no-store># GRANT USAGE ON DATABASE user_db TO rol1;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage" on "user_db"        |
|                 |          | granted to "rol1"                        |
+-----------------+----------+------------------------------------------+
<no-db>/<no-store># DESCRIBE ROLE rol1;
+----------+---------+------------+--------------------+---------------+
|  Type    |  Name   |  Privilege |  With Grant Option |  Granted By   |
+==========+=========+============+====================+===============+
| role     | public  | usage      | false              | orgadmin      |
+----------+---------+------------+--------------------+---------------+
| database | user_db | usage      | false              | securityadmin |
+----------+---------+------------+--------------------+---------------+

<no-db>/<no-store># GRANT USAGE,CREATE ON DATABASE user_db TO rol1 WITH GRANT OPTION;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage" on "user_db"        |
|                 |          | granted to "rol1"                        |
+-----------------+----------+------------------------------------------+
<no-db>/<no-store># DESCRIBE ROLE rol1;
+----------+---------+------------+--------------------+---------------+
|  Type    |  Name   |  Privilege |  With Grant Option |  Granted By   |
+==========+=========+============+====================+===============+
| role     | public  | usage      | false              | orgadmin      |
+----------+---------+------------+--------------------+---------------+
| database | user_db | usage      | true               | securityadmin |
+----------+---------+------------+--------------------+---------------+
| database | user_db | create     | true               | securityadmin |
+----------+---------+------------+--------------------+---------------+

Database Schema Privileges

GRANT [
      USAGE 
      | CREATE
      | ALL PRIVILEGES
      ]
ON SCHEMA schema_name
TO ROLE role_name [, ...]
[WITH GRANT OPTION];

Description

Grants schema privileges to one or more roles.

Arguments

USAGE

Allow role to list and use the schemas. The role also has additional privileges on relations to use them.

CREATE

Allow role to create relations under the schema.

ALL PRIVILEGES

Grants all the privileges listed above to the role.

schema_name

The qualified name of the schema to grant privileges on. This name can include a specific database name to form a fully-qualified name in the format of <database_name>.<schema_name>; otherwise the system uses the current database name in the session.

role_name [, ...]

One or more roles to which to grant the privileges.

WITH GRANT OPTION

Grants privileges that allow the role to grant those same privileges to other roles.

Example

<no-db>/<no-store># GRANT USAGE,CREATE ON SCHEMA accounting_db.public TO rol1;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage, create" on          |
|                 |          | "public" granted to "rol1"               |
+-----------------+----------+------------------------------------------+
<no-db>/<no-store># DESCRIBE ROLE rol1;
+----------+---------+------------+--------------------+---------------+
|  Type    |  Name   |  Privilege |  With Grant Option |  Granted By   |
+==========+=========+============+====================+===============+
| role     | public  | usage      | false              | orgadmin      |
+----------+---------+------------+--------------------+---------------+
| database | user_db | usage      | true               | securityadmin |
+----------+---------+------------+--------------------+---------------+
| schema   | public  | usage      | false              | securityadmin |
+----------+---------+------------+--------------------+---------------+
| database | user_db | create     | true               | securityadmin |
+----------+---------+------------+--------------------+---------------+
| schema   | public  | create     | false              | securityadmin |
+----------+---------+------------+--------------------+---------------+

<no-db>/<no-store># GRANT USAGE,CREATE ON SCHEMA accounting_db.public TO rol1 WITH GRANT OPTION;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage, create" on          |
|                 |          | "public" granted to "rol1"               |
+-----------------+----------+------------------------------------------+
<no-db>/<no-store># DESCRIBE ROLE rol1;
+----------+---------+------------+--------------------+---------------+
|  Type    |  Name   |  Privilege |  With Grant Option |  Granted By   |
+==========+=========+============+====================+===============+
| role     | public  | usage      | false              | orgadmin      |
+----------+---------+------------+--------------------+---------------+
| database | user_db | usage      | true               | securityadmin |
+----------+---------+------------+--------------------+---------------+
| schema   | public  | usage      | true               | securityadmin |
+----------+---------+------------+--------------------+---------------+
| database | user_db | create     | true               | securityadmin |
+----------+---------+------------+--------------------+---------------+
| schema   | public  | create     | true               | securityadmin |
+----------+---------+------------+--------------------+---------------+

Store Privileges

GRANT [
      USAGE 
      | ALL PRIVILEGES
      ] 
ON STORE store_name
TO ROLE role_name [, ...]
[WITH GRANT OPTION];

Description

Grants store privileges to one or more roles.

Arguments

USAGE

Allow role to list and use the store.

store_name

The name of the store on which to grant privileges.

role_name [, ...]

One or more roles to which to grant the privileges.

WITH GRANT OPTION

Grants privileges that allow the role to grant those same privileges to other roles.

Example

<no-db>/<no-store># GRANT USAGE ON STORE kafka_pub TO rol2;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage" on "kafka_pub"      |
|                 |          | granted to "rol2"                        |
+-----------------+----------+------------------------------------------+
<no-db>/<no-store># DESCRIBE ROLE rol2;
+--------------+-----------+-----------------+--------------------+---------------+
|  Type        |  Name     |  Privilege      |  With Grant Option |  Granted By   |
+==============+===========+=================+====================+===============+
| role         | public    | usage           | false              | orgadmin      |
+--------------+-----------+-----------------+--------------------+---------------+
| store        | kafka_pub | usage           | false              | securityadmin |
+--------------+-----------+-----------------+--------------------+---------------+

<no-db>/<no-store># GRANT USAGE ON STORE kafka_pub TO rol2 WITH GRANT OPTION;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage" on "kafka_pub"      |
|                 |          | granted to "rol2"                        |
+-----------------+----------+------------------------------------------+
<no-db>/<no-store># DESCRIBE ROLE rol2;
+--------------+-----------+-----------------+--------------------+---------------+
|  Type        |  Name     |  Privilege      |  With Grant Option |  Granted By   |
+==============+===========+=================+====================+===============+
| role         | public    | usage           | false              | orgadmin      |
+--------------+-----------+-----------------+--------------------+---------------+
| store        | kafka_pub | usage           | true               | securityadmin |
+--------------+-----------+-----------------+--------------------+---------------+

Descriptor Source Privileges

GRANT [
      USAGE 
      | ALL PRIVILEGES
      ]
ON DESCRIPTOR_SOURCE descriptor_source_name
TO ROLE role_name [, ...]
[WITH GRANT OPTION];

Description

Grants descriptor source privileges to one or more roles.

Arguments

USAGE

Allow role to list and use the descriptor source.

descriptor_source_name

The name of the descriptor source on which to grant privileges.

role_name [, ...]

One or more roles to which to grant the privileges.

WITH GRANT OPTION

Grants privileges that allow the role to grant those same privileges to other roles.

Example

demodb.public/demostore# GRANT USAGE ON DESCRIPTOR_SOURCE demosource TO rol1;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage" on "demosource"     |
|                 |          | granted to "rol1"                        |
+-----------------+----------+------------------------------------------+
demodb.public/demostore# DESCRIBE ROLE rol1;
+-------------------+--------------+------------+--------------------+---------------+
|  Type             |  Name        |  Privilege |  With Grant Option |  Granted By   |
+===================+==============+============+====================+===============+
| role              | public                  | usage      | false              | orgadmin      |
+-------------------+-------------------------+------------+--------------------+---------------+
| descriptor_source | demosource   | usage      | false              | securityadmin |
+-------------------+--------------+------------+--------------------+---------------+

Relation Privileges

GRANT [
      SELECT
      | INSERT
      | ALL PRIVILEGES
      ]
ON RELATION relation_name
TO ROLE role_name [, ...]
[WITH GRANT OPTION];

Description

Grants relation privileges to one or more roles.

Arguments

SELECT

Allow role to create a query and use the relation as a source.

INSERT

Allow role to create a query and use the relation as a sink.

relation_name

The name of the relation to grant privileges on. Optionally, provide database and schema name for a fully-qualified relation name in the format of [<database_name>.<schema_name>.]<relation_name> — for example, db1.public.pageviews. Otherwise, the system uses the current database and schema to identify the relation.

role_name [, ...]

One or more roles to which to grant the privileges.

WITH GRANT OPTION

Grants privileges that allow the role to grant those same privileges to other roles.

Example

demodb.public/demostore# GRANT SELECT ON RELATION demodb."public".pv TO rol1;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "select" on "pv" granted    |
|                 |          | to "rol1"                                |
+-----------------+----------+------------------------------------------+
demodb.public/demostore# DESCRIBE ROLE rol1;
+-------------------+-------------------------+------------+--------------------+---------------+
|  Type             |  Name                   |  Privilege |  With Grant Option |  Granted By   |
+===================+=========================+============+====================+===============+
| role              | public                  | usage      | false              | orgadmin      |
+-------------------+-------------------------+------------+--------------------+---------------+
| relation          | pv                      | select     | false              | securityadmin |
+-------------------+-------------------------+------------+--------------------+---------------+

demodb.public/demostore# GRANT INSERT ON RELATION demodb."public".pageviews TO rol1;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "insert" on "pv" granted    |
|                 |          | to "rol1"                                |
+-----------------+----------+------------------------------------------+
demodb.public/demostore# DESCRIBE ROLE rol1;
+-------------------+-------------------------+------------+--------------------+---------------+
|  Type             |  Name                   |  Privilege |  With Grant Option |  Granted By   |
+===================+=========================+============+====================+===============+
| role              | public                  | usage      | false              | orgadmin      |
+-------------------+-------------------------+------------+--------------------+---------------+
| relation          | pv                      | insert     | false              | securityadmin |
+-------------------+-------------------------+------------+--------------------+---------------+

Function Source Privileges

GRANT [
      USAGE 
      | ALL PRIVILEGES
      ]
ON FUNCTION_SOURCE function_source_name
TO ROLE role_name [, ...]
[WITH GRANT OPTION];

Description

Grants function source privileges to one or more roles.

Arguments

USAGE

Allow role to list and use the runction source.

function_source_name

The name of the function source on which to grant privileges.

role_name [, ...]

One or more roles to which to grant the privileges.

WITH GRANT OPTION

Grants privileges that allow the role to grant those same privileges to other roles.

Example

demodb.public/demostore# GRANT USAGE ON FUNCTION_SOURCE demofnsrc TO rol1;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage" on "demofnsrc"      |
|                 |          | granted to "rol1"                        |
+-----------------+----------+------------------------------------------+
demodb.public/demostore# DESCRIBE ROLE rol1;
+-------------------+-----------+------------+--------------------+---------------+
|  Type             |  Name     |  Privilege |  With Grant Option |  Granted By   |
+===================+===========+============+====================+===============+
| role              | public    | usage      | false              | orgadmin      |
+-------------------+-----------+------------+--------------------+---------------+
| function_source   | demofnsrc | usage      | false              | sysadmin      |
+-------------------+-----------+------------+--------------------+---------------+

Function Privileges

GRANT [
      USAGE 
      | ALL PRIVILEGES
      ]
ON FUNCTION function_identifier
TO ROLE role_name [, ...]
[WITH GRANT OPTION];

Description

Grants function privileges to one or more roles.

Arguments

USAGE

Allow role to list and use the function.

function_identifier

The name of the function on which to grant privileges.

role_name [, ...]

One or more roles to which togrant the privileges.

WITH GRANT OPTION

Grants privileges that allow the role to grant those same privileges to other roles.

Example

demodb.public/demostore# LIST FUNCTIONS;
+----------------------------+-------+--------------+------------------+--------------------+----------+-------------+-------------------------------+-------------------------------+
|  Signature                 |  Type |  Source Name |  Class Name      |  Egress Allow URIs |  Owner   |  Properties |  Created At                   |  Updated At                   |
+============================+=======+==============+==================+====================+==========+=============+===============================+===============================+
| upper(a VARCHAR) VARCHAR   | udf   | my_src       | demo.DSUpperCase |                    | sysadmin | {}          | 2024-06-06 03:35:52 +0000 UTC | 2024-06-06 03:35:52 +0000 UTC |
+----------------------------+-------+--------------+------------------+--------------------+----------+-------------+-------------------------------+-------------------------------+
demodb.public/demostore# GRANT USAGE ON FUNCTION upper(a varchar) varchar TO rol1;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage" on "upper"          |
|                 |          | granted to "rol1"                        |
+-----------------+----------+------------------------------------------+
demodb.public/demostore# DESCRIBE ROLE rol1;
+-------------------+------------+------------+--------------------+---------------+
|  Type             |  Name      |  Privilege |  With Grant Option |  Granted By   |
+===================+============+============+====================+===============+
| role              | public     | usage      | false              | orgadmin      |
+-------------------+------------+------------+--------------------+---------------+
| function_source   | demofnsrc  | usage      | false              | sysadmin      |
+-------------------+------------+------------+--------------------+---------------+
| function          | my_func    | usage      | false              | sysadmin      |
+-------------------+------------+------------+--------------------+---------------+

Region Privileges

GRANT [
      USAGE 
      | ALL PRIVILEGES
      ]
ON REGION region_name
TO ROLE role_name [, ...]
[WITH GRANT OPTION];

Description

Grants region usage privileges to one or more roles.

By default, the public role is granted access to all regions. A role with the MANAGE_GRANTS privilege can grant the region USAGE privilege to other roles, or revoke it.

Arguments

USAGE

Allow role to list and use the region to create stores and launch queries.

region_name

The name of the region on which to grant privileges.

role_name [, ...]

One or more roles to which to grant the privileges.

WITH GRANT OPTION

Grants privileges that allow the role to grant those same privileges to other roles.

Example

<no-db>/<no-store># DESCRIBE ROLE "public";
+--------------+----------------+------------+--------------------+---------------+
|  Type        |  Name          |  Privilege |  With Grant Option |  Granted By   |
+==============+================+============+====================+===============+
| role         | public         | usage      | false              | orgadmin      |
+--------------+----------------+------------+--------------------+---------------+
| region       | AWS us-east-1  | usage      | false              | securityadmin |
+--------------+----------------+------------+--------------------+---------------+

<no-db>/<no-store># GRANT USAGE ON REGION "AWS us-east-1" TO rol1;
+-----------------+----------+------------------------------------------+
|  Type           |  Command |  Summary                                 |
+=================+==========+==========================================+
| privilege grant | ALTER    | Privilege(s) "usage" on "AWS us-east-1"  |
|                 |          | granted to "rol1"                        |
+-----------------+----------+------------------------------------------+
<no-db>/<no-store># DESCRIBE ROLE rol1;
+-------------------+----------------+------------+--------------------+---------------+
|  Type             |  Name          |  Privilege |  With Grant Option |  Granted By   |
+===================+================+============+====================+===============+
| role              | public         | usage      | false              | orgadmin      |
+-------------------+----------------+------------+--------------------+---------------+
| region            | AWS us-east-1  | usage      | false              | securityadmin |
+-------------------+----------------+------------+--------------------+---------------+

PreviousGRANT OWNERSHIP NextGRANT ROLE

Last updated 2 months ago