Role-Based Access Control Strategies in Snowflake
Once you understand the fundamentals of Role-Based Access Control (RBAC) in Snowflake, you quickly realize that there is no one size fits all solution on how to design and implement an effective RBAC hierarchy. Having the ability to create a nearly unlimited amount of custom roles with varying object permissions and structural hierarchy that enforces privileges inheritance can be overwhelming.
If you have no idea about what I am talking about or what RBAC is, take a quick 4 min read through this Intro to Role-Based Access Control in Snowflake. It will cover the key concepts and basics that you need to know before making an RBAC strategy that is right for your organization.
Before going any further I want to emphasize that the following is based on my experience and observations. The approaches outlined below are not the only options and are just a few examples of countless possibilities.
Based on my experience there are three primary RBAC strategies that organizations use:
· 1:1 User to Role
· Functional Roles-Direct Privileges
· Access Roles-Inheritance
Each of these models have their advantages and disadvantages depending on the organization and its needs. One of the best ways to find which is best for you is by considering your:
1. User Base
How many Snowflake users will you have in your organization? Is that expected to grow by a material amount in the coming years?
2. Data Sensitivity
Does your environment contain a wide range of sensitive data that you don’t want most of your users to see?
3. Access Requirements
Will you require granular privileges on schema and table levels, or do you mainly require broad database privileges?
Answering these questions before moving on will help guide you to a strategy that might be a good fit for your organization. Now, let’s get down to it and explore some of the options.
1:1 User to Role Model
This model uses a very simple approach. For every user, you also create a role specifically for that user. This allows an organization to avoid creating complex data access flows and enables directly assigning privileges to the user role that needs them.
I would only suggest this option to organizations with a very small Snowflake user base (let’s assume less than 10 to put a number on it) with no expected user growth in the next 3–5 years. If you implement this method and decide to materially increase your Snowflake user base, I would recommend quickly redesigning your RBAC strategy to one of the other models.
Functional Roles-Direct Privileges Model
This model uses business functions within an organization as a template for planning and implementing a Custom Role hierarchy. For example, functional roles may include Finance, Data Analysts, HR, etc. The functional roles are then directly given privileges to securable objects. These roles are ultimately assigned to Snowflake users based on their job functions.
This is one of the most common approaches and is recommended if you require roles to be granted very granular privileges to multiple subsets of specific tables and schemas, OR if you have a wide range of sensitive data throughout your database.
It is important to note that there are additional tools to help with the accessibility and restrictions to sensitive data in Snowflake. If you want to know more about this check out dynamic data masking policies.
Access Roles-Inheritance Model
This model builds upon the functional role concept and is considered a Snowflake best practice. The primary difference is that with this method, the functional role is never assigned any privileges. Instead, we create an additional layer below the functional roles in our RBAC hierarchy called “access roles”.
The access roles are granted privileges on database objects, usually at the database and schema level. With this method you could create access roles at the table or view level as well, but the more granular the privilege requirements are, the more access roles you need to create. If all your data needs privileges determined on a table by table basis, it may be better to use the Functional Roles-Direct Privileges Model.
Once you have granted object privileges to the access roles, those access roles are then assigned to functional roles that require permissions to those same objects. Once assigned, all privileges granted to the access role are inherited by the functional role.
There are several benefits to the Access Roles-Inheritance Model. It is simple, flexible, and low maintenance when compared to the more complex alternatives. Object access can be easily, quickly, and consistently applied to multiple functional roles without a bunch of tedious additional work defining privileges. It is also much easier to scale out as your user/role base grows.
In Summary
Even though there is no one size fits all solution, thinking through the questions and models outlined in this article may help you on your journey to implement an effective and efficient RBAC strategy.
