A Functional Approach For Snowflake’s Role-Based Access Controls
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.
This blog post presents a methodology for developing just such a security model using Snowflake’s Role-Based Access Control (RBAC) scheme. It recommends an approach that distinguishes object access roles from user functional roles and then describes how to build a unified security model that combines both types of roles.
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:
Key Concepts
The following concepts are key to understanding Snowflake’s RBAC scheme:
- Privileges are never granted directly to users — instead, roles are granted to users, and the privileges granted to these roles determine a user’s level of access to database objects.
- 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:
This type of model works fine for relatively small deployments. But with larger deployments, it becomes unwieldy to manage — especially when the level of granularity for each access role is defined at lower levels such as tables or views. Also, this type of model is difficult to implement when user-to-role assignments are managed by an external identity provider (IdP) such as Azure AD or Okta due to the role ownership and provisioning requirements for Snowflake’s SCIM integration to work.
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:
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.
