25 October 2018        Add to Favorites   Report

Grant access to database objects in a schema to a Role in Snowflake

Snowflake uses Roles to manage user access provisioning.  You create a role with a set of accesses on a particular Table / Schema / Database. Then you assign that ROLE to a USER. 

You can grant the USAGE access to Warehouse / Database / Schema.

Grant usage on the database:

GRANT USAGE ON DATABASE <database> TO ROLE <role>;

Grant usage on the schema:

GRANT USAGE ON SCHEMA <database>.<schema> TO ROLE <role>;

Grant the ability to query an existing table:

GRANT SELECT ON TABLE <database>.<schema>.<table> TO ROLE <role>;

The following table privileges are supported:

Privilege

Usage

SELECT 

Execute a SELECT statement on the table

INSERT 

Execute an INSERT command on the table

UPDATE 

Execute an UPDATE command on the table

TRUNCATE 

Execute a TRUNCATE command on the table

DELETE 

Execute a DELETE command on the table

REFERENCES 

Reference the table as the unique/primary key table for a foreign key constraint

ALL [ PRIVILEGES ]

 Grant all privileges, except OWNERSHIP, on the table

OWNERSHIP 

Grant full control over a table.

NOTE: You will need to provide the Schema / Database level grants again whenever you create a new table. (It is not auto-refreshed)

Copied