A Functional Approach For Snowflake’s Role-Based Access Controls

Craig Warman
Nov 17, 2020 · 4 min read
Database Access Modeling
Database Access Modeling
Photo by Campaign Creators on Unsplash

Overview

Snowflake provides a set of pre-defined roles (such as SYSADMIN and ACCOUNTADMIN), but the use of these powerful roles should be restricted to appropriate personnel. Instead, Snowflake recommends that customers create a custom role hierarchy that reflects the data and user community's security requirements within each Snowflake account.

Background

Snowflake’s RBAC scheme defines who can access and perform operations on specific objects (tables, views, schemas, etc.) within an account. Roles are the entities to which privileges on securable database objects can be granted and revoked and are assigned to users to allow them to perform actions required for business functions in their organization. It looks something like this:

Image for post
Image for post

Key Concepts

The following concepts are key to understanding Snowflake’s RBAC scheme:

  • A user can be granted multiple roles and may choose which role is active in a Snowflake session. A default role can also be defined for each user.
  • Roles can also be granted to other roles, creating a hierarchy of roles. The privileges associated with a role are inherited by any roles above that role in the hierarchy.
  • Every securable object in a Snowflake account is owned by a role, which is typically the role that was active when the object was created. When this role is granted to users, they effectively have shared control over the securable object. The owning role has all privileges on the object by default, including granting or revoking privileges on the object to other roles. Also, ownership can be transferred from one role to another.

Basic Access Role Model

At first glance, it would seem the best model is to define a set of Access Roles to which access privileges have been granted for specific Snowflake account objects. For example, some roles may be granted read-level access to certain tables/views in a given schema, while others might be granted read/write or even ownership access to those objects. A naming convention for such roles typically includes the applicable Snowflake object (such as a database or schema) along with a suffix that indicates the type of access (such as _R or _RW). This might result in access role names such as HR_FULL_DB_R and FIN_TXN_SCHEMA_RW, for example. Here’s what that might look like:

Image for post
Image for post

Functional + Access Role Model

The problems just outlined with the Basic Access Role model can be solved with the introduction of Functional Roles. These are roles that map to business functions within the organization, such as those for HR Specialists or Financial Analysts or Managers. Naturally, such roles are typically assigned to users based on their job functions. Here’s how that might work for the example scenario presented earlier:

Image for post
Image for post

Conclusion

A large variety of Snowflake customers have successfully implemented the Functional+Access Role Model. Besides scaling much easier, it has the added benefit of simplifying external IdP integration. As noted on the right side of the diagram above, the external IdP can manage functional role assignments to users. Simultaneously, Snowflake manages the functional-to-access role hierarchy and access role privilege grants for database objects. In short, it’s considered a best practice for developing a custom role security model using Snowflake’s RBAC scheme.

Snowflake

Articles for engineers, by engineers.

Craig Warman

Written by

Fitness enthusiast, motorcycle rider, Big Data technologist. I’ve made a lot of mistakes in life, so I’m pretty good at apologizing.

Snowflake

Snowflake

An open forum of tips and best practices from data engineers, data scientists, and experts using Snowflake to power their data.

Craig Warman

Written by

Fitness enthusiast, motorcycle rider, Big Data technologist. I’ve made a lot of mistakes in life, so I’m pretty good at apologizing.

Snowflake

Snowflake

An open forum of tips and best practices from data engineers, data scientists, and experts using Snowflake to power their data.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store