Out of the Lake, Into The Clouds

May the better Data Platform Wins!

Recent Posts

The Power of Roles for Data Segregation

The Power of Roles for Data Segregation

A robust data governance model is crucial for any organization. You need to ensure that users can only access the data they are authorized to see, creating a secure and well-managed data environment.

This demonstration will follow on from Mahdi Askari’s post showcasing how Snowflake’s Role-Based Access Control (RBAC) provides a powerful and flexible way to achieve data isolation and controlled data access.

We will use the standard TPC-H dataset, accessed via a shared database, to create a realistic scenario. We’ll build a dedicated governance layer using views, and then apply permissions to two distinct business roles.

We’ll see how these roles provide separate, isolated views of the data, how we can enforce that isolation by disabling secondary roles, and how they can be combined for higher-level analysis only through explicit permission

The Scenario

Imagine two departments in a company:

  1. Finance: Analysts in this department are concerned with revenue, pricing, and order totals. They need access to customer orders and line item data.
  2. Logistics: Analysts in this department manage the supply chain. They need access to information about suppliers and the parts they provide.

Our goal is to give each department access to only the data they need, prevent them from combining these permissions on their own, and allow a manager to see the combined picture.

Snowflake RBAC Demonstration

Here is the step-by-step SQL code to set up and demonstrate this scenario in a Snowflake worksheet.

1. Setup: Prepare the Environment

First, we’ll use the ACCOUNTADMIN role to create a new database and schema that will serve as our governance layer.

-- Use the ACCOUNTADMIN role for initial setup
USE ROLE ACCOUNTADMIN;

-- Create a new database for our demo objects
CREATE OR REPLACE DATABASE TPCH_SAMPLE;
USE DATABASE TPCH_SAMPLE;

-- Create a schema to hold the views
CREATE OR REPLACE SCHEMA DEMO_VIEWS;
USE SCHEMA DEMO_VIEWS;

-- Create a warehouse to run queries
CREATE OR REPLACE WAREHOUSE DEMO_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

USE WAREHOUSE DEMO_WH;

2. Create Views as a Governance Layer

Instead of granting access directly to the shared data, we’ll create views. This could easily be done on base tables but since this is an open dataset, this is just helps illustrate the example without having to load any additional data.

-- Create views pointing to the shared TPC-H data
-- This assumes the share is named SFSALESSHARED_SFC_SAMPLES_AU_SAMPLE_DATA
-- and you have the necessary import privileges.
CREATE OR REPLACE VIEW ORDERS AS SELECT * FROM SFSALESSHARED_SFC_SAMPLES_AU_SAMPLE_DATA.TPCH_SF1.ORDERS;
CREATE OR REPLACE VIEW LINEITEM AS SELECT * FROM SFSALESSHARED_SFC_SAMPLES_AU_SAMPLE_DATA.TPCH_SF1.LINEITEM;
CREATE OR REPLACE VIEW CUSTOMER AS SELECT * FROM SFSALESSHARED_SFC_SAMPLES_AU_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
CREATE OR REPLACE VIEW PART AS SELECT * FROM SFSALESSHARED_SFC_SAMPLES_AU_SAMPLE_DATA.TPCH_SF1.PART;
CREATE OR REPLACE VIEW SUPPLIER AS SELECT * FROM SFSALESSHARED_SFC_SAMPLES_AU_SAMPLE_DATA.TPCH_SF1.SUPPLIER;
CREATE OR REPLACE VIEW PARTSUPP AS SELECT * FROM SFSALESSHARED_SFC_SAMPLES_AU_SAMPLE_DATA.TPCH_SF1.PARTSUPP;
CREATE OR REPLACE VIEW NATION AS SELECT * FROM SFSALESSHARED_SFC_SAMPLES_AU_SAMPLE_DATA.TPCH_SF1.NATION;

3. Create the Business Roles

Now, we’ll create our two distinct roles and grant them to SECURITYADMIN so it can manage them.

-- Create the Finance Analyst role
CREATE OR REPLACE ROLE FINANCE_ANALYST;

-- Create the Logistics Analyst role
CREATE OR REPLACE ROLE LOGISTICS_ANALYST;

-- Grant the new roles to SECURITYADMIN so it has permission to grant them to users.
-- This is a key step to avoid the "role does not exist or not authorized" error.
GRANT ROLE FINANCE_ANALYST TO ROLE SECURITYADMIN;
GRANT ROLE LOGISTICS_ANALYST TO ROLE SECURITYADMIN;

4. Grant Selective Permissions to Each Role

This is the core of data isolation. We grant SELECT privileges on the newly created views to each role.

-- Grant permissions to the FINANCE_ANALYST role on the views
GRANT SELECT ON VIEW TPCH_SAMPLE.DEMO_VIEWS.ORDERS TO ROLE FINANCE_ANALYST;
GRANT SELECT ON VIEW TPCH_SAMPLE.DEMO_VIEWS.LINEITEM TO ROLE FINANCE_ANALYST;
GRANT SELECT ON VIEW TPCH_SAMPLE.DEMO_VIEWS.CUSTOMER TO ROLE FINANCE_ANALYST;
GRANT USAGE ON DATABASE TPCH_SAMPLE TO ROLE FINANCE_ANALYST;
GRANT USAGE ON SCHEMA TPCH_SAMPLE.DEMO_VIEWS TO ROLE FINANCE_ANALYST;
GRANT USAGE ON WAREHOUSE DEMO_WH TO ROLE FINANCE_ANALYST;


-- Grant permissions to the LOGISTICS_ANALYST role on the views
GRANT SELECT ON VIEW TPCH_SAMPLE.DEMO_VIEWS.PART TO ROLE LOGISTICS_ANALYST;
GRANT SELECT ON VIEW TPCH_SAMPLE.DEMO_VIEWS.SUPPLIER TO ROLE LOGISTICS_ANALYST;
GRANT SELECT ON VIEW TPCH_SAMPLE.DEMO_VIEWS.PARTSUPP TO ROLE LOGISTICS_ANALYST;
GRANT USAGE ON DATABASE TPCH_SAMPLE TO ROLE LOGISTICS_ANALYST;
GRANT USAGE ON SCHEMA TPCH_SAMPLE.DEMO_VIEWS TO ROLE LOGISTICS_ANALYST;
GRANT USAGE ON WAREHOUSE DEMO_WH TO ROLE LOGISTICS_ANALYST;

5. Demonstrate Data Isolation

Let’s create a user and assign them these roles to test the isolation by querying the views.

-- Create a test user (or use your own) and grant them the roles
USE ROLE SECURITYADMIN;
CREATE OR REPLACE USER DEMO_USER PASSWORD='<password>' DEFAULT_ROLE=FINANCE_ANALYST DEFAULT_SECONDARY_ROLES = ();
GRANT ROLE FINANCE_ANALYST TO USER DEMO_USER;
GRANT ROLE LOGISTICS_ANALYST TO USER DEMO_USER;

CREATE OR REPLACE SESSION POLICY prod_env_session_policy
  SESSION_IDLE_TIMEOUT_MINS = 30
  SESSION_UI_IDLE_TIMEOUT_MINS = 30
  ALLOWED_SECONDARY_ROLES = ()
  COMMENT = 'session policy for user secondary roles ';

ALTER USER DEMO_USER SET SESSION POLICY prod_env_session_policy;

Now, let’s test what this user can see by switching roles.

(In a real scenario, the user DEMO_USER would log in. For this demo, we’ll switch our own role). If you are not changing to the user, run the below for the actual user

ALTER USER <yourusername> SET SESSION POLICY prod_env_session_policy;
-- Switch to the FINANCE_ANALYST role
USE ROLE FINANCE_ANALYST;

-- This query SUCCEEDS because the role has access to the ORDERS view
SELECT O_ORDERKEY, O_TOTALPRICE
FROM TPCH_SAMPLE.DEMO_VIEWS.ORDERS
LIMIT 10;

-- This query FAILS because the role does NOT have access to the SUPPLIER view
SELECT *
FROM TPCH_SAMPLE.DEMO_VIEWS.SUPPLIER
LIMIT 10;

You will receive an error like: “SQL compilation error: Object ‘SUPPLIER’ does not exist or not authorized.” on the second query

-- Now, let's switch roles and try again
USE ROLE LOGISTICS_ANALYST;

-- This query now FAILS because the logistics role can't see the ORDERS view
SELECT O_ORDERKEY, O_TOTALPRICE
FROM TPCH_SAMPLE.DEMO_VIEWS.ORDERS
LIMIT 10;
-- You will get the same "does not exist or not authorized" error.

-- This query now SUCCEEDS because the role has access to the SUPPLIER view
SELECT S_SUPPKEY, S_NAME, S_NATIONKEY
FROM TPCH_SAMPLE.DEMO_VIEWS.SUPPLIER
LIMIT 10;

This clearly shows that the user’s view of the available data changes instantly and completely just by switching their active primary role.

Snowflake can allow multiple roles to have specific roles, but we set a policy above to enforce only one role at a time. If a user tries to do:

USE ROLE LOGISTICS_ANALYST;
USE SECONDARY ROLE ALL;

they will get an error:

“Statement successfully executed. Not all secondary roles are activated due to the session policy associated with the user or account. Use the CURRENT_SECONDARY_ROLES function to see which secondary roles are activated.”

If they tried to run data against both datasets they will get an error:

“Object ‘TPCH_SAMPLE.DEMO_VIEWS.LINEITEM’ does not exist or not authorized.”

6. Combine Roles for Higher-Level Access (The “Right” Way)

Now we show the sanctioned method for a manager to see data from both finance and logistics: a dedicated hierarchical role.

-- Use a higher-level role to create the manager role
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE ROLE SENIOR_MANAGER;

-- Grant the analyst roles to the manager role. This creates a hierarchy.
-- The SENIOR_MANAGER role inherits all permissions from the roles granted to it.
GRANT ROLE FINANCE_ANALYST TO ROLE SENIOR_MANAGER;
GRANT ROLE LOGISTICS_ANALYST TO ROLE SENIOR_MANAGER;

-- Grant this new role to our user
USE ROLE SECURITYADMIN;
GRANT ROLE SENIOR_MANAGER TO USER DEMO_USER;


-- Now, let's test the combined access
USE ROLE SENIOR_MANAGER;

-- As the SENIOR_MANAGER, we can now join views from both domains!
-- Let's find the total price of line items for all suppliers across orders
SELECT
    s.s_name AS supplier_name,
    SUM(l.l_extendedprice * (1 - l.l_discount)) as total_revenue
FROM TPCH_SAMPLE.DEMO_VIEWS.LINEITEM l
JOIN TPCH_SAMPLE.DEMO_VIEWS.SUPPLIER s ON l.l_suppkey = s.s_suppkey
JOIN TPCH_SAMPLE.DEMO_VIEWS.ORDERS o ON l.L_ORDERKEY = o.o_orderkey
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

This query succeeds because the SENIOR_MANAGER role inherits the SELECT privileges on the necessary views.

Why This Can’t Be Done on Databricks

Databricks has a data governance model with Unity Catalog, but its approach is fundamentally different.

  1. No Dynamic Role Switching: The core of the Snowflake demo is executing USE ROLE <rolename>; to instantly change a user’s entire data access context. Databricks does not have this concept of easily switchable, session-level roles that redefine data visibility.
  2. No Concept of Secondary Role Control: The ability to explicitly enable or disable the combining of permissions at the user level via USE_SECONDARY_ROLES is a feature unique to Snowflake’s security model. It provides an extra layer of administrative control over how permissions are exercised in a session.
  3. Focus on Object ACLs, Not Role Hierarchy:

Snowflake: Privileges are granted to roles, and roles are granted to other roles. This creates a natural hierarchy that mirrors an organizational chart (Analyst -> Manager).

Databricks (Unity Catalog): Privileges are granted on securable objects (like tables) to principals (users or groups). While you can create groups that mirror roles, the powerful inheritance model where one role “contains” another is not the primary mechanism. To achieve this, you would typically grant a “manager” group access to a wider set of objects than an “analyst” group.

Since a user inherits all permissions on login, it is challenging to provide data segregation without seperate accounts or logins.

Conclusion

For use cases centered around creating distinct, isolated data environments that map to an organizational structure, Snowflake’s RBAC model is exceptionally intuitive. The ability to create hierarchical roles, allow users to dynamically switch between them, and explicitly control the use of secondary roles provides a seamless and highly governable way to manage data access.

While Databricks Unity Catalog offers access control, it solves the problem differently. The Snowflake approach, as demonstrated, allows for direct for implementing organization-based data segregation and aggregation with strict administrative oversight.

In my next blog posts, I will focus on other unique Snowflake governance characteristics that are critical for enterprise security and controls

Posted in

Leave a comment