Intro to Role-Based Access Control in Snowflake
Snowflake has become a solution to many of the problems that plague us when we deal with big data. The impressive query performance, low storage costs, and the ability to scale in, out, up and down on demand has us drooling at the possibilities. However, without a good foundation and understanding of Role-Based Access Control, you can quickly create: overly complex hierarchies, an overwhelming number of roles, and complicated data access flows. This can also lead to security issues if privilege inheritance is not well understood and sensitive information is unintentionally provided to an unnecessary role.
So what do we need to know? Snowflake has combined aspects from 2 access control models. There is Discretionary Access Control (DAC) which dictates that every object has an owner, and the owner of that object can grant others access to it as well. The other is Role-Based Access Control (RBAC) which focuses on the concept that privileges are granted to roles, and those roles are granted to users.
Now a lot of people like to symbolize a Role as being a hat you can wear. But based on its function, I think a Role acts more like a symbolic key versus a hat you put on. The only way to get through a door and interact with an object on the other side is to have the right key.
In order to illustrate the symbolic key concept I put together the below image. You can see that the users themselves do not have direct access to any of the securable objects. The Roles must also be granted specific privileges to access and interact with securable objects. These privileges act like the grooves in a key. If you don’t have the right privileges (grooves in the key), you won’t be able to get through the door.
Now that we have a basic understanding of what a Role is, it is important to note that Snowflake starts everyone out with 5 default Roles. In order to best describe these roles, I have gathered this information directly from the official Snowflake documentation.
ACCOUNTADMIN
Role that encapsulates the SYSADMIN and SECURITYADMIN system-defined roles. It is the top-level role in the system and should be granted only to a limited/controlled number of users in your account.
SECURITYADMIN
Role that can manage any object grant globally, as well as create, monitor, and manage users and roles.
USERADMIN
Role that is dedicated to user and role management only. Is granted the CREATE USER and CREATE ROLE security privileges. Note, the USERADMIN role is granted to SECURITYADMIN.
SYSADMIN
Role that has privileges to create warehouses and databases (and other objects) in an account. It is recommended that any custom role hierarchy is ultimately assigned to the SYSADMIN role.
PUBLIC
Pseudo-role that is automatically granted to every user and every role in your account. The PUBLIC role can own securable objects, just like any other role; however, the objects owned by the PUBLIC role are available to EVERY other user and role in your account.
In addition to these 5 system-defined roles, you have the ability to make as many Custom roles as you want. However, the best practice is to ensure that the Custom roles all rollup to the SYSADMIN.
There is one more concept to understand about RBAC in Snowflake before you start planning your own custom hierarchy…. inheritance. In Snowflake role hierarchies are allowed which means you can assign a Role to other Roles. As you can see in the image below, any privileges granted to the lower Role(s) in a hierarchy will be automatically inherited by the Role it is being assigned to.
I know that the functionality surrounding RBAC can seem simple, but as you continually migrate data and have more teams using Snowflake to create a data lake, data marts, dev, prod, etc. it becomes extremely complex very quickly. I highly recommend taking the time to create a role hierarchy that meets your needs and will require minimal maintenance.
You probably have even more questions now. Where do I go from here? How do I create a role hierarchy that is low maintenance and aligns with best practices? I will provide my opinion on these questions and much more in my next article. Stay tuned!
