Row-level security: three ways to believe it is in place when it is not

A regional director opens their report, sees their region, and everything seems in order. Three months later, someone exports a detail table and discovers the figures of another region.

Row-level security was in place. It did not cover that path.

The principle, in one sentence

A role carries a DAX filter on a table. When a user assigned to that role opens the report, the filter applies before any calculation, and it propagates to the other tables through the relationships of the model.

This last point is the source of the three mistakes that follow: security only protects what the relationships allow it to reach.

Mistake 1: the filter does not propagate to the facts

You place the role on the Region table, which is on the “one” side of a relationship to Sales. The filter flows down correctly: each director sees their own sales.

Then someone adds a Targets table, also related to Region, but the relationship is created in the other direction, or it stays inactive because a duplicate prevented it from being validated.

The report then shows filtered sales and everyone's targets. Nothing signals it: the two visuals sit side by side and one of them lies.

What we do: after setting a role, we list all the fact tables in the model and check one by one that they are reached. A fact table that no relationship path connects to the secured table is not protected, and never will be by this role.

Mistake 2: bidirectional filtering opens a door

Bidirectional cross-filtering is used to solve certain models with several fact tables. It also creates paths that the security design had not anticipated.

The typical case: Sales is related to Product in both directions so that a filter on sales narrows the list of products. A user restricted to one region then sees, in a slicer, the list of products sold in their region, which is intended. But if another table goes up through Product towards an unsecured axis, the filter can come out through that path.

What we do: no bidirectional relationship in a model that carries row-level security. Where the need is real, we handle it with CROSSFILTER in the one measure concerned, which makes the effect local and explicit.

Mistake 3: the role is right, the mapping table is not

Many models are secured through a table that associates a user with a scope:

[Email] = USERPRINCIPALNAME ()

The filter is correct. What breaks is the table.

An employee changes region: two rows coexist, and they see both scopes. An address is entered with a different capital letter, which makes no difference since DAX compares without regard to case, but a trailing space breaks the match and the user sees nothing. That case is reported quickly, because it gets in the way. The first one never is.

What we do: the mapping table is fed from the company directory rather than typed by hand, addresses are cleaned on import, and a check verifies that no user appears twice.

The test that catches all three

Power BI Desktop's View as button tests a role on the visuals of one page. It is useful and insufficient: it only tests what the page displays.

Our protocol comes down to four points.

1. A set of test accounts, one per scope, including an account with no scope at all, which must see zero rows, not everything.
2. A temporary control page that displays, for each fact table in the model, the number of visible rows. That is where the table that no filter reaches appears.
3. An export test. The most often forgotten path: Export data from a visual, and access to the model from Excel. Both of these bypass the page layout, not the security, provided the security is complete.
4. A test after every table is added. A table added six months later inherits nothing: it is open until someone relates it to the secured scope.

Point 3 deserves to be done in front of the client, once. It turns a promise into a demonstration.

What to remember

Row-level security only protects what the model's relationships allow it to reach. Three things defeat it: a fact table that the filter does not reach, a bidirectional relationship that opens a side path, and a mapping table that contains a duplicate.

The test that catches them is a control page listing the number of visible rows per table, run under a test account, including an account with no scope, which must see zero.