Understanding Snowflake RBAC

In Snowflake, access control is managed through roles. Roles are assigned specific privileges that define what actions they can perform and what data they can access. Users are then granted roles, and through these roles, they inherit the privileges necessary to perform their job functions. This model allows for a highly customizable and secure configuration, ensuring that users only have the access they need.

Tip: Privileges are granted to Roles and NOT to users. You may find this in the Snowflake Core exam.

Let’s go through some of the core concepts regarding RBAC and then see that in action later.

• Role: A role is an entity that groups privileges. Roles can be assigned to users or other roles. Snowflake also has some system-defined roles. You can read more about them here.
• Privileges: These are specific permissions to perform actions or access objects within Snowflake. Privileges can be on objects (like databases, schemas, and tables) or systems (like managing roles or warehouses).
• Role Hierarchy: Role hierarchies allow roles to inherit privileges from other roles. VERY IMPORTANT. A well-defined role hierarchy is very critical in streamlining access management.
• User: An entity associated with either a person or a program a.k.a. machine user.

Setting up RBAC in Snowflake

Below are some hands-on examples demonstrating the setup of RBAC in Snowflake. Let’s dig in.

-- Create two roles as shown below
CREATE ROLE data_analyst;
CREATE ROLE data_engineer;

One thing to note is that you need to have at least a SYSADMINrole OR sufficient privileges to create new roles. Also, as a best practice, put all the custom roles under SYSADMIN role.

-- Grant usage privilege on a database to the data_analyst role
GRANT USAGE ON DATABASE db1 TO ROLE data_analyst;

-- Grant usage & select privileges on all tables in a schema
GRANT USAGE ON SCHEMA schema1 TO ROLE data_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA db1.schema1 TO ROLE data_analyst;

-- Grant ALL privileges on a specific table to data_engineer role
GRANT ALL PRIVILEGES ON TABLE db1.schema1.table1 TO ROLE data_engineer;

Now that we have created and configured the required roles and granted privileges, let’s assign them to the users to use them.

-- Assign data_analyst role
GRANT ROLE data_analyst TO USER analyst_user;

-- Assign data_engineer role
GRANT ROLE data_engineer TO USER engineer_user;

QUIZ TIME…

Based on the above configuration, which of the following statement(s) could be true?

• Users analyst_user and engineer_usercan access table db1.schema1.table1
• Only engineer_user can access table db1.schema1.table1
• User engineer_user can access all tables under db1.schema1
• User analyst_user can access all tables under db1.schema1

Tip: This is another similar question that you may find in SnowPro Core Exam.

Now let’s talk about my favorite and most powerful configuration, i.e. Role Hierarchy.

-- create a team role
CREATE ROLE data_team;

-- Grant data_analyst and data_engineer roles to the team role
GRANT ROLE data_analyst TO ROLE data_team;
GRANT ROLE data_engineer TO ROLE data_team;

-- Grant team role to a team member
GRANT ROLE data_team TO USER user1;

This is a very simplistic configuration. However, it explains the concept of how you should:

• create separate roles for specific operations
• create a top-level role and grant required roles to the top-level role based on the privileges required
• assign the top-level role to the user(s)

Creating roles in this way allows you to repurpose some roles helps in ease of management, avoids redundancy, and implements the least privilege principle.

In the end, below are some of the helpful commands you can use to get information about RBAC-related setup.

-- show current role for your user
SELECT CURRENT_ROLE();

-- listing roles available to the user
SHOW GRANTS TO USER <user_name>;

-- list permissions a specific role has
SHOW GRANTS OF ROLE <role_name>;

-- query information schema for roles granted to current user
SELECT * FROM information_schema.enabled_roles;

-- query specific privilges of a role
SELECT * FROM information_schema.role_privileges WHERE grantee = 'role_name';

The below screenshots explain the system-defined roles and role hierarchy.

Courtesy of docs.snowflake.com

Courtesy of docs.snowflake.com

RBAC in Snowflake is a powerful tool for managing access control. By carefully planning and implementing roles and privileges, organizations can ensure that their data is secure and accessible only to authorized users.

Reference: https://docs.snowflake.com/en/user-guide/security-access-control-overview

If you like this article, please follow me on LinkedIn.