Introducing Snowflake Role Based Access Control

What is RBAC?

Role based Access Control (RBAC) is the method used by Snowflake to secure access to data and machine resources. Extensive experience has demonstrated; however, it is often one of the hardest tasks for a Snowflake administrator. Unlike almost every other Snowflake feature which are simple to deploy and operate, RBAC provides a toolkit of micro-components that can be combined in an almost infinite number of ways which gives you about a million ways to get it horribly wrong.

This article introduces some of the concepts you need to understand and provides some good advice on how to deploy role-based access control on Snowflake.

It is part of a series of three articles including:

This article, an introduction to Role Based Access Control
Snowflake System Defined Roles: Best Practices which describes Best Practices for RBAC design.
Designing Snowflake Role Based Access Solutions which describes how to architect an RBAC solution.

Users, Roles, Objects and Grants

In principle, RBAC is remarkably simple, and the diagram below illustrates the key components, including Users, Roles and Objects. Effectively access to Objects are granted to Roles which in turn are granted to Users.

The diagram above shows the main components of role-based access which include:

1. Users: Who need access to Database Objects including Databases, Schemas, Tables and Virtual Warehouses. Unlike on-premise database solutions, it’s important to secure access to virtual warehouses in addition to data. This could be used, for example, to avoid running short running queries on massive virtual warehouses which is not the most cost-effective use of resources.

2. Roles: Users are granted Roles which are in turn used to group users together and control which users have access to database objects.

3. Objects: Which include the Databases, Tables and Virtual Warehouses which need to be secured.

The following SQL shows the commands needed to deploy these grants.

-- Switch to role SYSADMIN to manage database objects
use role sysadmin;

grant usage
   on database prod_db
   to role prod_support;

grant usage 
   on schema prod_db.main
   to role prod_support;

grant select
   on all tables
   in schema prod_db.main
   to role prod_support;

grant usage
   on warehouse prod_support_vwh
   to role prod_support;

-- Switch to role SECURITYADMIN to manage users and roles   
use role securityadmin;

grant role prod_support
   to user leon;

In the above example, the user Leon has been granted the role PROD Support which in turn is granted:

Usage: On the PROD_DB database and schema. You need to be granted usage on both the database and schema in addition to the tables. This provides no access to the underlying tables but is a minimum requirement to access data.
Select: On all the tables in the schema.
Usage: Of a Virtual Warehouse, which provides the ability to execute queries. Without access to a virtual warehouse, the user cannot run queries.

Switching Roles

One of the single most confusing features of Snowflake occurs when users switch from one role to another. By default, when a user connects to Snowflake, they are assigned an initial role. If no user defined default is set, the system defaults to the PUBLIC role, which has access to nothing.

The following SQL shows the command to switch to another role.

use role UAT_SUPPORT;

The diagram below shows the initial state whereby the user has the PROD_SUPPORT role and can therefore view tables in the PROD_DB database. Notice, that although the user has been assigned both PROD and UAT access, they only have access to the CURRENT role.

The diagram below illustrates what happens when the user switches to the UAT_SUPPORT_ROLE and they no longer have access to PROD_SUPPORT.

The above situation can lead to confusion and leads to a best practice, to ensure most users have a single role to perform their daily tasks. The SQL script below can be used to set the default values for a user including the role, warehouse and schema.

alter user jryan set
    default_role      = UAT_SUPPORT
    default_warehouse = UAT_ADHOC
    default_namespace = UAT_DB.MAIN;

Of course, if only one role is active at a time, this leads to a question, how do you provide access to multiple schemas or databases at the same time? The answer is to make use of role inheritance.

Snowflake Role Inheritance

The diagram below illustrates a situation whereby a user is granted the role PLATFORM_SUPPORT which in turn has been granted the PROD_SUPPORT and UAT_SUPPORT roles.

Using this method, the user can access both databases at the same time as the PLATFORM_SUPPORT role inherits all of the underlying access and means the user could copy data from the PROD_DB to the UAT_DB databases.

The SQL needed to grant access from one role to another is shown below.

grant role PROD_SUPPORT to role PLATFORM_SUPPORT;
grant role UAT_SUPPORT  to role PLATFORM_SUPPORT;

Object Ownership and Control

So far, we have been concerned with granting access to roles and database objects. However, the Snowflake administrator will need to control who has access. This is achieved using ownership.

Firstly, it’s worth noting a few points about ownership:

Roles Own Everything: Everything in the system is owned by one and only one role. This means every table, view, schema, database and indeed every role has a single owning role. This situation continues in a hierarchy until we get to the ACCOUNTADMIN role that should normally own everything on the account.
Users Own Nothing: Unlike other databases (for example Oracle) in which a user can create a table and therefore owns the table, in Snowflake, the role in force at the time becomes the owner. This can be confusing when (for example) you create a table and then switch roles and can no longer access the table.
Ownership can be transferred: Ownership is effectively a privilege (like select, insert or update), and can be granted (transferred) to another role. Aside from the SECURITYADMIN role which can control any grant on the entire account, only the owning role can transfer ownership to another role.
Owning an Object is Different to Owning a Role: As we’ll see below, owning an object or role means you can alter or grant access to others. However, there are subtle implications in object and roles ownership.

The SQL statement below shows how to transfer ownership of a table to another role. Be aware however, once ownership is transferred the table may potentially become inaccessible.

grant ownership
   on table PROD_DB.MAIN.MY_TABLE
   to role SYSADMIN;

Object Vs Role Ownership

As indicated above, there are some subtle but important differences between owning an object and owning a role.The diagram below illustrates a situation whereby the user LEON who, as the PROD_DATA_ADMIN role, has created a Database, Schema and Table and therefore owns all three.

This means any user with the PROD_DATA_ADMIN role can:

Alter: Only the owner of a database, schema or schema object (eg. Table, View) can alter the object. There is no ALTER TABLE privilege in Snowflake.

Grant: Only the owner (or a role which has inherited ownership) can grant access to the object to another role.
Insert, Update, Delete: While these privileges may be granted to other roles, the owner has full ownership and can insert, update or delete entries.

Using the above as an example, LEON could grant access to the database, schema and tables to the PROD_USER role because he owns them. He cannot however grant the PROD_DATA_ADMIN role itself because he does not own that role.

The user LEON could execute the following commands to grant access to PROD_USER.

grant usage
   on database PROD_DB
   to role PROD_USER;

grant usage
   on schema PROD_DB.MAIN
   to role PROD_USER;

grant select
   on all tables
   in schema PROD_DB.MAIN
   to role PROD_USER;

The diagram below illustrates the new situation.

To summarise, the diagram above shows that LEON has the PROD_DATA_ADMIN role which means he can insert, update, delete or drop the tables in the PROD_DB database, whereas RICK with the PROD_USER role can only select from the tables.

The diagram below introduces the role PROD_ROLE_ADMIN which has ownership of the PROD_DATA_ADMIN role. While this allows Eldon the ability to alter or grant the role to others, it’s important to understand, he cannot actually view the data in PROD_DB.

As the owner of the PROD_DATA_ADMIN role, he could grant this role to himself, but this action would be automatically audited. It’s useful to remember this, as it means you can provide administrators the rights to control who has access to data without actually having access themselves.

As indicated above, Eldon who has been granted the PROD_ROLE_ADMIN role can grant access to others. This means the following SQL statement could be used by Eldon to grant the PROD_DATA_ADMIN role to the PROD_USER.

grant role PROD_DATA_ADMIN to role PROD_USER;

The diagram below illustrates the new situation whereby RICK now inherits full ownership access to the PROD_DB database.

Note: It’s important to understand these examples do not represent best practice for Snowflake role deployment but are purely used to illustrate the concepts involved. In the above example it may not be desirable to allow the PROD_USERS the ability to alter the underlying table structures and we will discuss best practice to design an RBAC solution in a later article.

These examples, do however illustrate some of the subtle but important concepts you need to understand to be able to correctly design and deploy a role-based access solution.

Conclusion

To summarize, Role-Based Access Control (RBAC) is the method used by Snowflake to control access to data and compute resources. Users are granted access to Roles which in turn are granted access to Database Objects and at a minimum you will need to be granted USAGE on the database and schema.

While it’s possible to switch roles, this is often confusing as access rights can change because only the privileges inherited by the currently active role are available and for this reason it’s good practice to set a default role, virtual warehouse and schema for every user. You could however provide a script to switch all three as and when needed.

Although it’s sensible for every user to be allocated a single default role, the concept of role inheritance means access to multiple database and schemas can be aggregated to produce a role hierarchy.

Finally, every object and role is owned by another role in a hierarchy all the way up to ACCOUNTADMIN. The owner can both alter the object and (more importantly) grant access to another role. Be aware however, that owning a role does not automatically give access to the data granted to that role, although this can be useful feature as it means (for example), administrators can grant access to sensitive HR data without actually needing access to the underlying data.

In the next article, I will describe the System Defined roles which are automatically available with Snowflake, and introduce some best practices for designing and deploying RBAC solutions.