Testing Data Security with Impersonation
- ❌
Desktop Edition - ✔ Business Edition
- ✔ Enterprise Edition
DAX Queries, Pivot Grid, or Preview Data enable testing of Data Security in Tabular Editor. It is recommended to always test Data Security with any changes to configuration, to mitigate risk of incorrect RLS/OLS implementation and the consequences thereof.
Important
Testing Data Security with Impersonation using Tabular Editor 3 is limited to datasets hosted in an Analysis Services instance or the Power BI Service. Tabular Editor 3 Desktop Licenses cannot benefit from this feature.
- About Data Security and RLS/OLS: A functional overview of RLS & OLS.
- Modify/Setup an RLS Configuration: How to configure RLS in a dataset.
- Modify/Setup an OLS Configuration: How to configure OLS in a dataset.
- Testing RLS/OLS with Impersonation (This Article): How to easily validate Data Security with Tabular Editor.
Testing with Impersonation
Data Security can be easily tested using Impersonation in Tabular Editor 3. Impersonation is a feature that lets you view a query result as a model Role or User. It is similar to the 'View As Role...' feature in the Power BI service, with two key differences:
- The End-User being impersonated requires dataset Build permissions in addition to Role assignment & Dataset Read access.
- Any query can be executed within Tabular Editor 3; it is not limited to available report visuals, as in the Power BI Service.
This is valuable, as it lets the developer run defined tests to see how the result would be viewed by any end-user with Build permissions. This helps ensure that even for complex queries and DAX expressions, the Data Security works as expected, and users only see what they should see.
Important
Ensure that Build permissions are not provisioned by providing end-users Workspace Roles (Contributor, Member, Admin), as these roles have Write permissions to the dataset and thus bypass Data Security; the testing will appear to not work, even if it's configured correctly.
How to Test with Impersonation
To test with impersonation, follow the below steps:
- Ensure that the Dataset Configuration & Access is correct: End-users being impersonated...
- ...have been assigned to the appropriate Roles.
- ...have been provisioned Dataset Read Access.
- ...have been provisioned Dataset Build Access. (Power BI)
- ...are not Workspace Contributors, Members or Admins (Power BI).
- Create a new DAX Query, Pivot Grid or Preview Data window:
- It's recommended that you start with Preview Data to observe the effect on model tables
- Thereafter, perform a second validation with a DAX Query. This is because DAX Queries can be saved for documentation and later reference, if a change occurs in the model requiring re-testing.
Select 'Impersonation' and enter the User e-mail: If you have implemented Static RLS, you can test the role, instead.
Explore the data to validate that the results appear as expected: (according to the Security Rules).
Tips for Testing
Test more than one user: It's recommended you test at least 3-10 different users per Role. You can also automate the testing to iterate through each UPN in the Security Table (i.e. using C# Scripting and Macros).
Test each Role & Table Permission: Since each Table Permission represents a different DAX Filter Expression, they all have to be tested, separately. Ensure that each Role is tested, and that each test includes the relevant tables with configured Filter Expressions. For example, if a Role consists of table expressions on the 'Customers' and 'Products' table, ensure your query includes attributes from both tables for validation purposes.
Test many Queries/Measures: Try to find complex queries to test, particularly those which might be problematic in the context of Data Security. For example, if calculations require comparing to a total, unfiltered average (i.e. % of total) and it's expected that that total is not filtered in RLS, the developer may need to re-think the Data Security implementation as a function of the model.