Snowflake System Roles - Best Practices

Role-based access control (RBAC) can be challenging for any Snowflake Administrator. This article explains some of the most essential RBAC concepts, including System-Defined Roles and their purpose. It then explains system-defined role best practices, which will form the underlying principles for building an extensible RBAC solution.

Role-Based Access Control - Series

This article is part of a series of three, including:

Introducing Role Based Access Control, an introduction to RBAC concepts
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.

System Object Hierarchy

Before describing the System-Defined Roles, it’s worth understanding the overall structure of objects that we are working with to secure access. The diagram below illustrates the hierarchy of objects, starting with the Account and account-level objects (Databases, Users, Roles, Virtual Warehouses, etc.) and the Schema and schema-level objects, including Tables, Views, and Materialised Views.

Introducing Role Privileges

Clearly, then, we aim to protect items at the Account and Schema levels, and Snowflake provides a huge number of Access Control Privileges that may be granted to Roles, which provide the ability (for example) to select, insert, or update data entries in a table.

The diagram above illustrates the syntax used for the most common grants providing table access. The SQL below illustrates a typical grant statement.

grant select, insert, update, delete
   on my_schema.my_table
   to role prod_analyst;

While the above diagram and SQL illustrate the most common grant scenario, in reality, every Snowflake role (including the System-Defined roles) is simply a role with a predefined set of privileges. The diagram below illustrates the syntax to grant access to the most common system-defined privileges.

The SQL statement below shows the command needed to create a new role and grant the create database privilege to that role. This effectively gives the role PROD_SYSADMIN similar privileges to the system-defined role SYSADMIN.

create role PROD_SYSADMIN;

grant create database
   on account
   to role PROD_SYSADMIN;

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

System Defined Roles

The critical point is that system-defined roles don’t have unique powers, and creating a custom-built role with the same privileges as the prebuilt system-defined roles is possible. However, this means that we must be careful when designing the RBAC solution to avoid granting super-powerful privileges. For example, it would be a colossal mistake to grant the manage grants privilege to a custom role as this makes it as powerful as the System Defined role,SECURITYADMINwhich can change the grants to any role on the Account. This could be used (for example) to grant yourself the ACCOUNTADMIN role.

However, a secondary (and more important insight), is that the System Defined roles have no in-built special access to roles or tables and it’s perfectly possible to get yourself into a complete mess.

Take, for example, the situation below as a starting point.

In the above situation, LEON has been correctly granted the SYSADMIN and USERADMIN roles, which in turn own the PROD_DEVOPS and PROD_USER roles. However, if he executes the following SQL script, the situation changes to the completely inadvisable situation illustrated in the diagram below.

use role USERADMIN;
revoke role PROD_DEVOPS
   from role SYSADMIN;

grant ownership
   on role PROD_DEVOPS
   to role PROD_USER;

grant ownership 
   on role PROD_USER
   to role PROD_USER;

The above diagram illustrates one of the lesser-known facts about System-Defined Roles—that they don’t have any special access to roles or objects and that access can easily be lost. In the above situation, LEON no longer has access to the roles or the database. Furthermore, the database has become entirely inaccessible unless another user has the PROD_DEVOPS role.

This example also underscores one of the best practices of role-based access control. You must limit access to the SECURITYADMIN and ACCOUNTADMIN roles, as (by default) only these two have the powerful manage grants privilege. Therefore, you can correct the above situation by granting ownership to roles.

Overview of System-Defined Roles

The diagram below illustrates the system-defined roles, which are pre-built in every Snowflake account and cannot be removed.

The system-defined roles include:

ACCOUNTADMIN: This inherits the privileges of the roles below and several powerful privileges specific to this role.
SYSADMIN: This, by default, has the privileges needed to create databases, schemas, and warehouses.
SECURITYADMIN: This can create users and roles, has powerful manage grant privileges, and can, therefore, affect every grant on the entire account.
USERADMIN: This can create users and roles, but unlike SECURITYADMIN, it can only affect the objects it has made.
PUBLIC: This is automatically granted to every other role. Any privileges granted to PUBLIC are accessible to everyone on the account.

The diagram below illustrates the primary intended purpose of each role.

ACCOUNTADMIN It is reserved purely for system configuration.
SECURITYADMIN And USERADMIN are used for User and Role management.
SYSADMIN Is used to manage databases, schemas, and tables.

Snowflake Training by an Expert.

Click in the image below for more detail

System Define Roles - Best Practices

It is sensible to follow the guidance below to avoid confusion and poor design decisions.

ACCOUNTADMIN

It must be reserved for account-level administration. It must not be used daily, and access must be limited to a few (less than five) users on the entire account.
The ACCOUNTADMIN role must never own any objects as these can only be amended by this super-powerful role.
This role must not be accessible to administrators on a day-to-day basis. Ideally, provide system administrators with a separate login with access to this role alone. For example, the user JRYAN might have a user JRYAN_ACCOUNTADMIN. This forces users to switch to the specific USER ID to perform ACCOUNTADMIN tasks before logging off again.
This role must never be granted to any other role. It must be at the absolute top of the role hierarchy.

SECURITYADMIN

Ideally, you should use the USERADMIN role instead of SECURITYADMIN. This role has the powerful MANAGE GRANTS privilege, which allows it to alter any grant on the system, including granting the ACCOUNTADMIN role to any user.
You should avoid granting this role to other roles.

USERADMIN

It should be the default role for creating and managing Users and Roles.
This role only has access to the roles it creates, which helps limit the blast radius if mistakes are made.
While you should avoid granting this role to others, the underlying privileges (create user and create role) could be given to another role to manage a sub-set of roles. For example, deploying a

role that can create and manage roles within a development environment may be sensible. (More details will be provided in the following article.
This role should generally own all other custom roles but not be granted access to the underlying tables. This means it should only be given roles that it owns other roles. USERADMIN must never be given access to roles that have, in turn, been granted access privileges to tables.

SYSADMIN

This should be the default role for creating and managing Databases, Schemas, and underlying database objects.
While you should avoid directly granting this role to others, the underlying privileges (including create schema and create table(etc.) could be granted to another role that owns the underlying objects.
This role should generally be the eventual owner of database objects but must never own roles. This means it may be granted roles that own or have been granted access to tables, but it must never be granted roles that own roles. This maintains the separation of responsibility described below.

Separation of Responsibility

Notice that the above best practices lead to a separation of responsibility between roles and database objects. This is intentional and should be enforced throughout the system.

Effectively, SECURITYADMIN And (primarily) USERADMIN are responsible for roles, whereas SYSADMIN is the owner of Tables, Views, and all other database objects.

Summary and Conclusion

The diagram below shows a typical grant and ownership hierarchy.

The diagram above embodies many of the RBAC principles we have discussed both in this article and in the Introduction to Role-Based Access Control article, which includes:

Separation of Layers: With Users who have been granted Roles which have access to Database Objects.
SYSADMIN: This role has been granted the roles that, in turn, own database objects. This means the SYSADMIN role has access to every table and all the data on the entire system. This is an important best practice as it means account-level system administrators can control every table in the entire system.
USERADMIN: That owns the roles but has been granted no access to data. Again, this is a critical best practice as it means system administrators can control every role on the account.

In the next installment in this series (Designing Role-Based Access Solutions), I will discuss some of the more complex role-based features available, including managed access schemas, schema-level grants, and future grants. It also describes how to architect an extensible and highly scalable RBAC solution.

Snowflake Training from an Snowflake Superhero.

Click on the image below for more information.

Next Article in the Series