Row-Level Security vs Object-Level Security in Power BI

1. RLS Deep Dive

RLS is defined per dataset through roles. A role specifies a DAX filter expression that applies automatically when a user assigned to that role queries the model.

Static RLS

The role expression is a literal filter. For example: FILTER(Region, Region[Name] = "Northeast"). Users assigned to the Northeast role see only Northeast data. Static RLS is simple to implement but requires one role per distinct entitlement. It does not scale beyond small teams.

Dynamic RLS

The role expression references USERPRINCIPALNAME() to filter based on the querying user.

-- Dynamic RLS on the Region table
Region[Region Key] IN
CALCULATETABLE(
    VALUES('User Security'[Region Key]),
    'User Security'[User Principal Name] = USERPRINCIPALNAME()
)

The User Security table is a bridge table mapping UPNs to entitlement keys. A single dynamic role replaces dozens of static roles. Adding a new user is a row insert in the User Security table, not a model change. This is the pattern we implement for nearly every enterprise engagement.

2. OLS Deep Dive

OLS is configured in Tabular Editor 2 or 3 (Power BI Desktop does not yet expose OLS through the UI). The process is:

1. Open the model in Tabular Editor connected to the XMLA endpoint or as a .bim/.tmdl file.
2. Select a role, then select a table or column.
3. Set the Table Permissions or Column Permissions property to None for the role.
4. Save changes to the workspace or source file.

When a user in a role with OLS restrictions queries the model, any visual or DAX expression that references a blocked column returns an error. This is intentional: OLS fails closed rather than silently returning blanks, which is a critical security property.

Handling OLS errors in visuals

If a report is shared across roles with different OLS restrictions, some users will see visual errors on pages that reference blocked columns. Three mitigation patterns:

• Role-specific reports: publish separate reports for each role, each using only columns that role can access.
• Conditional visual visibility: use bookmarks and DAX USERPRINCIPALNAME-driven visibility rules to hide sensitive visuals from unauthorized users.
• Calculation groups: wrap sensitive measures in calculation items that return BLANK() for unauthorized users.

The role-specific report pattern is the cleanest but requires more maintenance. The conditional visibility pattern is more flexible but must be carefully tested.

3. Combining RLS and OLS

The combined pattern for an enterprise HR model might look like this:

• RLS on Employee table: managers see only employees in their reporting chain, HR business partners see only employees in their assigned business unit, executives see all.
• OLS on Salary column: only HR roles can see the Salary column. Managers see a redacted view without compensation data.
• OLS on SSN column: only HR Payroll role can see SSN. All other roles have the column hidden entirely.
• OLS on Performance Rating column: only the employee's direct management chain can see their rating.

This combination enforces both row-level entitlement (who you can see) and column-level sensitivity (what attributes of those rows you can see). It is the baseline pattern for any HR, healthcare, or financial services semantic model.

4. External Tools for RLS and OLS

Power BI Desktop handles static and dynamic RLS natively through the Modeling menu. For OLS and large-scale role management, external tools are required.

• Tabular Editor 2 (free) or 3 (paid): full OLS configuration, bulk role management, scripted role definitions.
• ALM Toolkit: compare role definitions between development and production workspaces.
• DAX Studio: test role behavior by impersonating effective identities at the XMLA endpoint.
• PowerShell cmdlets (MicrosoftPowerBIMgmt): automate role membership assignment from enterprise identity systems.

For a deeper dive on external tool workflows, see our XMLA endpoint guide.

5. Testing Strategy

RLS and OLS failures are usually silent: a user either sees too much or too little, and nothing in the product alerts you. Testing is the only defense.

Test matrix

Build a matrix of test users x expected row counts x expected columns. For each intersection, record the expected values from authoritative sources (the source HR system, the ERP). Automate a nightly test that impersonates each role and validates actual vs expected.

# PowerShell example: impersonate user and validate row count
$dax = @"
EVALUATE
  ROW(
    "Total Employees", COUNTROWS('Employee')
  )
"@

$result = Invoke-ASCmd `
  -Server "powerbi://api.powerbi.com/v1.0/myorg/HR%20Workspace" `
  -Database "HR Analytics" `
  -Query $dax `
  -EffectiveUserName "[email protected]"

Assert-Equal $result.Rows[0].'Total Employees' 142

Run this nightly. Any drift in expected counts triggers an incident and a review.

6. Compliance Considerations

RLS and OLS are often the primary technical controls that satisfy compliance requirements in regulated industries.

• HIPAA: OLS on PHI columns ensures only authorized clinical staff access patient identifiers. RLS on encounter tables ensures providers only see patients they treat.
• SOC 2: role definitions with documented access justifications provide the audit evidence for access control and least-privilege requirements.
• GDPR: RLS supports purpose limitation by restricting who can query personal data. OLS supports data minimization by hiding attributes not needed for a particular role.
• FedRAMP: role documentation and automated testing evidence feed directly into the System Security Plan and continuous monitoring requirements.

Document every role with: purpose, scope, approver, review cadence, and evidence of testing. Most mature enterprises maintain this documentation in a role catalog separate from the model definition itself.

7. Common Pitfalls

• Forgetting to assign users to roles: a correctly defined role with no members grants no access. Users try to open the report and see nothing. Audit role membership monthly.
• Bidirectional cross-filter breaking RLS: bidirectional relationships can propagate filters in unexpected directions. Avoid bidirectional cross-filter on any table that has RLS applied, unless you explicitly test the filter propagation.
• USERNAME() returns domain\\user on desktop, UPN in service: always use USERPRINCIPALNAME() in RLS expressions for consistent behavior between Desktop and Service.
• Measures referencing OLS-blocked columns: a measure like [Avg Salary] = AVERAGE(Employee[Salary]) fails for any role with OLS on the Salary column. Either hide the measure or make it role-aware.
• Exporting data bypasses RLS visual filters but not RLS row filtering: users can export to Excel what they can see in the visual, which is already RLS-filtered. But always confirm export permissions in your tenant settings.

Frequently Asked Questions