Row-level security: test what each role can actually see

Row-level security restricts which model rows a consumer can access. A report slicer or hidden page is not an access boundary. Test security with explicit expected and forbidden records, under the permissions and identity the intended consumer will actually use.

This exercise uses only synthetic data from the retail modelling lab. No live workspace, account or permission changes are performed by the supplied files.

Define the allowed population first

Create a teaching policy in which North readers may access customers whose Region is North, and West readers may access West. The explicit Unknown region is excluded from both roles and reserved for a separately authorized reconciliation audience.

For the clean fixture, North has customer keys 1 and 4. Its paid net value is 44,500 paise. West has customer keys 2 and 5; key 5 has no transactions, and West's paid value is 20,000.

These are expected results derived from the fixture, not proof that an RLS model has been configured correctly.

Apply the rule to the dimension

For a static North role on DimCustomer, the DAX filter is:

[Region] = "North"

The relationship from DimCustomer to FactSales must propagate the restriction to the intended fact rows. Add a detail table with LineID, CustomerKey and value so the test can inspect exact records rather than only a total card.

Power BI's current workspace permissions matter: RLS applies to Viewer consumers, while workspace Admin, Member and Contributor roles are not restricted by those RLS rules. Test role membership and workspace permissions together. Microsoft's RLS documentation.

Test allowed and forbidden rows

Under North, expected fact LineIDs are S1, S2, S4 and S7. West should expose S3 and S6. S8, the Unknown customer line, must not appear under either of these roles.

Check a deliberately selected forbidden customer through filters and drill-through. Removing a slicer, changing the report page or opening an allowed detail view must not reveal a forbidden row. Record the actual observed key set and compare it with the expected set.

Also test an empty legitimate slice, such as West filtered to a product with no West activity. An empty result can be correct; the interface should not encourage broadening access merely to make a visual populate.

Review multiple-role membership

Power BI role permissions are additive. A user assigned both North and West can receive the combined permitted populations, rather than the intersection. In this fixture that combined paid total is 64,500 paise, still excluding Unknown. RLS role-membership behaviour.

If the intended policy requires a different result, redesign the role or user-mapping approach. Do not assume that assigning a restrictive second role removes access granted by the first.

Validate dynamic identity in the real access path

For dynamic RLS, compare the actual identity returned by USERPRINCIPALNAME() with the controlled user-to-region mapping. Guests, aliases and embedded effective identities need tests in their actual access path. A successful author-side simulation does not establish that every consumer identity resolves identically.

An unmapped user should follow an explicitly designed no-access or no-row policy, not fall into an unrestricted default. Test that negative case deliberately.

RLS secures rows, not an arbitrary subset of columns. If some fields require separate restrictions, assess the appropriate object-level or model-design controls. Hiding a column in a report is not the same as removing access to it.

Keep the evidence attached to the model

Record model version, role expressions, relationship settings, consumer permissions, test identity and observed rows. Include permitted exports or alternate query paths if consumers can use them. Re-run tests after relationship or permission changes.

The lab's SQLite checks confirm source totals only. It does not execute RLS, publish a report or claim an access-control certification. Those checks must occur in the authorized Power BI environment before real data is shared.

Exercise: add a user-to-region mapping with no entry for one test user. Verify the intended denied state, then assign two allowed regions and compare the observed union of records with the policy.

NeuraPath's Data Analytics with Generative AI course connects dashboard delivery with access testing. A strong RLS project proves both what each consumer can see and what remains inaccessible.

Continue learning

This article is part of the Power BI data models and reporting sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Power BI refresh failures: separate credentials from schema changes.
• Continue with Choose Import or DirectQuery from the workload.