Row-Level and Object-Level Security in BI Cheat Sheet

Updated 2026-05-23

Next Topic: Sales Analytics and Pipeline Reporting Cheat Sheet

🧠Study flashcards on this topic73 cards · spaced repetition→

Row-level security (RLS) and object-level security (OLS) are the foundational mechanisms that determine which data a user can see and which model objects they can access inside a BI platform. Implemented incorrectly, these controls leak sensitive data, degrade performance, or silently allow bypasses through workspace roles and bidirectional filter propagation. This cheat sheet covers the full security stack across Power BI, Tableau, Looker, and Qlik — from basic DAX filter expressions and dynamic identity lookups to entitlement tables, section access, audit logging, and Microsoft Fabric's cross-engine enforcement model.

What This Cheat Sheet Covers

This topic spans 12 focused tables and 74 indexed concepts, 73 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: RLS Fundamentals and TypesTable 2: Power BI DAX Identity Functions for RLSTable 3: Power BI Security Tables and Bridge PatternsTable 4: Object-Level Security (OLS) in Power BITable 5: Testing and Validating RLS in Power BITable 6: Power BI RLS — Common Pitfalls and Bypass RisksTable 7: Power BI RLS Performance OptimizationTable 8: Tableau Row-Level SecurityTable 9: Looker Row-Level SecurityTable 10: Qlik Sense Section AccessTable 11: Microsoft Fabric and OneLake SecurityTable 12: Audit, Logging, and Governance for BI Security

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: RLS Fundamentals and Types

Static and dynamic RLS differ in where the user identity is encoded — directly in the DAX filter expression versus resolved at query time from a mapping table. Understanding this distinction, plus how filters propagate across relationships, is the prerequisite for every platform-specific implementation that follows.

Type	Example	Description
Static RLS	`[Region] = "East"`	• DAX filter hardcoded in a role definition • all members of the role see the same fixed slice of data
Dynamic RLS	`[Email] = USERPRINCIPALNAME()`	• Filter expression uses an identity function resolved at query time • each user automatically sees only their own rows
Row Filter Propagation	`Sales[CustomerID] = Security[CustomerID]` `AND Security[Email] = USERPRINCIPALNAME()`	RLS filter on one table cascades across relationships to related tables following the cross-filter direction.
Additive Roles	User in both `East` and `West` roles	• When a user belongs to multiple roles, the results are a union (OR) of all allowed data • filters are never intersected
DirectQuery RLS	`WHERE Region = 'East'` pushed to source	• RLS filters are pushed down to the underlying SQL source • supported but adds network round-trip overhead
Import Mode RLS	DAX evaluated against in-memory model	• Filters applied entirely in-memory after data is imported • no source query modification

Table 2: Power BI DAX Identity Functions for RLS

These DAX functions return the currently authenticated user's identity and are the core building blocks of dynamic RLS. Their return values differ between cloud (Power BI Service) and on-premises (Analysis Services / Report Server) deployments.

Function	Example	Description
USERPRINCIPALNAME()	`[Email] = USERPRINCIPALNAME()`	• Returns the UPN/email of the logged-in user in Power BI Service • preferred for cloud RLS

The rest of this section is part of the full cheat sheet

Table 3: Power BI Security Tables and Bridge Patterns

When the same user needs access to multiple data segments, or when the data model is complex (hierarchical or many-to-many), a dedicated security mapping table isolates access logic from business fact tables and centralises maintenance.

Pattern	Example	Description
Security Mapping Table	`UserSecurity[Email] = USERPRINCIPALNAME()` joined to fact via `Region`	• A dedicated table with `Email` and allowed dimension values • the RLS filter is placed on this table and propagates to facts via relationship

The rest of this section is part of the full cheat sheet

Table 4: Object-Level Security (OLS) in Power BI

OLS hides entire tables or individual columns from role members at the model metadata level — the field simply does not appear to exist. It is a separate layer from RLS and requires external tooling because Power BI Desktop has no native OLS UI.

Concept	Example	Description
OLS — Column Hidden	`Salary` column set to `None` for role `SalesViewer`	Column permission set to None makes the column invisible and unqueryable for members of that role.

The rest of this section is part of the full cheat sheet

Table 1: RLS Fundamentals and Types

Type	Example	Description
Static RLS	`[Region] = "East"`	• DAX filter hardcoded in a role definition • all members of the role see the same fixed slice of data
Dynamic RLS	`[Email] = USERPRINCIPALNAME()`	• Filter expression uses an identity function resolved at query time • each user automatically sees only their own rows
Row Filter Propagation	`Sales[CustomerID] = Security[CustomerID]` `AND Security[Email] = USERPRINCIPALNAME()`	RLS filter on one table cascades across relationships to related tables following the cross-filter direction.
Additive Roles	User in both `East` and `West` roles	• When a user belongs to multiple roles, the results are a union (OR) of all allowed data • filters are never intersected
DirectQuery RLS	`WHERE Region = 'East'` pushed to source	• RLS filters are pushed down to the underlying SQL source • supported but adds network round-trip overhead
Import Mode RLS	DAX evaluated against in-memory model	• Filters applied entirely in-memory after data is imported • no source query modification

Table 2: Power BI DAX Identity Functions for RLS

Function	Example	Description
USERPRINCIPALNAME()	`[Email] = USERPRINCIPALNAME()`	• Returns the UPN/email of the logged-in user in Power BI Service • preferred for cloud RLS

Table 3: Power BI Security Tables and Bridge Patterns

Pattern	Example	Description
Security Mapping Table	`UserSecurity[Email] = USERPRINCIPALNAME()` joined to fact via `Region`	• A dedicated table with `Email` and allowed dimension values • the RLS filter is placed on this table and propagates to facts via relationship

Table 4: Object-Level Security (OLS) in Power BI

Concept	Example	Description
OLS — Column Hidden	`Salary` column set to `None` for role `SalesViewer`	Column permission set to None makes the column invisible and unqueryable for members of that role.