Snowflake System Defined Roles - Best Practice

Role Based Access Control (RBAC) can be a challenging subject for any Snowflake Administrator. This article explains some of the most important RBAC concepts, the System Defined Roles and their purpose. It goes on to explain 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 which 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 to. The diagram below illustrates the hierarchy of objects starting with the Account and the 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 level and Snowflake provides a huge number of Access Control Privileges which 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 which provide access to a table. 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 illustrates the most common grant scenario, in reality every Snowflake role (including the System Defined roles) are simply a role with a predefined set of privileges. The diagram below illustrates the syntax used 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;

System Defined Roles

The important point here, is that System Defined Roles don’t have unique powers and it’s perfectly possible to create a custom built role with the same privileges as the prebuilt system defined roles. This means however, we need to be careful when designing the RBAC solution to avoid granting super-powerful privileges. For example, it would be a huge mistake to grant the manage grants privilege to a custom role as this makes it as powerful as the System Defined role SECURITYADMIN which can change the grants to any role on the Account. This could be used (for example), to grant the ACCOUNTADMIN role to yourself.

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. These roles 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 the System Defined Roles - that they don’t have any special access to roles or objects and access can easily be lost. In the above situation, the user LEON no longer has access to either the roles or the database. Furthermore, unless another user has the PROD_DEVOPS role, the database has become completely inaccessible.

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 and can therefore correct the above situation by granting ownership to roles to fix the situation.

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: Which inherits the privileges of the roles below in addition to a number of powerful privileges specific to this role.
SYSADMIN: Which by default has the privileges needed to create databases, schemas and warehouses.
SECURITYADMIN: Which can create users and roles and has the powerful manage grants privilege and can therefore affect every grant on the entire account.
USERADMIN: Which has the ability to create users and roles, but unlike SECURITYADMIN can only affect the objects it has created.
PUBLIC: Which 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 of the key roles in the system with ACCOUNTADMIN reserved purely for system configuration, while SECURITYADMIN and USERADMIN are used for User and Role management while SYSADMIN is used to manage databases, schemas and tables.

System Define Roles - Best Practices

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

ACCOUNTADMIN

Must be reserved for account level administration. It must not be used on a day-to-day basis and access must be limited to a few (less than five) users on the entire account.
ACCOUNTADMIN must not 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 the absolute top of the role hierarchy.

SECURITYADMIN

Should be used only in exceptional circumstances to deploy or control access to roles. As this role has the powerful MANAGE GRANTS privilege, it can alter any grant on the entire system including granting ACCOUNTADMIN role to any user.
Instead of using SECURITYADMIN to manage Users and Roles, the USERADMIN role should be used for day-to-day usage.
You should avoid granting this role to other roles.

USERADMIN

Should be the default role for creating and managing both 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 granted to another role to manage a sub-set of roles. For example, it may be sensible to create a DEV_ROLE_ADMIN role with the ability to create and manage roles within a development environment. (More detail in the next article).
This role should normally be the owner of all other custom roles, but should not be granted any access to the underlying tables. This means it should only be granted roles which themselves own other roles. USERADMIN must never be granted access to roles which have in turn been granted access privileges to tables.

SYSADMIN

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

Separation of Responsibility

Notice 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 responsible for 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 in turn have access to Database Objects.
SYSADMIN: That has been granted the roles which 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 an important best practice as it means system administrators can control every role on the account.

In the next instalment 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.

Next Article in the Series