Power BI Row-Level Security in a Fabric World: One Policy, Many Engines

1. The Core Problem: RLS Sprawl in Fabric

Fabric gives you multiple engines in one workspace:

• Lakehouse (Delta tables, Direct Lake)
• Warehouse (SQL endpoint)
• Semantic models (Power BI)

If each of these has its own RLS logic:

• You duplicate filters in DAX, SQL, and sometimes even in ETL.
• You inevitably mis‑align one of them and leak data.
• Every organizational change (new region, manager, team) means updating 3–5 places.

The goal is simple:

Define security rules once, consume them everywhere.

That means:

1. Central security tables (who can see what).
2. A consistent identity model (UPN / AAD object IDs).
3. Minimal engine‑specific logic.

2. Design Principles for Centralized RLS in Fabric

Before we touch code, lock in a few principles.

2.1 Model security as data, not code

Instead of hard‑coded filters like:

[Region] = "Poland" && USERPRINCIPALNAME() = "anna@contoso.com"

…create a security dimension:

• DimUser
  • UserKey (surrogate key)
  • UserUPN
  • RegionKey
  • DepartmentKey
  • IsManager (bit)

…and link it to your business dimensions (DimRegion, DimDepartment).

Your RLS then becomes "filter facts by what the user is allowed to see" instead of embedding business rules in DAX.

2.2 Keep identities consistent

Pick one identity field as the canonical key:

• Usually UserUPN (e.g. anna@contoso.com).
• In more advanced setups, Azure AD object ID.

Use the same identity:

• In Lakehouse tables.
• In Warehouse tables.
• In the semantic model (USERPRINCIPALNAME() mapping to UserUPN).

2.3 Push security as far upstream as needed, but no further

Typical pattern:

1. Lakehouse: store and maintain security tables.
2. Warehouse: expose security tables via SQL views.
3. Semantic model: apply RLS roles referencing those tables.

You only push row filters into the Warehouse (SQL) when non‑Power‑BI tools need secure access.

3. Building a Central Security Model in the Lakehouse

We’ll start in the Lakehouse, because that’s where your curated Delta tables usually live.

3.1 Create security tables

At minimum, design:

• sec.User – one row per user.
• sec.Role – logical roles (Region Manager, HR, Finance, etc.).
• sec.UserRole – bridge table (many‑to‑many).
• sec.UserRegion (or sec.UserEntity) – what data slices each user can see.

Example SQL (Fabric Warehouse, but conceptually the same in Lakehouse SQL analytics endpoint):

CREATE SCHEMA sec;

CREATE TABLE sec.[User] (
    UserKey       INT IDENTITY(1,1) PRIMARY KEY,
    UserUPN       NVARCHAR(256) NOT NULL,
    DisplayName   NVARCHAR(256) NULL,
    IsActive      BIT NOT NULL DEFAULT 1
);

CREATE TABLE sec.[Role] (
    RoleKey       INT IDENTITY(1,1) PRIMARY KEY,
    RoleName      NVARCHAR(100) NOT NULL
);

CREATE TABLE sec.UserRole (
    UserKey       INT NOT NULL,
    RoleKey       INT NOT NULL,
    CONSTRAINT PK_UserRole PRIMARY KEY (UserKey, RoleKey)
);

CREATE TABLE sec.UserRegion (
    UserKey       INT NOT NULL,
    RegionKey     INT NOT NULL,
    CONSTRAINT PK_UserRegion PRIMARY KEY (UserKey, RegionKey)
);

Populate these from HR / AD exports using Dataflows Gen2 or notebooks.

3.2 Automate user mapping from Entra ID

If you have a user directory in Entra ID, you can sync it into Lakehouse with PySpark (simplified example):

from pyspark.sql.functions import col

# Assume df_ad_users loaded from Graph API or exported CSV

( df_ad_users
    .select(
        col("userPrincipalName").alias("UserUPN"),
        col("displayName").alias("DisplayName")
    )
    .write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("sec.User_staging")
)

spark.sql("""
MERGE INTO sec.[User] AS tgt
USING sec.User_staging AS src
   ON tgt.UserUPN = src.UserUPN
WHEN MATCHED THEN
    UPDATE SET tgt.DisplayName = src.DisplayName,
               tgt.IsActive    = 1
WHEN NOT MATCHED THEN
    INSERT (UserUPN, DisplayName, IsActive)
    VALUES (src.UserUPN, src.DisplayName, 1);
""")

The important part: UserUPN is now the bridge between Fabric and AAD.

4. Exposing Security to the Warehouse and Semantic Model

Once the security tables are in the Lakehouse, you have two options:

1. Use them directly from the Lakehouse (Direct Lake / import).
2. Surface them via Warehouse views for SQL‑centric access.

4.1 Create Warehouse views over Lakehouse tables

In Fabric, you can create a Warehouse that references Lakehouse tables via shortcuts or external tables. Then define views that encapsulate security logic.

Example: a view that returns only active users and their regions.

CREATE VIEW sec.vwUserRegion AS
SELECT
    u.UserUPN,
    r.RegionKey,
    r.RegionName
FROM sec.[User] u
JOIN sec.UserRegion ur
    ON u.UserKey = ur.UserKey
JOIN dim.Region r
    ON ur.RegionKey = r.RegionKey
WHERE u.IsActive = 1;

This view:

• Is your single source of truth for user‑to‑region mapping.
• Can be used by:
  • Power BI semantic models.
  • SQL‑based tools (e.g. downstream apps, ad‑hoc queries).

4.2 Import security tables into the semantic model

In Power BI Desktop (or in the Fabric semantic model designer):

1. Connect to the Warehouse or Lakehouse.
2. Import:
   • sec.vwUserRegion (or equivalent).
   • Any other security views (user‑to‑department, role membership, etc.).
3. Create relationships:
   • vwUserRegion[RegionKey] → DimRegion[RegionKey] (single, filter both).
   • No direct relationship from vwUserRegion to fact tables.

This keeps your security logic in dimensions, not facts.

5. Defining RLS Once in the Semantic Model

Now we layer RLS on top of the security tables.

5.1 Basic region‑based RLS role

In the semantic model, create a role, e.g. RLS_Region, with a DAX filter on vwUserRegion:

-- Table: vwUserRegion
[UserUPN] = USERPRINCIPALNAME()

Because vwUserRegion filters DimRegion, and DimRegion filters your fact tables, the effect is:

• Each user only sees regions listed in vwUserRegion for their UPN.

Nothing else needs a filter expression.

5.2 Combining multiple security dimensions

If you also have department‑level restrictions, you can:

• Create vwUserDepartment.
• Relate it to DimDepartment.
• Add a second filter in the same role.

-- Table: vwUserRegion
[UserUPN] = USERPRINCIPALNAME()

-- Table: vwUserDepartment
[UserUPN] = USERPRINCIPALNAME()

Now the effective RLS is the intersection:

• Only rows where both region and department are allowed.

5.3 Handling managers and row expansion

Managers often need to see their team’s data. Model this as a separate mapping table:

• sec.UserHierarchy:
  • ManagerUserKey
  • EmployeeUserKey

Create a view that resolves the final allowed entities for each manager.

CREATE VIEW sec.vwManagerUserRegion AS
SELECT DISTINCT
    m.UserUPN      AS ManagerUPN,
    r.RegionKey,
    r.RegionName
FROM sec.UserHierarchy h
JOIN sec.[User] m
    ON h.ManagerUserKey = m.UserKey
JOIN sec.[User] e
    ON h.EmployeeUserKey = e.UserKey
JOIN sec.UserRegion ur
    ON e.UserKey = ur.UserKey
JOIN dim.Region r
    ON ur.RegionKey = r.RegionKey;

In the semantic model, you can then:

• Use either vwUserRegion (for direct permissions),
• Or vwManagerUserRegion (for manager views),
• Or union them in a single view if managers should also see their own region.

RLS filter:

-- Table: vwEffectiveUserRegion
[UserUPN] = USERPRINCIPALNAME()

Where vwEffectiveUserRegion is a view that combines direct and manager‑derived rights.

6. Making It Work Across Lakehouse, Warehouse, and Direct Lake

The nice part of Fabric is that once your security tables live in Delta, you can reuse them across engines.

6.1 Direct Lake semantic model

For Direct Lake models over a Lakehouse:

1. Ensure the security tables (sec.*) are in the same Lakehouse as your facts/dims.
2. Include them in the model.
3. Create relationships and RLS roles exactly as above.

Direct Lake respects RLS defined in the semantic model, so:

• No extra work is needed at the Lakehouse level.
• You still centralize the logic in sec tables.

6.2 SQL‑based access to the Warehouse

If you have consumers querying the Warehouse directly (e.g. analysts using SSMS or applications using ODBC), you might need RLS at the SQL layer as well.

Pattern:

1. Use SQL Server Row-Level Security‑style predicates via security policies (when available in your Fabric Warehouse environment), or
2. Expose secured views that join facts to sec tables.

Example: secured sales view using a function that maps the current user.

CREATE FUNCTION sec.fnUserRegionPredicate(@UserUPN NVARCHAR(256))
RETURNS TABLE
AS
RETURN
(
    SELECT RegionKey
    FROM sec.vwUserRegion
    WHERE UserUPN = @UserUPN
);
GO

CREATE VIEW dbo.vwSales_Secured AS
SELECT s.*
FROM fact.Sales s
JOIN sec.fnUserRegionPredicate(SESSION_CONTEXT(N'UserUPN')) f
    ON s.RegionKey = f.RegionKey;

You’d then need to set SESSION_CONTEXT('UserUPN') from the caller. In Fabric today this pattern is more realistic for controlled application access than ad‑hoc user queries; the key idea is that the same sec.vwUserRegion drives both SQL and Power BI.

6.3 Avoiding double‑filtering

If you apply RLS in both the Warehouse and semantic model, you can end up with:

• Filters being applied twice.
• Confusing error messages when debugging.

Practical rule:

• For Power BI‑only usage: keep RLS in the semantic model; Warehouse tables/views stay open.
• For mixed tooling (Power BI + direct SQL):
  • Implement base‑level security in Warehouse views.
  • Make semantic model RLS as thin as possible (or rely on the secured views and keep model RLS minimal).

7. Operationalizing: Governance, Testing, and Change Management

Centralizing RLS is only useful if it’s maintainable.

7.1 Governance checklist

Put these under explicit ownership:

• Schema ownership for sec tables/views.
• Process for adding/removing users and roles.
• Approval workflow when changing security logic.

7.2 Testing RLS in Fabric

In Power BI / Fabric semantic model designer:

• Use View as to test different roles.
• Maintain a small set of test users:
  • One per role type.
  • One with no access (to test denials).

Testing pattern:

1. Pick a test user.
2. Check what rows they should see in sec.vwUserRegion via SQL.
3. "View as" that user in the semantic model and confirm the report matches.

7.3 Handling organizational change

When a region is split or teams are reorganized:

• Only sec tables should change (mappings, not DAX).
• Reports and RLS roles stay untouched.

This is the main payoff of centralization: business change becomes data change, not code change.

8. One Concrete Takeaway

Pick a single high‑value Power BI dataset and move its RLS logic into a dedicated sec schema (tables + views) in your Lakehouse or Warehouse. Wire your semantic model to those tables and reduce your role definitions to simple UserUPN = USERPRINCIPALNAME() filters. Once that works end‑to‑end, you’ve got a proven pattern you can replicate across Fabric, instead of rewriting RLS separately for every model, Warehouse, and Lakehouse.