Dynamic Row-Level Security (RLS) with Azure AD, Snowflake & Python

💡 Why Dynamic RLS?

Dynamic Row-Level Security (RLS) tailors what data each user sees—based on who they are and where they belong. It’s a smarter way to keep your data secure, clean, and personalized.

And when this is combined with:

✅ Azure AD for user identity
✅ Snowflake for centralized data warehousing
✅ Python (Snowpark) to automate syncing

…you’ve got yourself a cloud-native, enterprise-grade, self-refreshing RLS engine.

Microsoft Graph API in RLS

🔑 What is Microsoft Graph API?
Graph API allows you to securely access Azure AD data (users, groups, attributes). It’s used to:

• Query group memberships (for access control)
• Fetch user attributes (e.g., userPrincipalName, usageLocation) for filtering
• Support real-time updates (via API calls)

🛠️ Setup Requirements

Here’s what you’ll need to bring this pipeline to life:

• Azure Active Directory (with admin access)
• App registration in Azure AD with:
    – Group.Read.All and User.Read.All permissions
    – Tenant ID, Client ID, Client Secret
• Snowflake with:
    – External Access Integration enabled
    – Network rules configured
• Snowpark Python

🏗️ Step 1: Register an App in Azure AD

1. In Azure Portal, go to Azure Active Directory → App registrations.

2. Register a new app.

3. Note down:
   – Client ID
   – Tenant ID
   – Client Secret

4. Assign API Permissions:
   – Group.Read.All
   – User.Read.All

5. Create a Security Group and assign users to it.

🧊 Step 2: Configure Secrets and External Access in Snowflake

Store Secrets:

CREATE OR REPLACE SECRET CLIENT_ID
  TYPE = GENERIC_STRING
  SECRET_STRING = ‘<your-client-id>’;

CREATE OR REPLACE SECRET CLIENT_SECRET
  TYPE = GENERIC_STRING
  SECRET_STRING = ‘<your-client-secret>’;

Create Network Rule:

CREATE OR REPLACE NETWORK RULE GRAPH_API_NETWORK_RULE
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = (‘graph.microsoft.com’);

External Access Integration:

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION GRAPH_EXTERNAL_ENDPOINTS
  ALLOWED_NETWORK_RULES = (GRAPH_API_NETWORK_RULE)
  ALLOWED_AUTHENTICATION_SECRETS = (CLIENT_ID, CLIENT_SECRET)
  ENABLED = TRUE;

🔐 Step 3: Secure Python Function in Snowflake to fetch Secrets:

Find the get_secret_val python UDF here:

https://github.com/Darshini26/Dynamic-RLS/blob/main/get_secret_val.txt

💻 Step 4: Snowpark Python to Fetch Azure AD Metadata

We’ll now use Snowpark Python to fetch all users in a given Azure AD group along with their userPrincipalName (email) and usageLocation.

Find the complete Snowflake python notebook script here:

https://github.com/Darshini26/Dynamic-RLS/blob/main/Dynamic_RLS.ipynb

Automate the Pipeline
To further streamline this process, you can call the above Python notebook inside a Snowflake procedure and schedule it using Snowflake Tasks. This ensures the RLS table is regularly updated, without manual intervention, for seamless access control and data security.    

Stored Procedure:

CREATE OR REPLACE PROCEDURE TEST_SCHEMA.SP_INSERT_DW_RLS()

RETURNS VARCHAR(16777216)

LANGUAGE SQL

EXECUTE AS CALLER

AS ‘

BEGIN

    — Call notebook within Snowflake

    EXECUTE NOTEBOOK  TEST_SCHEMA.NB_LOAD_RLS_USERS();

    RETURN ”Procedure executed successfully”;

END;

‘;

Task:

create or replace task TEST_SCHEMA.TASK_RLS_GROUPS_LOAD

       warehouse=DEV_NOTEBOOK_WH

       schedule=’USING CRON 15 12 * * 2 America/New_York’

       USER_TASK_TIMEOUT_MS=600000

       COMMENT=’Task runs every Tuesday at 12.15 PM EST’

       as CALL TEST_SCHEMA.SP_INSERT_DW_RLS();

🔍 Step 5: Step by Step implementation in PBI

How RLS Works in Power BI with Snowflake:

Step#1: Define a Security Table (RLS_USERS)

Contains userPrincipalName (email/UPN) and region/country permissions.

Step#2: Create the Location and Sales Tables in Snowflake:

Create the Location table:

Create the Sales fact table:

Find the DDLs and Insert scripts for the above tables here:

https://github.com/Darshini26/Dynamic-RLS/blob/main/DDLs%20and%20Insert%20scripts.sql

Step#3: Connect Power BI to Snowflake

Step#4: Import the tables in the PBI dataset.

Import the below three tables in PBI:

• RLS_USERS
• LOCATION
• SALES

Step# 5: Establish relationships between tables in the model with the below Join:

FROM RLS_USERS R

INNER JOIN LOCATION L ON

R.”usageLocation” = L.COUNTRY_CODE

INNER JOIN SALES S ON

L.REGION = S.REGION;

Step# 6: Implement RLS in Power BI

In Power BI, go to Modeling → Manage Roles

Create New Role as: Region Security from the RLS_Users table -> Switch to the DAX editor and apply the below formula:

[userPrincipalName] = USERPRINCIPALNAME()

Step# 7: Test the RLS

In Power BI, go to Modeling → View as

Check the created Region Security and Enter the email_id/Userprinciple id in the other user

Screenshots captured during testing with users from various regions:

By entering the user-email id who belongs to AMER region have only the specific region visibility.

Similarly, given the email id of the user belongs to APAC region.

Benefits of Implementing RLS in PBI:

• Improved Security – Prevents unauthorized access to sensitive data.
• Better Performance – Reduces data transfer by only fetching relevant rows.
• Easier Management – Centralized security logic in Snowflake.
• Scalability – Supports enterprise-wide role-based access control.
• Real-time Data Access Control – Dynamically applies security policies without requiring dataset refreshes.
• Compliance & Governance – Helps meet regulatory requirements by ensuring data access policies are enforced.
• Reduced Maintenance Overhead – Eliminates the need to create multiple filtered datasets for different users.

🧠 Conclusion

With this pipeline, you’ve unlocked:
✅ Automated syncing of Azure AD users
✅ Real-time updates without manual mapping
✅ Scalable, secure, and dynamic access control

You’ve brought the magic of identity-aware data pipelines into the modern data stack.

Please feel free to reach out to us for your Snowflake solution needs. Cittabase is a Premier  partner with Snowflake.