Grant Role_Snowflake

Skip to Main Content

• Home
• About Me

Search for:

A Comprehensive Tutorial of Snowflake Privileges and Access Control

Intro

When I first started working with Snowflake, I was trying to figure out what was possible — what I could do. I started out setting up tables, then views, procedures, functions, pipes, stages, integrations, etc. Figuring out the feature set was most pressing for me, and privileges were a second thought.

Whenever I had to figure out the permissions to do something, I’d grant myself the permissions and away I’d go. This worked well for me and the few other people who were helping me incubate and test the product, but as we got closer to rolling out across our entire organization, I was uncomfortable with how messy our permissions had gotten.

So over Christmas vacation (Merry Christmas everybody!) I did a deep dive into Snowflake access control. One of the most difficult things was documenting which privilege allowed me to execute which statements.

This exercise has been really helpful for me to understand how I want to set up Snowflake permissions for my organization. If you want to skip to that, see how I set up Snowflake privileges.

This post is the result of my working through privileges, roles and permissions in Snowflake. I hope it’s helpful for you!

Worksheet Session Context

Whenever you run a command in Snowflake, it runs in a session context. You can easily see your context in the Snowflake UI:

This context is unique to the worksheet you’re in and every worksheet can have a different context. To understand Snowflake access control, it’s important for you to understand that this context exists and what it consists of.

The context applies to the worksheet you’re working in (or the connection you’ve made in a third-party app) and consists of*:

1. A role
2. A warehouse
3. A database
4. A schema

Hopefully you’ve played around in Snowflake a bit or have some familiarity with this concept from other databases.

*Note: There are some commands you can run without specifying a database or schema or even a warehouse. Commands like CREATE WAREHOUSE don’t need a database or a schema set. But I’m trying to keep things simple to the general use case and generally for working in Snowflake, all four of these need to be set.

Scoped Privileges in Snowflake

Snowflake controls users’ access to database objects through assignment of privileges to roles, and assignment of roles to users. A privilege is something you can do, and it’s always applied to a specific object where you can do it (scope). Here are some examples:

Privilege (i.e. what you can do)Scope (i.e. where you can do it)Alter ViewA view called Log_Events in the Log schema in the Log databaseCreate DatabaseThe Snowflake AccountCreate SchemaA database called Sales_DBSelectA table called Log_Events

I call the combination of these a scoped privilege. I don’t think anybody else uses that term, but it helps to think of it as one thing so I gave it a name. What you can do is a privilege. Where you can do it is the scope. A scoped privilege is one term for the both of them.

You can see more about scoped privileges in my Mega-listing of Snowflake Access Control Privileges. Or if you prefer the snowflake documentation, you can use that as well.

Grants in Snowflake

A GRANT is the assignment of a scoped privilege to a specific role. Below are some examples of grants. See if you can pick out what is the scoped privilege, and what is the role.

grant USAGE on WAREHOUSE XS_WH to role ANALYST;
grant USAGE on DATABASE DEMO_DB to role ANALYST;
grant USAGE on SCHEMA DEMO_DB.DEMO_SCHEMA to role ANALYST;
grant SELECT on TABLE DEMO_DB.DEMO_SCHEMA.LOG_TABLE to role ANALYST;

As you can see in the last line of the example above, the grant of what you can do (i.e. SELECT), and the scope of where you can do it (i.e. Log_Table) is assigned to a role ANALYST. Incidentally, these four statements together constitute the smallest configuration of a role that allows you to select from an existing table, which highlights a key principle:

Key Principle #1: To interact with a schema-level object like a table, you usually need to be operating with a role that has (at a minimum) four scoped privileges:

1. USAGE on the object’s parent database
2. USAGE on the object’s parent schema
3. USAGE on a warehouse
4. A privilege on the object

You need USAGE privilege on a database to run this command:

• use database demo_db;

You also need USAGE on a database to switch to that database in your context:

Here’s a little diagram to make our definitions thus far a little clearer:

There are two really useful statements you can use to investigate scoped privileges on a role:

• show grants to role Demo_Dev_Role;
• Answers the question: What can this role do? What privileges does it have on which objects?
• This lists all the scoped privileges granted to this role
• There is a column called “grant_option”. If this is set to true, it allows the recipient role to grant the privilege to other roles.
• show grants of role Demo_Dev_Role;
• Answers the question: Who is assigned to this role?
• This shows that we’ve granted the READER_DEMO_DB role to Demo_DB_User;

A role is an object just like any other. If you want to know who has ownership or usage on the role, you can run show grants on role Demo_Dev_Role; To see more on “show grants” see the Snowflake documentation: https://docs.snowflake.net/manuals/sql-reference/sql/show-grants.html

Roles in Snowflake

With an understanding of scoped privileges and grants, we are ready to look at roles. You already know a little bit about them.

As you’ve seen, we can make many grants to a role so that people operating with that role can perform the actions allowed to them by the grants. In this way, a role makes up the sum of what a user can do when operating under that role. If we created a custom role called ANALYST

create role ANALYST;

and granted that role some privileges:

grant USAGE on WAREHOUSE XS_WH to role ANALYST;
grant USAGE on DATABASE DEMO_DB to role ANALYST;
grant USAGE on SCHEMA DEMO_DB.DEMO_SCHEMA to role ANALYST;
grant SELECT on TABLE DEMO_DB.DEMO_SCHEMA.LOG_TABLE to role ANALYST;

and assigned a user to the ANALYST role

grant role ANALYST to user TREVOR_HIGBEE;

and if that user’s context was set to operate under the ANALYST role, they could select from any table in the DEMO_DB database using the XS_WH warehouse.

Users are assigned to one or many roles. Users can switch between roles to act with the privileges that are granted to that role. You (i.e. your user account) can be assigned many roles, but you can only be operating with one roll at a time.

Key Principle #2: Users can be assigned many roles, but may only operate under one role at a time and are fully enabled or restricted by the privileges in that role.

For example, let’s say I’m logged in as Demo_DB_User. This user is assigned to two different roles:

• OWNER_DEMO_DB – Has privileges that allow it to create schemas in the DEMO_DB database.
• READER_DEMO_DB – Does not have privileges that allow it to create schemas.

When the role DEMO_DB_USER is operating under is OWNER_DEMO_DB, I can create schemas in the database:

However, if I’m the DEMO_DB_USER and I switch my role to READER_DEMO_DB:

Then I can’t do it:

I’m still logged in as the same user. I’m just operating in the worksheet context (i.e. session) with a different role. The role you’ve chosen determines the sum of what you can do and where you can do it when operating under that role.

Available Privileges Vary by Object Type

Remember, a privilege describes what you, as a member of a role, can do. The scope of a privilege describes where you can do it.

It wouldn’t make sense, therefore, to do the following:

grant SELECT on PROCEDURE MY_PROCEDURE to role ANALYST;

You might execute a procedure. Or drop it or rename it. But you wouldn’t select it. Similarly, it wouldn’t make sense to do the following:

grant EXECUTE on TABLE MY_LOG to role ANALYST;

What would it mean to execute a table? Again, that doesn’t make sense.

So each object type has certain privileges that allow you to perform certain actions specific to that object type. For example, here are the available privileges on TABLE:

SELECT
INSERT
UPDATE
TRUNCATE
DELETE
REFERENCES
OWNERSHIP

Here are the privileges on PROCEDURE:

USAGE
OWNERSHIP

Privileges on an object don’t always line up one-to-one with an action you want to take on the object. For example, there are only two privileges on PROCEDURE (usage and ownership), but there are many actions we might want to perform on a procedure:

• Create a new procedure
• Rename a procedure
• Alter procedure text
• Call a procedure

Figuring out which permissions are required to perform which actions can be challenging. Here are the privileges needed to perform various actions on a PROCEDURE:

PrivilegeAction PerformedOWNERSHIP on procedurealter procedure if exists procedure1(float) rename to procedure2;CREATE PROCEDURE on schema
CREATE OR REPLACE PROCEDURE DEMO_PROCEDURE()
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
var x = 1.01;
return x;
$$;Any Permission on proceduredescribe procedure my_procedure();Any Permission on procedureselect get_ddl('procedure', 'my_procedure()')USAGE on procedurecall my_procedure();Any Permission on procedureshow procedures;

Notice that one of the privileges we need to CREATE a PROCEDURE is actually a privilege on the schema object.

If you want to dive into this a bit more, I’ve organized account privileges to actions in my Mega-listing of Snowflake Access Control Privileges. You can also take a look at Snowflake’s documentation, which tells you which privileges are associated with which objects, but does not tell you exactly what you can do with each privilege grant.

All Privileges, All Objects, Future Objects

If we wanted to give the ANALYST role SELECT privileges on a specific table, we could grant them this privilege by doing the following just like we’ve already seen in this tutorial:

grant SELECT on TABLE DEMO_DB.DEMO_SCHEMA.LOG_TABLE to role ANALYST;

This is completely valid. But it only grants a single privilege on a single already-existing object. What if we wanted to grant all privileges on all tables that exist now and in the future? To do that we’d run this:

GRANT SELECT ON ALL TABLES IN DATABASE demo_db TO ROLE analyst;
GRANT SELECT ON FUTURE TABLES IN DATABASE demo_db TO ROLE analyst;

We could go further and grant all privileges on all current and future tables, like this:

GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE demo_db TO ROLE analyst;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE demo_db TO ROLE analyst;

However, you cannot grant all privileges on ALL OBJECTS at once. You have to do it object-type by object-type:

--'ALL OBJECTS' DOESN'T WORK:
GRANT ALL PRIVILEGES ON ALL OBJECTS IN DATABASE demo_db TO ROLE analyst;

Future grants can be created on objects at either the database-level or schema-level.

GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE demo_db TO ROLE analyst;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA demo_db.demo_schema TO ROLE analyst;

Have a plan when applying these b/c placing a future grant at the schema level makes it so that the future grant at the database level (for the same object type) doesn’t fire. I’ve made this mistake!

The Ownership Privilege in Snowflake

Every object in Snowflake has an owner. Or in other words, for each object in the database, a single role is granted the ownership privilege. That role has full control (i.e. all privileges) over the object. The owner is always a role, not a user. By default the owner is whatever role was used to create the object. If I run the following:

--role owner_demo_db is the owner of the demo_db database
use role owner_demo_db;
create schema demo_schema; --owner of a db has full control and can create schemas

And then run:

show schemas;

we can see that the OWNER_DEMO_DB role is the owner of the schema:

This fact is actually very convenient. Because once someone is an owner of a database, they can create schemas and objects inside that database all belonging to the same owning role. And they can assign others who need full control over the database or objects within it to the role.

SIDE NOTE: INFORMATION_SCHEMA always has a blank owner – it’s completely owned and managed by Snowflake.

The ownership role has all the privileges available for an object and can operate on the table without restrictions. But the owner role also has one extra ability that you don’t have even if you have all these permissions. Members of the owner role can grant any of these roles to any other user or role.

Key Principle #3

When you create an object, the role you used to create it has the OWNERSHIP privilege on the object. Users don’t own objects. Roles own objects.

If you have the OWNERSHIP privilege on an object, you have full control over the object.

OWNERSHIP on an object allows users to grant others privileges on that object

What is the difference between ownership and all privileges?

There are two privileges applicable to almost every object in a database: OWNERSHIP and ALL PRIVILEGES. They’re done like this:

ALL PRIVILEGES is kind of a pseudo-privilege. Meaning that if you grant ALL PRIVILEGES to a role in a single grant statement, Snowflake actually grants each available privilege on that object (except OWNERSHIP) to the role.

use role owner_demo_db;
create table app_vars (key_val varchar, value_val varchar);

Owner owns the schema so has all privileges on the table. This means that when operating under this role, we have the CREATE TABLE schema privilege. You can see that’s one of the privileges in the list of schema privileges in the snowflake documentation.

grant ALL PRIVILEGES on table app_vars to role reader_demo_db;

Because we are the owner, we can grant any privilege on this object to any user.

When we ask snowflake to show grants to the reader role, we see that all privileges available on the table object have been added to the app_vars table:

show grants to role reader_demo_db;

-- Because we own the table, we can also revoke one of the privileges.
REVOKE TRUNCATE on table app_vars from role reader_demo_db;

And you can see that the TRUNCATE privilege has been removed.

In contrast, if you look at the grants on the owner role, we don’t see the individual roles specified. All we see is that the owner role has OWNERSHIP on the APP_VARS table.

This is one difference between OWNERSHIP and ALL PRIVILEGES. The other difference, as we’ve already seen, is that the owner can grant anyone permissions on the table – even transfer ownership to another role. You can see the grant_options when we assigned ALL PRIVILEGES is set to false. But the grant_option on the ownership privilege is true.

The Snowflake Object and Role Hierarchies:

There are two hierarchies that are important to understand when designing user access scheme in Snowflake:

• Object Hierarchy
• Role Hierarchy

Here is the Object Hierarchy (see also Snowflake docs on this):

• Account
• Users
• Roles
• Warehouses
• Resource Monitors
• Integrations
• Databases
• Schemas
• Tables, Views, Procedures, Tasks, Pipes, Streams, Sequence,etc.

Here is the default Role Hierarchy:

• AccountAdmin
• SecurityAdmin
• SysAdmin
• Custom owner roles

AccountAdmin is in charge of account-level objects and settings. SecurityAdmin is in charge of managing users and roles. Sysadmin is in charge of owning all database objects.

To see what privileges each role actually has (on your own), you can run the following for each role:

--We filter where granted by is blank to see what the defaults were when Snowflake was first set up.
show grants to role sysadmin;
select * from table(result_scan(last_query_id())) t
where "granted_by" = '';

Roles Don’t Automatically Cascade

Even though objects are organized in a hierarchy, privileges on those objects don’t automatically cascade down. So just because you are the owner of a database, that doesn’t mean you are the owner of all the objects in the database. It could be that two roles have the ability to create objects in the same database. One role is the owner, and that role gave the CREATE TABLE privilege to a different role. If that’s the case, you could have objects owned by different roles, which can be messy. Let’s look at an example of this:

Create Role_A and Role_B. Role_A owns a schema. According to the documentation, one of the privileges on the schema is CREATE TABLE. Because Role_A owns the schema, it can grant the CREATE TABLE permission on the schema to Role_B. Role_A goes to town and creates a bunch of tables. Role_B goes to town and creates a bunch of tables. But when they want to work together, they find they can’t see each others tables!

use role accountadmin;
​
--Create a new database
create or replace database demo_db;
​
--Create Role_A and grant to it ownership of the new db
create or replace role Role_A;
grant usage on warehouse xs_wh to Role_A;
grant ownership on database demo_db to Role_A;
grant role Role_A to role sysadmin;
​
--As the database's owner, create a new schema
use role Role_A;
create schema demo_db.demo_schema;
​
use role accountadmin;
​
--Create Role_B and give it CREATE TABLE on the new schema
create or replace role Role_B;
grant usage on warehouse xs_wh to Role_B;
grant usage on database demo_db to Role_B;
grant usage, create table on schema demo_db.demo_schema to Role_B;
​
--Grant me Role_A and Role_B
grant role Role_A to user trevor_higbee;
grant role Role_B to user trevor_higbee;
​
--Use Role_A and Role_B to create two tables
use role Role_A;
create table demo_schema.table_a (val number);
​
use role Role_B;
create table demo_schema.table_b(val number);

Now if we use the ACCOUNTADMIN role to SHOW TABLES, we’ll see that even though the database is owned by Role_A, Table_B in that database is owned by Role_B.

In fact, if I run SHOW TABLES as the database- and schema-owning Role_A, I only see one table:

If I do the same thing with Role_B, I only see one table:

The owner of the database doesn’t necessarily own (and maybe can’t even see!) all the objects in the database they own. There are a few ways to resolve this. One way is to combine both roles in a new master role called Role_AB and assign our users to Role_AB. If they’re assigned to Role_AB, they will inherit ownership of each of the roles. This is called role composition – taking two roles and composing a single role from them.

use role accountadmin;
create role Role_AB;
grant Role_A to Role_AB;
grant Role_B to Role_AB;

Another way to solve the problem is to grant Role_B to Role_A and then assign both users to Role_A. This is called role inheritance:

use role accountadmin;
grant role Role_B to role Role_A;

Snowflake recommends role inheritance over role composition.

For more interesting reading on this topic, see this Snowflake Community post: SNOWFLAKE RBAC SECURITY PREFERS ROLE INHERITANCE TO ROLE COMPOSITION

Managing the SysAdmin Role

Wait. If our custom role owns the database, and if there’s only one owner of each object, then SysAdmin doesn’t own the database! This seems like a problem since SysAdmin is supposed to be the administrator for all non-security objects in the account. The way we solve this by role inheritance. Good thing you just learned about this! We grant ownership to each custom owner role, but then grant the custom role to SysAdmin. That way, SysAdmin isn’t directly the owner of any of the databases, but receives ownership because of the grant. Here is an example:

USE ROLE ACCOUNTADMIN;
CREATE DATABASE IF NOT EXISTS DEMO_DB; --CREATE DB
CREATE ROLE IF NOT EXISTS OWNER_DEMO_DB; --CREATE OWNER ROLE
GRANT OWNERSHIP ON DATABASE DEMO_DB TO ROLE OWNER_DEMO_DB REVOKE CURRENT GRANTS; --GRANT DB OWNERSHIP TO NEW ROLE
GRANT ROLE OWNER_DEMO_DB TO ROLE SYSADMIN; --GRANT THIS ROLE TO SYSADMIN

Essentially, the accountadmin role creates the database, but then transfers ownership of the database to the OWNER_DEMO_DB role. SysAdmin then has no access to it, and AccountAdmin is no longer the owner! However, AccountAdmin has the Manage Grants privilege that it inherits from the SecurityAdmin role. The Manage Grants privilege grants the ability to grant or revoke privileges on any object as if the invoking role (AccountAdmin) were the owner of the object.

In the last line, AccountAdmin uses role inheritance to grant the owner role to the SysAdmin role. Now Sysadmin can act as the owner. As other databases are created, and other owner roles are created, you need to explicitly grant these roles to SysAdmin or else SysAdmin will not be able to manage them.

A Standard Best-Practice Script for Implementing Access Control in Snowflake

If you don’t have an all-encompassing standard strategy for managing access control in Snowflake you are in for a world of hurt. If you grant ad-hoc privileges as people need them, you and your users will be really dissatisfied. You’ll have users using the wrong roles to create objects and they’ll be confused why they don’t have any visibility into those objects. Or they’ll create objects in one roll, swith over to another role and not be able to see them.

You can get in front of this by having a standard script for all your databases. Here is the script I currently prefer for setting up privileges on databases in Snowflake. It has the following features:

Roles:

• ACCOUNT_MONITOR – Has privileges that allow it to view account-level stats and usage
• OWNER_[DB_NAME] – Owns the database and all objects in the database.
• READER_[DB_NAME] – Can view all objects in the database

One of the really important things to me is that all users (including readers) can see all objects and all object definitions in the database. This lets us have less documentation and can empower users to answer their own questions when something doesn’t look right in the data.