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)
Mike-Barn
posted on 25 Oct 18Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
Post Comment