The Snowflake Data Cloud provides a powerful hierarchical role model as the standard for defining access across the Snowflake platform. This is powerful because roles can inherit privileges from other roles, making it easier to manage access across your Snowflake account.
In this guide to Snowflake role hierarchy, we will walk you through the creation and management of a hypothetical project (‘Rocketship’) and demonstrate the required access control to access data that lives in the Rocketship project.
We’ll show the SQL statements required to:
- Build up the role hierarchy
- Grant privileges to roles
- Grant approved roles to users based on functionality and access required to get their work accomplished
At each step, we will show an image of the current state of the environment to help visualize the relationships between the objects we create, like databases and schemas, and the access control we are building for users, roles, and privileges.
Start Using Traverse Today!
Traverse makes it easier to understand, use, and fix the privileges in your Snowflake account. Test drive Traverse in a demo environment!
Why Are Roles Important?
Before we get started creating the hierarchy, it’s important to understand why roles are important and the advantages of creating a role hierarchy.
Snowflake allows you to define how you want to manage access, including granting privileges directly to users. Taking this approach is difficult and fragile since each user added will create an exponentially increasing list of individual grants to keep up with.
In contrast, when using a role hierarchy:
- Roles can be re-used and granted to many users. Because of this re-use, fewer statements need to be used. It’s also easier to go back and modify a role than it is to modify statements granted to individual users. If statements are granted to individual users, there will be one statement for each user instead of one statement for each role. This increases the attack surface and introduces potential security risks that are easily overlooked due to the amount of statements required to be maintained.
- Roles can be granted to other roles, inheriting their privileges. Again, this reduces the number of grants that need to be created, which leaves less room for error. Users are also able to choose the least powerful role that’s needed for their current task, reducing the risk of modifying or dropping data accidentally when the user only intends to read data.
Learn more: Get Our Guide to Snowflake Role Hierarchy Best Practices
Getting Started: The Rocketship Project Example
In this example, we will create a ‘rocketship’ database with a schema that holds telemetry data.Â
We will partition the privileges so users will only have the ability to perform the tasks they need — and nothing more. The examples are intentionally kept simple, but the ideas used can be applied to any Snowflake objects.Â
First, we set up a database, schema, and two warehouses for the Rocketship project.
USE role SYSADMIN;
CREATE DATABASE rocketship;
CREATE SCHEMA rocketship.telemetry;
CREATE WAREHOUSE telemetry_etl;
CREATE WAREHOUSE telemetry_analysis;
Image created by Traverse.
Different users will need different access levels to each of the objects. For example:
- Database
- Schemas, and
- Tables
An engineer might need write access to all tables so they can insert data, while an analyst only needs to be able to read data. An administrator should have the abilities of both the engineer and the analyst, plus extra privileges to manage the project.
Creating the Roles
First, we create a role for each access level that’s needed:Â
USE ROLE SECURITYADMIN;
CREATE role rocketship_administrator;
CREATE role rocketship_engineer;
CREATE role rocketship_analyst;
Creating the Role Hierarchy
Next, we link the roles, so that each role that’s more ‘powerful’ will inherit the privileges of the less powerful roles. The engineer role inherits from the analyst, and the administrator inherits all privileges of the engineer.
USE ROLE SECURITYADMIN;
GRANT role rocketship_analyst TO role rocketship_engineer;
GRANT role rocketship_engineer TO role rocketship_administrator
Now we can see the administrator inheriting from the engineer, which inherits from the analyst.
Image created by Traverse
Applying Privileges
Now that the roles are linked, we can start applying privileges.
Analyst Role
The Analyst only needs to read data, so we’ll grant:
- USAGE on the schema and database so the user can use them
- SELECT on all the tables that will be created in the schema
- USAGE on a warehouse that can be used to query data
USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE rocketship TO role rocketship_analyst;
GRANT USAGE ON SCHEMA rocketship.telemetry TO role rocketship_analyst;
GRANT SELECT ON ALL FUTURE TABLES IN SCHEMA rocketship.telemetry TO ROLE rocketship_analyst;
GRANT USAGE ON WAREHOUSE telemetry_analysis TO role rocketship_analyst;
Image created by Traverse
Engineering Role
The engineer role will be adding new data to the tables as it is collected, so this role needs:
- CREATE TABLE and CREATE VIEW
- INSERT privileges into those new tables and views
- USAGE on a warehouse to perform etl
GRANT INSERT ON ALL FUTURE TABLES IN SCHEMA rocketship.telemetry TO role rocketship_engineer;
GRANT CREATE TABLE,CREATE VIEW ON SCHEMA rocketship.telemetry TO role rocketship_engineer;
GRANT USAGE ON WAREHOUSE telemetry_etl TO role rocketship_analyst;
This image shows the privileges of the engineer role by itself:
Image created by Traverse
Since the engineer role inherits privileges from the analyst role, it also has the privileges of the analyst role.
Image created by Traverse
Administrator Role
The project administrator role is the most powerful. In addition to inheriting privileges from the engineer and analyst role, they will be able to create stages to ingest new telemetry data from an external source.
USE ROLE SECURITYADMIN;
GRANT CREATE STAGE ON SCHEMA rocketship.telemetry TO ROLE rocketship_administrator;
The picture is starting to get bigger now, with the administrator role having the most privileges.
Image created by Traverse
User Access
Finally, we’ll create some users and grant the roles. The users can then assume the roles and get to work ingesting and analyzing the telemetry data.
USE ROLE USERADMIN;
CREATE USER neil_armstrong;
CREATE USER sally_ride;
CREATE USER alan_shepard;
USE ROLE SECURITYADMIN;
GRANT ROLE rocketship_administrator TO USER neil_armstrong;
GRANT ROLE rocketship_engineer TO USER sally_ride;
GRANT ROLE rocketship_analyst TO USER alan_shepard;
Now users have access to their roles:
Image created by Traverse
To bring it all together, we can now see the users with their roles — and all the privileges by schema, database, and warehouses.
Image created by Traverse
Visualizing Snowflake Role HierarchyÂ
Snowflake roles are powerful and can help better organize access control within a Snowflake account. Hopefully, these visualizations help explain the advantages and usage of Snowflake roles.Â
Want to try it out for yourself? Test drive Traverse in a demo environment!
Putting it all Together
Using the ‘Rocketship’ database example above, we have demonstrated the power of the Snowflake role hierarchy and how inheritance of roles simplifies user and access management.Â
Combining the power of the role hierarchy with the visualization of Traverse allows our customers to quickly visualize and audit who has access to which data sets or Snowflake resources.
