We are RSA Innovation Sandbox Award 2021 top 10 finalists
Watch our submission video

Snowflake Role Hierarchy

Snowflake’s data access modeling is different from many other databases and data warehouses. It’s funny when I hear the question, “does Snowflake offer RBAC?”  Snowflake works  purely in a role-based access control (RBAC)  model, where you can only assign privileges on actions and objects to roles, never to users. Moreover—and this part is slightly controversial—the roles granting access are alternative, not accumulative, meaning that if you have several roles, you’re going to have to choose one for the queries you’re running.

 

This access control model has good intentions: to force the organization to work in a truly RBAC model, and to allow users to be able to have several “hats” which they can “put on” according to the actions they want to perform. For example, an administrator can use their user account with a certain role and use the more permissive administrative role with wide privileges to create new objects only when it is needed for administrative purposes.

 

We discussed in another article ways you can scale your Snowflake roles management, as well as how you can simplify the scaling of Snowflake users management by introducing Satori to the mix. However, a topic that comes up a lot in my talks with DataOps and data engineering people is Snowflake’s unique feature of role hierarchy. Some Snowflake administrators claim it’s one of the strongest enablers of role management at scale; others are saying it’s a risky feature that  can create an unclear roles structure.

 

I personally think they’re both right: Snowflake role hierarchy can allow great things to be done, but it also has the potential to entangle role architecture and transform it into a complex architecture where it’s not clear what will happen when you grant or revoke a role.

 

Let’s start with answering a few common questions, to make sure everything is clear.

What is a Role in Snowflake?

In Snowflake, rights to perform actions on objects are granted as privileges. For example, a privilege can be the right to perform a select statement (read data) from a specific table, or a right to use a certain database or warehouse. Unlike other databases where these privileges (or permissions, as they’re sometimes called) are assigned to users, who have a set of privileges, in Snowflake these privileges are assigned only to roles.

 

A Role is therefore the “sum of its parts,” or a collection of privileges on different objects. Some roles are created when a new Snowflake account is created (such as the ACCOUNTADMIN and SYSADMIN administrative roles, or the PUBLIC role, available to all users). Additional roles are created by Snowflake administrators using the CREATE ROLE command, or provisioned by an IdP (in such cases, an Identity Group which was configured to be provisioned is then created as a Snowflake role).

 

A Snowflake user can be granted one or more roles, and for each query the user executes, a single role is used to determine whether or not the query has sufficient privileges.

 

There are several common ways to manage Snowflake roles, and we discussed them here—but for now, the important thing to remember is that a role is an object that holds a set of privileges and can be granted to a user. The configuration of what objects are granted to which role can be found in the snowflake.account_usage.grants_to_roles metadata view, and the configuration of what roles are granted to each user can be found in the snowflake.account_usage.grants_to_users metadata view.

Snowflake Role Hierarchy

Snowflake’s role hierarchy speaks to the programmer in me (who’s hidden snuggly somewhere while I’m writing this). Instead of creating role privileges from zero, you can reuse an existing role and inherit its privileges from another role. Technically, that’s done using the GRANT ROLE <parent role> TO ROLE <receiving role>. You can cancel the inheritance of privileges at any time by using the REVOKE ROLE <parent role> FROM ROLE <receiving role>.

 

Some important characteristics about role hierarchy:

 

  • Role hierarchy is multi-tiered. That means that a role can both receive the privileges set from another role as well as give them to another role at the same time.
  • A role can be granted to several roles. That means that multiple roles will get the privilege set of the specific role.
  • A role can be granted with several roles. That means that this role will get several privilege sets of different roles.
  • All these characteristics work simultaneously, meaning that you can create a multi-tiered roles ecosystem where several roles are building “bigger building blocks” and roles are granted with several roles. 

 

As you can see, there is a high degree of flexibility in the way you set role hierarchies, and this allows you to use role hierarchies for different use cases, but the flexibility also allows organizations to build complex role structures, where changes in one role need to be traced to measure their effect on other roles, and this increases the overhead when adding new roles or modifying existing ones, which are either translated to a slower rate of data enablement, or to a higher security risk.

 

This is intensified, because  roles are not only used in the classic object-action ownership model but also used for fine-grained access control, inside views, and masking policies, to enable row-level access control, column-based access control, and other specific use cases. These settings are often harder to take into account when making changes to roles or privileges because they’re sometimes “hidden” inside a CASE WHEN statement in a view, or in one of the masking policies.

Snowflake Role Hierarchy Limitations

Nothing is without limitations, but around such recursion, there are two main questions to answer.

 

How many Snowflake Hierarchy Tiers can you have?

There is virtually no limit on the number of tiers you can have. I have personally tested 1,000 tiers of hierarchy (roles test_1 to test_1000, where each role is granted with its predecessor), and everything seemed to function normally (granting a privilege to role test_1 reflected all the way down to role_1000). Remember: just because there are no limits to the number of tiers doesn’t mean you need to add more and more levels, adding obscurity.

Circular Hierarchy

There is a limitation, though, on circular hierarchies, which is totally understandable. That means that a role can’t inherit from a role it also (directly or indirectly) is granted to. In such cases, you will get the following error:

SQL execution error: Circular role grants not allowed. Granting role TEST_1000 to TEST_1 would create a cycle.

 

Approaches of using role hierarchy in Snowflake

As mentioned above, one of the problems that causes  a messy role structure is that the hierarchical roles feature is very flexible. This is why it’s crucial to be very clear about the way you want to structure your Snowflake roles, and then use Hierarchy if it fits, and use it as a tool, and not introduce it ad hoc because it fits a certain scenario without looking at the big picture. Here are some of the common scenarios when using role hierarchies makes sense.

Grouped access to datasets

One approach is to group certain resources (mostly tables, views, and schemas) into logical units and set corresponding roles to these datasets. By splitting your Snowflake account into such segments, you can then make the decisions of which organizational function requires access to those datasets, and those functions will get the corresponding roles.

Project Management

Sometimes it makes sense to create a role that’s specific to a project. Let’s consider a project that involves several different teams, and requires elevating their privileges to work on certain data. Creating roles for such projects can be helpful because when this project is over, its privileges can be removed, eliminating any residual over-privilege risk. In addition, this specific project role can have privileges to a specific warehouse for better resource control.

Building blocks, used by IdP groups

IdP groups are manifested in Snowflake as roles. What’s done in many cases is to keep such roles lean, and not give them specific privileges, but assign them to a corresponding role with the required privileges. This decoupling helps maintain better flexibility when changes are needed (for example, when new IdP groups are added to Snowflake). The logical roles inherited by the IdP-provisioned roles can be those made for specific datasets, or such done for specific projects.

 

In some cases, the building blocks are used by roles specific to users, either for the entire organization or for specific use cases. For example, a user named BEN will be assigned with a ROLE_BEN role, who will inherit the different “building block” roles.

Organizational Structure Hierarchy

This is recreating hierarchy per the organization structure, and as with the other strategies, it may or may not fit the certain organizational and data structure. This means that a certain team has a role with privileges to certain actions and objects, and their upper tier inherits all of that team’s privileges, along with the other teams reporting to it. An example is regional teams having access to regional data, and the global team (or larger regional team) has access inherited from all its subgroups.

 

Role Hierarchy in “Satori For Snowflake”

In a scenario where the Snowflake account(s) of the organization are augmented by Satori, you can use all the above architectures of Snowflake Role Hierarchy. Inn addition, though, you have several tools added to your toolbox for better access and roles management at scale:

 

  • You can easily create data users directory groups of users based directly on IdP groups, on Snowflake roles, and specific Snowflake roles, and use these groups to simplify roles management.
  • You can easily set datasets within a Snowflake account, as well as across Snowflake accounts (or even across different data platforms), and assign permissions across this entire dataset, including fine-grained access control (row-level, column-level and attribute based access control).
  • You can get analytics, audits, and data inventory across all your roles and data sources.
  • You can set global security permissions across all your data sources.

 

If you’d like to learn more about Satori for Snowflake, click here to schedule a demo.