Snowflake Role-Based Access Control (RBAC) Approach
Learn how Role-Based Access Control (RBAC) works and how to deploy it within Snowflake.
What does RBAC mean?
One of the most challenging tasks for Snowflake administrators is to deploy and manage access control. RBAC is the approach that Snowflake uses to secure access to its system (i.e. data, resources, objects, etc.). RBAC is modular, meaning roles are provided independently to each component of the system — this allows each component to be combined in multiple ways and administrators to design the right fit for their purpose whilst also mitigating the risk of hard-to-maintain solutions.
Even though RBAC is primarily recommended when the organization has a simple structure with only a few roles, there are two other authorization methods that could be implemented, depending on the use-case.
Policy-based access control (PBAC): with this authorization method, access is not only determined by role and associated permissions, but also by a variety of other attributes, providing finer-grained control capabilities.
Attribute-based access control (ABAC): administrators can create precisely targeted rules without needing to create additional roles. This is because this method uses attributes, or characteristics, to dynamically determine user access privileges.
What are the RBAC components?
Objects: databases, tables, views, virtual warehouses, etc. which need to be secured and users need to access them to perform different tasks.
Users: those who need access to the database objects.
Roles: those granted to users. They grant and revoke privileges on the different database securable objects.
Privileges: those granted to roles. They specify the operations that the users can perform on the different database objects.
Below is a high-level diagram that describes how these elements interact together.
In the diagram above, the user is granted a role. Privileges to the various database objects (i.e. databases, schemas, tables, views) are granted to the role. Example:
Grant role <role_name> to user <user_name>;
Grant usage on database <database_name> to role <role_name>;
Grant usage on schema <schema_name> to role <role_name>;
Grant select on table <table_name> to role <role_name>;
Grant select on view <view_name> to role <role_name>;What are roles?
Roles are entities that will grant and revoke access to distinct securable database objects. Users are granted one or more roles, allowing them to switch between them to access the required database objects needed to undertake various business functions.
Furthermore, role hierarchies are created as a result of roles being assigned to other roles. The privileges associated with a role are inherited by any roles above that role in the hierarchy.
What are inbuilt, system-defined roles?
Each Snowflake account has inbuilt, system-defined roles which can’t be removed, and privileges assigned to these roles can’t be revoked. It is not recommended to use system-defined roles to provide access to users. These roles should be reserved for the right people to perform admin tasks and access to them should be restricted. Snowflake recommends creating a custom role hierarchy tailored to the use case to provide users with access.
For example, to create objects, run automated scripts, etc. it is not recommended to use ACCOUNTADMIN system-defined role. To perform those tasks, we create a custom role with all the required privileges to achieve the required outcome.
CREATE ROLE IF NOT EXIST CUSTOM_ACCOUNTADMIN;
GRANT <required privilege> ON ROLE CUSTOM_ACCOUNTADMIN;Snowflake has six inbuilt, system-defined roles:
- ORGADMIN
- ACCOUNTADMIN
- SECURITYADMIN
- USERADMIN
- SYSADMIN
- PUBLIC.
For more details on each of the inbuilt system-defined roles visit the Snowflake documentation.
What are custom roles?
Custom roles are any roles different from the system-defined roles. They can be created by the USERADMIN role or higher as well as by any role to which the CREATE ROLE privilege has been granted to.
RBAC model approaches
There are two approaches for efficiently deploying an RBAC solution to Snowflake: Basic access role model and functional access role model.
Basic access role model:
This is a model to define a set of Access Roles (AR) to which access privileges will be granted to specific Snowflake account objects. Some roles may be granted read-only access (R) to certain objects in each schema and database, while others might be granted read, write and control (RWC).
In the diagram below:
- User 1 and User 2 have read, write and control privileges to all the Finance Database objects.
- User 3 has read-only access to a specific SCHEMA1 on the Finance Database and RWC to the SCHEMA2 on the HR Database.
- User 4 has RWC access to the SCHEMA2 on the HR Database.
This type of Access Role model is recommended for small deployments. Otherwise, it becomes difficult to operate, 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 challenging to implement when user-to-role assignments are managed by an external Identity Provider (IdP) due to the role ownership and provisioning requirements for Snowflake’s System for Cross-domain Identity Management (SCIM) integration to work.
Functional access role model:
With the implementation of Functional Roles we can remediate the limitations faced when using a basic access role model where user-to-role assignments are managed by an external IdP. Now, Functional Roles are the ones being mapped to business functions within the organisation (i.e HR, Finance, etc.) and are typically assigned to users based on their job functions. The external IdP can manage functional role assignments to users and at the same time, Snowflake manages the functional-to-access role hierarchy and access role privilege grants for database objects. It is considered a best practice for developing a custom role security model using Snowflake’s RBAC model.
Process automation
When thinking about the two solutions described above, though they sound straightforward, they can become very time-consuming to implement — more so if your organisation requires multiple role creation.
The implementation of either of the two solutions should be automated. One method to achieve this is using stored procedures in Snowflake which can be run through tasks so they can be scheduled or run via DBT. This will depend on the use case and the tools being used within the architecture when implementing the solution.
Every step of the RBAC process can be automated using a unique stored procedure; however, the recommendation is to split the process into different stored procedures in order to isolate each specific functionality. This will result in de-coupled code which is easy to understand, manage and maintain.
Below are the main steps required to automate the process (one stored procedure per step). Note that more steps can be added depending on the use case, for example, grant usage on warehouses, etc.
1. Create custom Access Roles.
2. Grant custom Access Roles to custom Functional Roles (from IdP).
3. Grant Objects (privileges) to custom Access Roles.
Note: IdP will manage Functional Roles creation and user assignment.
When granting privileges on schema objects like tables or views, the role must also have the USAGE privilege on the parent database and schema.
Privileges granted to a particular role are automatically inherited by any other roles to which the role is granted, as well as any other higher-level roles within the role hierarchy.
When creating a custom role its owner is the role that was being used while creating it. For example, if we are creating CUSTOM_ROLE_A while using SECURITYADMIN, the owner will be SECURITYADMIN. Only the SECURITYADMIN and ACCOUNTADMIN system roles have the MANAGE GRANTS privilege; however, this privilege can be granted to custom roles.
For this, it is recommended that organization functional areas have custom admin roles — for example, FR_ACCOUNTADMIN with ACCOUNTADMIN privileges.
The following example shows how the process is implemented:
Custom Access Roles are to be defined depending on the use case. A table containing metadata of the objects (i.e. tables) is one way to tackle this implementation and is going to be used for this demonstration.
Assume there is two possible types of tables with two possible sensitivities of the data and this that will drive access. PII will have read-only privileges, NOPII will have read, write and control privileges. Again, this is just an example and should be determined by the use case.
Metadata table:
Using the information provided in the metadata table we can create the following access roles (all combinations of Type and Sensitivity):
CREATE ROLE IF NOT EXIST TYPE_A_R; (from table1 and table4)
CREATE ROLE IF NOT EXIST TYPE_B_RWC; (from table2)
CREATE ROLE IF NOT EXIST TYPE_A_RWC; (from table3)Note: the table with the metadata information can be stored in Snowflake and accessed by the stored procedure to dynamically create the custom Access Roles inside the store procedure.
Once we create the three custom Access Roles we should make sure those ARs do not end up as orphans; therefore, we need to grant them a chosen Functional Role. In this solution every custom Access Role should be granted at least to the predefined custom FR_ACCOUNTADMIN Functional Role.
GRANT ROLE TYPE_A_R TO ROLE FR_ACCOUNTADMIN;
GRANT ROLE TYPE_B_RWC TO ROLE FR_ACCOUNTADMIN;
GRANT ROLE TYPE_A_RWC TO ROLE FR_ACCOUNTADMIN;Additionally, the ARs should be mapped to the Functional Roles created by the IdP. This mapping should exist in Snowflake (it could be part of another mapping table) to be accessed by the stored procedure and dynamically grant access.
GRANT ROLE TYPE_A_R TO ROLE FR_HR;
GRANT ROLE TYPE_A_R TO ROLE FR_FIN;
GRANT ROLE TYPE_B_RWC TO ROLE FR_HR;
GRANT ROLE TYPE_A_RWC TO ROLE FR_HR;Once we created the three custom Access Roles and granted them to the corresponding Functional Roles we need to grant the corresponding privileges to them (access to tables).
GRANT SELECT ON TABLE Table1 TO ROLE TYPE_A_R;
GRANT SELECT ON TABLE Table4 TO ROLE TYPE_A_R;
GRANT ALL ON TABLE Table2 TO ROLE TYPE_B_RWC;
GRANT ALL ON TABLE Table3 TO ROLE TYPE_A_RWC;It is important to mention that not only do we need to grant the privileges on the table, but as mentioned above, we also need to grant usage on the schema and database where those tables belong. It is recommended to grant usage to the database and schema to the Functional Role. Granting usage on database and schema does not necessarily mean the user will have access to the objects within it. This step of granting usage on databases and schemas can be included in any of the stored procedures.
After the example implementation, access should look like this:
User 1: Because he is part of FR_FIN Functional Role he can perform All on table3, All on table2 and Select on table1 and table4.
User 2: Because he is part of FR_FIN he can perform Select on table1 and table4.
Conclusion
This article discusses the implementation of access control leveraging the Snowflake Role-Based Access Control method illustrated with several examples and use cases. It is also intended to enable new Snowflake users to get up to speed with RBAC and effectively build solutions for their organizations. The approaches mentioned above come from my own experience, and while it is not the only way of working with access control, we have successfully used RBAC to structure many Snowflake environments.
Slalom is a global consulting firm that helps people and organizations dream bigger, move faster, and build better tomorrows for all. Learn more and reach out today.
