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:
Execute a SELECT statement on the table
Execute an INSERT command on the table
Execute an UPDATE command on the table
Execute a TRUNCATE command on the table
Execute a DELETE command on the table
Reference the table as the unique/primary key table for a foreign key constraint
|ALL [ PRIVILEGES ]|
Grant all privileges, except OWNERSHIP, on the table
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)