GRANT PRIVILEGES

Grant 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[, 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 will also additional privileges on Database, Schema, Relations and Stores in order 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[, 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 CREATE_DATABASE, CREATE_STORE ON ORGANIZATION TO rol1, rol2;
<no-db>/<no-store># DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-04-24T18:55:03Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
       Type      |    Target    | ID/Name | Grant option  
-----------------+--------------+---------+---------------
  CreateDatabase | Organization | myorg   |               
  CreateStore    | Organization | myorg   |        
<no-db>/<no-store># GRANT CREATE_DATABASE, CREATE_STORE ON ORGANIZATION TO rol1, rol2 WITH GRANT OPTION;
<no-db>/<no-store># DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-04-24T18:55:03Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
       Type      |    Target    | ID/Name | Grant option  
-----------------+--------------+---------+---------------
  CreateDatabase | Organization | o1      | ✓             
  CreateStore    | Organization | o1      | ✓

Grant database privileges

GRANT [ USAGE 
      | CREATE
      | ALL PRIVILEGES
      ,...
      ]
ON DATABASE database_name
TO ROLE role_name[, 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 will also require additional privileges on Schema, and Relations in order 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[, 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 accounting_db TO rol1;
<no-db>/<no-store># DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-04-24T18:55:03Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
  Type  |  Target  |    ID/Name    | Grant option  
--------+----------+---------------+---------------
  Usage | Database | accounting_db |               
<no-db>/<no-store># GRANT USAGE,CREATE ON DATABASE accounting_db TO rol1 WITH GRANT OPTION;
<no-db>/<no-store># DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-04-24T18:55:03Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
   Type  |  Target  |    ID/Name    | Grant option  
---------+----------+---------------+---------------
  Usage  | Database | accounting_db | ✓             
  Create | Database | accounting_db | ✓

Grant Database Schema privileges

GRANT [ USAGE 
      | CREATE
      | ALL PRIVILEGES
      ]
ON SCHEMA [database_name.]schema_name
TO ROLE role_name[, 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 will also additional privileges on Relations in order to use them.

CREATE

Allow Role to create Relations under the Schema.

ALL PRIVILEGES

Grants all the Privileges listed above to the Role.

[database_name.]schema_name

The qualified name of the Schema to granted privileges on.

role_name[, 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,CREATE ON SCHEMA accounting_db.public TO rol1;
<no-db>/<no-store># DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-04-24T18:55:03Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
   Type  |  Target  |    ID/Name    | Grant option  
---------+----------+---------------+---------------
  Usage  | Database | accounting_db | ✓             
  Usage  | Schema   | public        |               
  Create | Database | accounting_db | ✓             
  Create | Schema   | public        |            
<no-db>/<no-store># GRANT USAGE,CREATE ON SCHEMA accounting_db.public TO rol1 WITH GRANT OPTION;
<no-db>/<no-store># DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-04-24T18:55:03Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
   Type  |  Target  |    ID/Name    | Grant option  
---------+----------+---------------+---------------
  Usage  | Database | accounting_db | ✓             
  Usage  | Schema   | public        | ✓             
  Create | Database | accounting_db | ✓             
  Create | Schema   | public        | ✓

Grant Store privileges

GRANT USAGE 
ON STORE store_name
TO ROLE role_name[, 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 to granted privileges on.

role_name[, 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 STORE kafka_store TO rol2;
<no-db>/<no-store># DESCRIBE ROLE rol2;
  Name |      Created at       
-------+-----------------------
  rol2 | 2023-04-24T18:55:05Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
  Type  | Target |   ID/Name   | Grant option  
--------+--------+-------------+---------------
  Usage | Store  | kafka_store |               
<no-db>/<no-store># GRANT USAGE ON STORE kafka_store TO rol2 WITH GRANT OPTION;
<no-db>/<no-store># DESCRIBE ROLE rol2;
  Name |      Created at       
-------+-----------------------
  rol2 | 2023-04-24T18:55:05Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
  Type  | Target |   ID/Name   | Grant option  
--------+--------+-------------+---------------
  Usage | Store  | kafka_store | ✓

Grant Descriptor Source privileges

GRANT USAGE
ON DESCRIPTOR_SOURCE descriptor_source_name
TO ROLE role_name[, 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 to granted privileges on.

role_name[, 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

demodb.public/demostore# GRANT USAGE ON DESCRIPTOR_SOURCE demosource TO rol1;

demodb.public/demostore# DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-09-10T21:01:04Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
  Type  |      Target      |  ID/Name   | Grant option  
--------+------------------+------------+---------------
  Usage | DescriptorSource | demosource |

Grant Relation privileges

GRANT [ SELECT
      | INSERT
      | ALL PRIVILEGES
      ]
ON RELATION [[database_name.]schema_name.]relation_name
TO ROLE role_name[, 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.

[[database_name.]schema_name.]relation_name

The name of the Relation to granted privileges on. Optionally provide Database and Schema name to fully qualified relation name.

role_name[, 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

demodb.public/demostore# GRANT SELECT ON RELATION demodb."public".pageviews TO rol1;
demodb.public/demostore# DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-09-10T21:01:04Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
   Type  |  Target  |  ID/Name  | Grant option  
---------+----------+-----------+---------------
  Select | Relation | pageviews |               
demodb.public/demostore# GRANT INSERT ON RELATION demodb."public".pageviews TO rol1;
demodb.public/demostore# DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-09-10T21:01:04Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
   Type  |  Target  |  ID/Name  | Grant option  
---------+----------+-----------+---------------
  Insert | Relation | pageviews |

Grant Function Source privileges

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

Description

Grants Function Source privileges to one or more roles.

Arguments

USAGE

Allow Role to list, and use the Function Source.

descriptor_source_name

The name of the Function Source to granted privileges on.

role_name[, 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

demodb.public/demostore# GRANT USAGE ON FUNCTION_SOURCE demofnsrc TO rol1;
demodb.public/demostore# DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-09-10T21:01:04Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
  Type  |     Target     |  ID/Name  | Grant option  
--------+----------------+-----------+---------------
  Usage | FunctionSource | demofnsrc |

Grant Function privileges

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

Description

Grants Function privileges to one or more roles.

Arguments

USAGE

Allow Role to list, and use the Function.

descriptor_source_name

The name of the Function to grant privileges on.

role_name[, 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

demodb.public/demostore# LIST FUNCTIONS;
   Source   |      Class       |        Signature         |  Owner   |      Created at      |      Updated at       
------------+------------------+--------------------------+----------+----------------------+-----------------------
  demofnsrc | demo.DSUpperCase | upper(a varchar) varchar | sysadmin | 2023-09-11T16:42:02Z | 2023-09-11T16:42:02Z  
  
demodb.public/demostore# GRANT USAGE ON FUNCTION upper(a varchar) varchar TO rol1;

demodb.public/demostore# DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-09-10T21:01:04Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
  Type  |     Target     |  ID/Name  | Grant option  
--------+----------------+-----------+---------------
  Usage | FunctionSource | demofnsrc |               
  Usage | Function       | upper     |

Grant Region privileges

GRANT USAGE
ON REGION region_name
TO ROLE role_name[, 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 the regions. A Role with the MANAGER_GRANTS privilege can grant or revoke the Region USAGE privilege from other roles.

Arguments

USAGE

Allow Role to list, and use the Region to create Stores and launch Queries.

region_name

The name of the Region to granted privileges on.

role_name[, 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># DESCRIBE ROLE public;
   Name  |      Created at       
---------+-----------------------
  public | 2023-09-11T18:07:43Z  

Granted Roles
   Name   
----------
  public  

Granted Privileges
  Type  |    Target    |     ID/Name     | Grant option  
--------+--------------+-----------------+---------------
  Usage | Organization |                 |               
  Usage | Region       | AWS us-east-1   |               

<no-db>/<no-store># GRANT USAGE ON REGION "AWS us-east-1" TO rol1;

<no-db>/<no-store># DESCRIBE ROLE rol1;
  Name |      Created at       
-------+-----------------------
  rol1 | 2023-09-10T21:01:04Z  

Granted Roles
   Name   
----------
  public

Granted Privileges
  Type  |    Target    |     ID/Name     | Grant option  
--------+--------------+-----------------+---------------
  Usage | Region       | AWS us-east-1   |
PreviousGRANT OWNERSHIPNextGRANT ROLE

Last updated