Snowflake RBAC: A Guide to Role-Based Access Control

What is RBAC?

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

This article introduces some of the concepts you need to understand and provides good advice on deploying 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 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 straightforward, and the diagram below illustrates the key components, including Users, Roles, and Objects. Effectively, access to Objects is granted to Roles, which 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 used to group users together and control which users have access to database objects.

3. Objects: These include the Databases, Tables, and Virtual Warehouses that 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 of 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 does not provide access to the underlying tables but is a minimum requirement to access data.
Select: On all the tables in the schema.
Usage: A Virtual Warehouse provides the ability to execute queries. Without access to a virtual warehouse, the user cannot run queries.

Want to learn more from a Snowflake Expert? Click on the image below for training from Analytics Today.

Switching Roles

One of Snowflake's single most confusing features is when users switch from one role to another. When they connect to Snowflake, users are assigned an initial role by default. 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 view tables in the PROD_DB database. Notice that although the user has been assigned 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 lead to a best practice, which is to ensure most users have a single role in performing 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 the question: How do you provide access to multiple schemas or databases at the same time? The answer is to use role inheritance.

Snowflake Role Inheritance

The diagram below illustrates a situation in which 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. This 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 role has a single owning role. This situation continues in a hierarchy until we get to the ACCOUNTADMIN role, which 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 own 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, switch roles, and can no longer access the table.
Ownership can be transferred: Ownership is 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 from 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 for object and role ownership.

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

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

Snowflake Training by an Expert.

Click in the image below for more detail

Object Vs. Role Ownership

As indicated above, some subtle but significant differences exist between owning an object and owning a role. The diagram below illustrates a situation in which the user LEON, who has created a database, schema, and table as the PROD_DATA_ADMIN role, 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 (e.g. Table, View) can alter the object. There is no ALTER TABLE privilege in Snowflake.
Grant: Only the owner (or a role with inherited ownership) can grant access to the object to another role. For example, Leon who has the PROD Data Admin role can grant access to a table in the MAIN schema to PROD User 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 owns the PROD_DATA_ADMIN role. While this allows Eldon to alter or grant the role to others, it’s important to understand that 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 helpful to remember this, as it means you can give administrators the right to control who has access to data without having access themselves.

As indicated above, Eldon, who has been granted the PROD_ROLE_ADMIN role, can grant access to others. This means Eldon could use the following SQL statement 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 practices 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 practices to design an RBAC solution in a later article.

However, these examples illustrate some of the subtle but important concepts you need to understand to correctly design and deploy a role-based access solution.

Conclusion

To summarize, Role-Based Access Control (RBAC) is the method Snowflake uses 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. 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, role inheritance means access to multiple databases 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 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 that are automatically available with Snowflake and introduce some best practices for designing and deploying RBAC solutions.

Snowflake Training from an Snowflake Superhero.

Click on the image below for more information.

Understanding Snowflake Role-Based Access Control: A Complete Guide to RBAC