End-to-End Dynamic Security Architecture

In modern global enterprises, maintaining static RLS roles (e.g., creating 50 separate roles for 50 country managers) is an administrative failure. Whenever a country manager resigns or moves to another territory, a BI developer would have to manually edit the .pbix file and republish it.

The enterprise standard is Dynamic Row-Level Security driven by a Security Bridge Table.

1. Dynamic RLS Architecture Overview

Dynamic RLS uses a data-driven approach where user permissions are stored inside an internal relational database table:

2. The Single Universal DAX Security Filter

With this architecture in place, you configure EXACTLY ONE ROLE in the entire model (e.g., Dynamic_User_Security).

The Filter Formula on Security_UserRegionMapping:

How the Filter Propagates:

1. 1
   When Aarav logs into the Power BI Service, USERPRINCIPALNAME() returns aarav.sharma@company.com.
2. 2
   The Security_UserRegionMapping table is filtered to only his rows (RegionKey = 1 and RegionKey = 2).
3. 3
   This filter naturally propagates through the relationship into Dim_Region and downstream into Fact_Sales!
4. 4
   Aarav sees both North and East data automatically!

3. Handling Superusers (CEO / Executive Override)

Executives need to see ALL data across all regions. We can build an override into the dynamic DAX filter: