RBAC in Snowflake

Introduction

As organizations increasingly move their data and analytics to the cloud, data security and access control become critical concerns. Snowflake, a popular cloud data platform, provides robust security features including Role-Based Access Control (RBAC). RBAC allows you to control access to objects and capabilities in Snowflake based on the roles granted to users.

This article discusses RBAC in Snowflake. It explains how RBAC works and provides examples to help you get familiar with Snowflake’s RBAC.

What is Role-Based Access Control?

Role-Based Access Control determines who can access a computer or resource based on user roles in a company. RBAC is a method of controlling access to information.

This system assigns permissions to users based on their roles within the organization. It helps ensure that only authorized individuals can access certain data or resources.

In RBAC, you associate roles with permissions, and assign users to suitable roles. This simplifies access management. Rather than assigning permissions to each user individually, you can assign permissions to roles. Users of the Snowflake database belong to those roles as needed.

RBAC offers several benefits:

• Simplified access management: Admins can assign permissions to roles rather than individual users
• Improved security: Users only have access to the specific resources their role allows
• Reduced admin overhead: With fewer direct assignments, RBAC reduces the management burden
• Regulatory compliance: RBAC makes it easier to implement and demonstrate access controls to meet regulatory requirements

How RBAC Works in Snowflake

Snowflake manages all access control through roles and privileges. A role assigns a named collection of privileges to users. Privileges grant the ability to perform certain actions on securable objects like databases, schemas, tables, and so on.

Here are the key concepts in Snowflake’s RBAC model:

• Roles: A role is a collection of privileges. Users or other roles can assign roles.
• Privileges: A privilege grants the ability to perform a certain action, like creating a database or querying a table. Roles map a set of privileges to the users.
• Securable objects: An entity like a database, schema, table, or view that has controlled access. We give privileges to secure objects.
• Access control: Roles are groups that control access by giving permissions and assigning them to users. Admin users assign roles permissions and assign roles to users.
• Role hierarchy: Database admin can assign roles to other roles, creating a hierarchy. Child roles inherit the privileges of their parent roles.

Configuring RBAC in Snowflake

To implement RBAC in Snowflake, you’ll follow these high-level steps:

1. Create roles
2. Granting privileges to roles
3. Assign roles to users

Let’s walk through an example. Suppose we have a simple database with sales data that different users need to access. We’ll create an RBAC configuration to control access.

First, let’s create the database and table:

CREATE DATABASE sales_db;
CREATE TABLE sales_db.public.orders (
order_id INT,
amount DECIMAL(10,2)
);

Next we’ll create some roles:

CREATE ROLE admin;
CREATE ROLE analyst;
CREATE ROLE reporter;

Now we can assign privileges to the roles:

-- admins can manage the database
GRANT CREATE DATABASE ON ACCOUNT TO ROLE admin;
GRANT CREATE SCHEMA ON DATABASE sales_db TO ROLE admin;
GRANT CREATE TABLE ON ALL SCHEMAS IN DATABASE sales_db TO ROLE admin;
-- analysts can query the data
GRANT USAGE ON DATABASE sales_db TO ROLE analyst;
GRANT USAGE ON SCHEMA sales_db.public TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA sales_db.public TO ROLE analyst;
-- reporters can run SELECT queries 
GRANT USAGE ON DATABASE sales_db TO ROLE reporter;
GRANT USAGE ON SCHEMA sales_db.public TO ROLE reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA sales_db.public TO ROLE reporter;

Finally, let’s assign roles to users:

CREATE USER michelle PASSWORD = 'strong_password';
CREATE USER frank PASSWORD = 'another_strong_password';
CREATE USER lisa PASSWORD = 'yet_another_strong_password';
GRANT ROLE admin TO USER michelle;
GRANT ROLE analyst TO USER frank;
GRANT ROLE reporter TO USER lisa;

Michelle has admin privileges, Frank has analyst privileges, and Lisa has reporter privileges based on their assigned roles.

We can test it out:

-- connect as michelle 
USE ROLE ADMIN;
CREATE TABLE sales_db.public.customers (customer_id INT); -- success
-- connect as frank
USE ROLE ANALYST;
SELECT * FROM sales_db.public.orders; -- success
CREATE TABLE sales_db.public.customers (customer_id INT); -- fails, analysts can only query 
-- connect as lisa
USE ROLE REPORTER;
SELECT * FROM sales_db.public.orders; -- success 
CREATE TABLE sales_db.public.customers (customer_id INT); -- fails, reporters can only query
Each user can only do what their role allows them to do, following the rule of least privilege."

Role Hierarchy

Organizations can organize snowflake roles into hierarchies, where a child role inherits the privileges of its parent role. This allows you to define broad access at higher levels and then fine-tune access by creating more specific child roles under them.

For example, let’s say we wanted to create a “read-only” role that can access multiple databases. We can create a parent role with broad SELECT privileges, then create child roles for specific access:

CREATE ROLE read_only;
GRANT USAGE ON DATABASE sales_db TO ROLE read_only;
GRANT USAGE ON SCHEMA sales_db.public TO ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA sales_db.public TO ROLE read_only;
GRANT USAGE ON DATABASE marketing_db TO ROLE read_only;
GRANT USAGE ON SCHEMA marketing_db.public TO ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA marketing_db.public TO ROLE read_only;
CREATE ROLE sales_reader;
GRANT ROLE read_only TO ROLE sales_reader;
CREATE ROLE marketing_reader;
GRANT ROLE read_only TO ROLE marketing_reader;
Now the sales_reader and marketing_reader roles inherit SELECT privileges from the read_only parent role. We can assign them to users as needed:

GRANT ROLE sales_reader TO USER frank;
GRANT ROLE marketing_reader TO USER lisa;

Frank can access the sales database for information. Lisa can access the marketing database for information. They both have the read-only parent role.

Using role hierarchies judiciously can make RBAC configurations easier to manage and understand. Build your hierarchy starting with broad roles, then create child roles for more granular, specific access.

Centralizing and Simplifying RBAC Management

Snowflake has a good system for managing roles and privileges. However, it can be difficult to handle them in a large organization with many users and databases. This is where third-party tools like DataSunrise can help.

DataSunrise offers a suite of tools to enhance and simplify data security and compliance in Snowflake. Their intuitive web interface makes it easy to manage RBAC configurations across all your Snowflake databases and warehouses. You can manage users, roles, and privileges from a single console.

In addition to RBAC management, DataSunrise provides other critical security features for Snowflake, including:

• Data encryption and tokenization
• Dynamic data masking
• SQL firewall for real-time monitoring and policy enforcement
• Data audit and reporting to meet compliance requirements

If you’re looking for ways to enhance security and compliance in your Snowflake environment, consider exploring tools like DataSunrise. We offer a free demo so you can see DataSunrise capabilities in action.

Conclusion

Role-Based Access Control is a powerful tool for managing data access in Snowflake. RBAC allows you to control access in detail by giving privileges to roles.

Subsequently, the database administrator allocates these roles to the users. This follows the principle of least privilege. Snowflake’s implementation of RBAC is flexible and robust, supporting role hierarchies for even more granular control.

While RBAC in Snowflake is natively robust, enterprise-scale deployments can still become complex to manage. Third-party tools like DataSunrise can help to simplify and centralize RBAC management across all your Snowflake instances.

We hope this article has been a helpful introduction to RBAC in Snowflake. To learn more, consult the Snowflake documentation.