Row-level security for shared models

One semantic model can serve a whole trust if each person sees only the rows they should: a site manager their own site, a directorate its own specialties. Row-level security (RLS) does that with roles, each holding DAX filters on tables. It is what lets one model replace a copy per audience. It also has edges worth knowing before you rely on it: it doesn’t bind everyone who can open the model, it doesn’t hide columns, and it has no say over a spreadsheet once it has been exported.

The examples come from my theatre utilisation semantic model: three sites, three static roles and one dynamic role, on synthetic data. Its TMDL loads in Microsoft’s serializer. I haven’t opened the model in Power BI Desktop or run the roles in Power BI, so the numbers for what each role sees come from a pandas version of the same filters.

How a role filters

A role holds a filter expression for each table it restricts. The expression is evaluated for every row, and only rows where it returns TRUE stay visible. A filter on a dimension reaches the fact tables through relationships, from the one side to the many side. Even on a bi-directional relationship, a security filter travels only that way unless Apply security filter in both directions is ticked. Microsoft’s guidance is to put RLS filters on dimension tables where you can, and to let well-designed relationships carry them, noting that they propagate only through active relationships.

Two consequences catch people out:

• Other dimensions aren’t filtered. In the theatre model, a filter on Site removes other sites’ lists and cases, but Theatre has no relationship to Site. Without a filter of its own, a user limited to the main hospital would still see the elective centre’s theatres listed in a slicer, with no data behind them. So every role filters Theatre as well.
• Inactive relationships don’t carry it. Microsoft’s documentation also warns that with RLS enabled, USERELATIONSHIP in queries and measures might cause unexpected errors, and suggests designing around it. Check any measure that switches relationships before you add roles.

Static roles

A static role hard-codes its filter. The theatre model has one per site:

/// Static role: lists, cases and theatres at the main hospital only.
role 'Main Hospital'
	modelPermission: read

	tablePermission Site = Site[Site Code] = "MAIN"

	tablePermission Theatre = Theatre[Site Code] = "MAIN"

	tablePermission 'Site Access' = FALSE ()

Static roles are easy to read and easy to test. The cost is a role per audience, each with its own membership. Power BI Desktop can’t assign members: you define roles in Desktop, publish, and add people or groups on the semantic model’s Security page in the service. Map security groups rather than individuals where you can; Microsoft 365 groups aren’t supported for RLS membership.

Roles are additive. A user in two roles sees the union of what each allows, and “once denied, always denied” doesn’t apply. Microsoft’s guidance gives the example of a Workers role that filters a Payroll table with FALSE() and a Managers role with TRUE(): a user in both sees every payroll row. Its advice is to design roles so each user needs only one.

Dynamic roles with a security table

A dynamic role has one filter that depends on who is signed in. USERPRINCIPALNAME() returns the user principal name (UPN). In the Power BI service USERNAME() returns the UPN too, but in Desktop it returns DOMAIN\user, so I use USERPRINCIPALNAME() throughout.

Who sees what lives in a table, one row per user and site. The first five of its eight rows:

user_principal_name,site_key
[email protected],1
[email protected],2
[email protected],3
[email protected],2
[email protected],3

The role reads it:

/// Dynamic role: each member sees the sites listed against their user principal name in Site Access. A member who isn't listed sees no lists or cases.
role 'Site Managers'
	modelPermission: read

	tablePermission Site =
			Site[Site Key]
				IN CALCULATETABLE (
					VALUES ( 'Site Access'[Site Key] ),
					'Site Access'[User Principal Name] = USERPRINCIPALNAME ()
				)

	tablePermission Theatre =
			Theatre[Site Key]
				IN CALCULATETABLE (
					VALUES ( 'Site Access'[Site Key] ),
					'Site Access'[User Principal Name] = USERPRINCIPALNAME ()
				)

	tablePermission 'Site Access' = 'Site Access'[User Principal Name] = USERPRINCIPALNAME ()

Four decisions are in those lines:

• The filter sits on Site, not on the mapping table. A common alternative relates the mapping table to Site and ticks Apply security filter in both directions on that relationship. Microsoft warns that bi-directional security filtering can hurt query performance, and a table in several bi-directional relationships can have the option on only one of them. Filtering the three-row Site table directly keeps every relationship single-direction.
• The mapping table filters itself. It’s hidden from the field list, and the rule on it means nobody can list who else has access. The static roles filter it with FALSE ().
• Unknown users see nothing. Someone signed in but missing from the table matches no sites, so every fact row is filtered out. Microsoft’s guidance makes the same point about embedded apps: write rules so an unexpected value returns no rows, not all of them.
• The stored value must be what USERPRINCIPALNAME() returns. A UPN is the sign-in name, which isn’t always the email address. For external (B2B) guests it may be their own email or a #EXT# form, depending on the tenant. DAX string comparisons ignore case, but spelling still has to match.

Here is what each role sees over the whole 18 months:

Static or dynamic? With three sites that rarely change, static roles are simpler to reason about. Once access changes often, or people need combinations of sites, a mapping table maintained as data beats a growing list of roles and memberships. The model has both only so this article can compare them; I’d ship one.

Roles in TMDL

Each role is a file in the roles folder, and model.tmdl fixes their order with ref role lines:

ref role 'Main Hospital'
ref role 'Elective Centre'
ref role 'Community Hospital'
ref role 'Site Managers'

A role has modelPermission: read and a tablePermission for each table it filters. The filter expression follows the equals sign, and a long one continues on the lines below, indented like a measure’s DAX. Object-level security lives in the same role: a tablePermission with metadataPermission: none hides a table, and a columnPermission inside it hides a column.

Loading the folder with the Tabular Object Model’s TmdlSerializer catches structural mistakes. When I renamed a role’s tablePermission Theatre to Theatres, the load failed with “refers to an object which cannot be found”. It doesn’t parse the DAX inside the filter; I checked those separately with DAX Formatter, which checks syntax only.

Testing

In Power BI Desktop, Modeling > View as applies one or more roles to the report. For a dynamic role, also tick Other user and type a UPN; Microsoft notes that Other user only changes the result when the rules are dynamic. For the theatre model I’d test [email protected] (two sites), [email protected] (all three) and an address that isn’t in the table, which should show nothing.

In the service, the semantic model’s Security page has Test as role, with Now viewing as to switch between roles. Know its limits. Microsoft’s documentation says it evaluates USERPRINCIPALNAME() with your own identity, so it can’t show what a particular external guest will see; for that, sign in as the guest. It doesn’t work for DirectQuery models with single sign-on or for paginated reports, and dashboards can’t be tested with it.

Microsoft’s troubleshooting tip is worth adopting from day one: a “Who am I” measure returning the user’s identity, on a card, shows what the rules are actually comparing against.

Performance

RLS works by adding its filters to every DAX query, so efficient RLS comes down to model design. Microsoft’s guidance: filter dimension tables rather than facts, rely on relationships rather than LOOKUPVALUE where a relationship would do, and treat bi-directional security filtering as a cost. The theatre model’s dynamic rule is evaluated over the 3 rows of Site and the 11 rows of Theatre, and the facts are filtered through ordinary relationships.

To measure the cost, run Performance Analyzer on a report page, then again under View as, and compare the query durations.

If you have only a few audiences with simple, static filters, Microsoft suggests considering separate models, one per audience in its own workspace, instead of RLS. Each model is smaller, queries can be faster with no role filters to apply, and they can use features that don’t work with RLS, such as Publish to web. The price is duplicated reports and no consolidated view for people who need more than one audience.

What RLS doesn’t protect

Workspace members. RLS applies only to users with the Viewer role. Admins, Members and Contributors have edit permission on the semantic model, so RLS doesn’t apply to them. Viewers with Build permission are still filtered, including when they use Analyze in Excel. If someone in the workspace must be restricted, they can only be a Viewer.

Columns, tables and their names. RLS filters rows. A user who can see a row sees every column in it. To hide a table or a column, including its name, use object-level security. Microsoft documents setting it in TMDL view or Tabular Editor, and it too applies only to Viewers. Neither RLS nor OLS can be defined on a calculation group table.

Totals. RLS can’t show summaries while hiding the detail beneath them. No DAX expression can override it either: REMOVEFILTERS() in a measure still sees only permitted rows. For “my site as a share of the trust”, Microsoft’s guidance adds a summary table that the roles don’t filter, and warns that with only two regions a user could then work out the other one.

Exports. An export from a visual contains only data the user is allowed to see. After that the file is outside Power BI. Sensitivity labels with protection settings carry into exports to Excel, PowerPoint and PDF; a CSV export can’t carry a label.

Service principals and people in no role. Service principals can’t be added to RLS roles, and RLS isn’t applied when an app uses one as the effective identity; embedded apps pass an effective identity instead. And a user who isn’t in any role typically sees no data at all.

Before you publish

• Every table with sensitive rows is filtered, including dimensions that the main filter doesn’t reach.
• The mapping table holds exactly what USERPRINCIPALNAME() returns, and it’s filtered too.
• Unknown users get no rows.
• Each person needs one role, mapped through a security group.
• Anyone in the workspace who must be restricted has the Viewer role, not Contributor or above.
• Columns that must be hidden use OLS, not RLS.
• View as with at least one user per role, plus one who shouldn’t see anything.