When creating a Snowflake cloud data platform role hierarchy, you could quickly end up with thousands of different role types trying to grant access to your users with your original role design. By taking advantage of Snowflake’s inheritance structure and creating roles with business functions in mind, you can easily simplify your resources and relationships.
Whether migrating to Snowflake or starting net-new, one of the first things you have to consider is security and access to your data; users of your data need to be able to access the appropriate information, with just enough access to complete their task. Traditionally, users are given a set of roles/privileges, and all of these privileges are aggregated into one set of access. However, Snowflake only allows a user to assume one role (a collection of privileges) at a time. This role design has positives and negatives.
There are situations where regulations require that some data sets cannot be combined, and this model for roles facilitates that requirement well. However, it can be tricky when your former model for roles is not compatible with this structure. You may end up with thousands of roles that hold every permutation of permission combinations in an attempt to imitate your original role design. This post will explore the Snowflake role hierarchy, and how to structure your data access.
Types of Roles in Snowflake
Before we start talking about role hierarchies and inheritance, it’s important to understand that there are different types of roles within Snowflake: system and custom. There are five different system roles. Let’s talk through each of these roles and how they inherit from each other:
ACCOUNTADMIN
This is the highest level role in Snowflake. It should be heavily limited in access and encapsulates SYSADMIN and SECURITYADMIN
SECURITYADMIN
This role is used for managing any object grant globally, and therefore is granted the MANAGE GRANTS privilege by default. This role also inherits the USERADMIN role.
USERADMIN
This role is dedicated to user/role management. By default, this role can create/modify users and their respective roles (assuming those roles/users haven’t been transferred to another role).
SYSADMIN
This role is dedicated to system object management. It is recommended by Snowflake to assign all custom roles to the SYSADMIN role, so this role can grant these privileges to other roles.
PUBLIC
This is a pseudo role that every user/role in the account gets. Things owned by the public role are, well, public!
As you can see, within the system roles, there emerges a simple hierarchy of roles. This same hierarchy structure extends into custom roles as well. In order to create a custom role, you will need the CREATE ROLE privilege. If starting out with a new Snowflake account, this would be done with the SYSADMIN role.
Let’s start to extend our hierarchy. At this point, it looks like the following:
Building Your Snowflake Role Hierarchy
When creating a new role, it’s important to align the role to a particular goal. You can create a role with the goal to either give access to an object or give access to a particular business function. While the implementation is the same, this guides how you logically assemble your hierarchy and assign privileges in your Snowflake instance.
For example, say you have a group of users that need to be able to query a particular database with sensitive data, which we’ll call Transactions. You could create a role with an object access name like READ_TRANSACTIONS, or you could create a role with a business function name like ACCOUNTING_BASIC. Generally, your organization will end up with a combination of both, but keeping privileges aligned to business functions allows for easier user/role management. That is, if you know Jill is not in accounting but has the ACCOUNTING_BASIC role assigned to her, it’s easier to notice than if she just had a READ_TRANSACTIONS role assigned.
Now, within our transactions table, let’s say your organization needs specific users to be able to create transactions. If you’re creating roles based on object access, you’d probably end up creating a role named CREATE_TRANSACTIONS. You can see how this might lead over time to a lot of roles and user assignments to manage as your need for different access to different roles expands. Instead, we could create a business function guided role with a name ACCOUNTING_ADVANCED that inherits from our ACCOUNTING_BASIC role, and just assign the corresponding roles to our users.
Let’s take a look at what our Snowflake role hierarchy looks like now:
By taking advantage of the inheritance structure within Snowflake, and creating roles with business functions in mind, it allows you to simplify your Snowflake resources and relationships.
Role Management Within Your Enterprise
As you continue to add new objects into your enterprise and Snowflake instance, privileges to those objects need to be handled accordingly. Snowflake provides the ability to automatically grant privileges on future objects once you’ve created your base object.
For example, if you created a schema, and you create a role that grants the select privilege on all those tables, by default you would have to manually add a select privilege for each table to your role. However, by using the “on future tables” syntax when doing your initial privilege grant to your role, this is handled automatically by Snowflake.
As you can see, managing all of the objects within your enterprise, the roles to group privileges, the grants to those roles/users, structuring those in the correct order, and maintaining the Data Definition Language (DDL) quickly becomes too cumbersome to manage manually. If only there was a tool to simplify this process…
Enter Tram
Tram was built by phData specifically to handle provisioning of Snowflake resources/privileges with an infrastructure as code approach. Tram takes a directory with group/model pairs, as well as object definitions, and automatically handles provisioning for your enterprise. In order to add, modify, or delete privileges, pull requests are made to your repository, approved by the appropriate personnel, and then merged/deployed to your environment.
Tram not only simplifies versioning and security but also automatically generates all of your DDL syntax for you. Even in a very basic environment, this can be thousands of lines of DDL that you otherwise would have to write manually. In the case of a migration to a new Snowflake account or instance, you can run Tram against it, and it will automatically replicate all your objects to your new account so you can focus your time and energy where you most need it.
Closing Remarks
Within any enterprise, there are many different rules and regulations set by both the enterprise and the business vertical itself. Users of your system will need different levels of access to data within your enterprise, and with traditional database management systems, manual permission and user management quickly becomes cumbersome. With Snowflake’s ability to create a role hierarchy, and by picking the correct strategy for assigning privileges to your roles and users (coupled with tools like Tram to make this management easier) it allows your enterprise to focus on business deliverables instead of manually writing thousands upon thousands of lines of code.
Wondering what you can do with all that extra time?
As a leader in Snowflake Data Engineering, we’ve helped hundreds of users make the most out of their Snowflake instance. Because of this, we compiled that knowledge into a white paper designed to help you do the same! This paper covers a wide variety of topics, including: automating the management of database objects, standardizing data transformation, creating a monitoring plan, and so much more.
Download our “Getting Started with Snowflake” white paper and start delivering results back to your business!
