Snowflake offers many powerful tools to protect your data from unauthorized access but it can be difficult to know where to start. In this article I provide a high level overview of Snowflake security features that offer fine-grained access control to help you keep your data safe. I'll start by providing an example of how to manage access using Snowflake roles since they are an important primitive used throughout Snowflake's security offering. I'll also demonstrate how to control access at the column and row levels and provide a summary of advanced Snowflake security features that can also be used to manage data access.
An important capability enabled by Snowflake’s RBAC system is role hierarchies. A role in Snowflake can have its own child roles and inherit their privileges. Snowflake recommends leveraging role hierarchies to create two types of roles: object access roles (a collection of privileges) and functional roles (a collection of object access roles). There is no technical difference between these two types of roles. "Object access" and "functional" are simply terms to describe how roles can be structured. Designing a scheme with these two types of roles can be a handy technique to safely manage access to your data.
Think of the functional roles as representing business roles in your organization. They are the roles you would grant directly to your Snowflake database users to provide the access that enables them to do their jobs. If you design them correctly, they could also be mapped to external identity providers. For example, you could use group linking via Okta’s SCIM integration for Snowflake where your Okta groups are mapped to functional roles in Snowflake.
If this sounds like a useful approach, let’s work through an example. But first, I’ll pull some definitions from Snowflake’s Access Control docs.
Each securable object resides in a logical container, in a hierarchy of containers. Here is a handy visualization also from Snowflake’s docs.
For our example, we'll create a schema for a simple banking app along with sample data and two users: Alice and Bob. Alice is our CFO who will need access to analyze financial data and Bob is one of our Product Managers who wants to track product analytics. We're going to create object access roles with privileges to relevant data. Then we will grant the object access roles to functional roles that we can in turn grant to our users. This is a hefty chunk of code below but it will help set up our next few examples.