Basic Access Control Privileges in Snowflake

«»Why can’t I see this table…?»»

— Frustrated Snowflake User

A key feature of the Snowflake platform is access control. Access control allows Snowflake account-holders to determine which users are allowed to do what.

When learning to use Snowflake, you will probably be using the accountadmin role. By default, these roles have the broadest privileges in the account. They can create any object they want. Also, because of how Snowflake works, they will have all privileges on all objects they’ve created. This behavior can make it difficult to understand and learn how to control and grant access on new objects to users.

Working as accountadmin is like living in an apartment you own. Obviously, you can open the fridge and pour yourself a glass of milk because you own the fridge, the milk, the glass, and certainly the kitchen. But what if you have a guest? What if they also want a glass of milk? In real life, it’s pretty simple: you can tell your guest that, sure, they can grab some milk from the fridge. In the digital world, however, access privileges are granular, and software avoids making potentially dangerous assumptions.

This blog post aims to be a simple introduction to access control in Snowflake, hoping to help a beginner in Snowflake learn the ropes.

How to think about access control in snowflake

Several models of access control exist. Snowflake uses a combination of two: Discretionary Access Control and Role-Based Access Control .

Discretionary Access Control (DAC) defines an owner for every object. The owner can in turn grant access to the object in question. (Discretionary – based on one’s individual choice or judgement.)

e.g. You bought a TV and can do whatever you want with it. You could even sell it and give it away, thus transferring ownership of it to another person.

Role-Based Access Control (RBAC) is a method of access control that grants privileges to roles, which are in turn granted to any number of users.

e.g. You are hired as a janitor. The company that hired you gives all janitors a set of keys that lets them access all supply closets and all trash disposal rooms.

Granting access to a table

Let’s run a simulation. Pretend your colleague Guillermo wants to look at a specific table that is managed by your Snowflake account.

In this context, maybe it’s best to think of this situation as a series of locks. Tables are housed in schemas, which are in turn housed in specific databases. Imagine the database as a locked room, inside of which are more locked doors that each lead into a particular schema. Inside theses schemas are tables, each contained within a locked box. To access the lockboxes, you will need the key that opens the database door, the key that opens the schema door, and the key to the specific lockbox.

Back to Guillermo. To give him access to the table, we need to give him a set of keys (access privileges). He definitely needs one for the table, but also to the schema and database that contain them. Finally, if he wants to be able to query the table, we also need to make sure he can use a warehouse to compute the query.

However, we also need to rememberthat privileges are granted to roles, not to users. Guillermo is a «Data Analyst» at your company, a role that has already been implemented in Snowflake.

We can use the following commands to grant Guillermo’s role access to the table:

GRANT usage ON DATABASE database_1 TO ROLE data_analyst;
GRANT usage ON SCHEMA my_schema TO ROLE data_analyst;
GRANT select ON TABLE data_table TO ROLE data_analyst;
GRANT usage ON WAREHOUSE compute_wh TO ROLE data_analyst;

Basic Privileges – Quick Reference

Useful Documentation for Access Control

• GRANT <privileges> | Snowflake Documentation
• Overview of Access Control | Snowflake Documentation
• Access Control Privileges | Snowflake Documentation