Published datasets can be configured with Data Security using Row-Level Security (RLS) (for Tables) or Object-Level Security (OLS) (for Tables & Columns). The purpose of Data Security is to ensure users only see and use data they are permitted to, both in published reports and when making their own, self-service data solutions. To do this, users are assigned to Roles which have RLS or OLS rules configured, which filter (RLS) or restrict (OLS) queries generated by reports & client tools like Power BI Desktop or Excel.
Configuring RLS or OLS can be benificial for your model & reporting:
Reduce risk and improve governance by ensuring users only see data they have access to.
Configure dynamic RLS with central role tables for consistency and lightweight maintenance.
Have granular control over what data and objects can be queried.
How Does it Work?
Data Security works at the level of the model. It is configured following the below steps:
1. Create Roles:
Roles are groups of users who have the same permission / data security logic. Users in this case are identified by their email, or the email of an Azure AD Security Group. Examples of roles:
Users in the same region, team or department (EMEA, UA Sales Team).
Users with the same role, function or access clearance (Key Account Managers, SC Clearance).
Groups defined by other business logic or arbitrary rules (Externals, Build Users).
Figure 1: In Tabular Editor, Roles are one of the top-level object types (like Tables, Relationships, etc).
Important
After creating a new Role in Tabular Editor, you must first set the Model Permission property to Read.
2. Specify Rules:
Rules are applied for each role to one or more objects, depending on the security type:
RLS Table Permissions: DAX table expressions -- return each row evaluating True. These permissions traverse relationships; the design of the model is imperative to good RLS rules.
Figure 2: In Tabular Editor, Table Permissions for RLS are visible under the role. New table permissions can be created by right-clicking a Role and selecting 'Add Table Partition...'Figure 3: Table Permission DAX is visible in the Expression Editor when selecting a Table Permission.
OLS Object Permissions: These permissions apply to the primary objects as well as all downstream dependents.
Read (Can see / query)
None (Cannot see / query)
Default (No policy configured; equivalent to Read)
Figure 4: In Tabular Editor, object permissions are accessible in the 'Properties' window, under the 'Translations, Perspectives, Security' heading.
3. Assign Users to Roles:
Once configured in the dataset, users must be added to their respective roles.
You can assign and remove users/groups from roles through Tabular Editor the following way:
Right-Click the Role, select Edit members...
Figure 7:Users can be assigned to roles by right-clicking a Role and selecting 'Edit members...'.
Click the dropdown button on the 'Add Windows AD Member' button and choose Azure AD Member:
Figure 8: For AAS/SSAS models, users can be added via the 'Edit members...' dialog box.
Specify the Azure AD user identity (typically, the user e-mail address) as the Member Name property.
Click OK.
Save the model.
Important
If your organisation is using on-premises Active Directory with SQL Server Analysis Services, you will need to use the Windows AD Member option instead of Azure AD Member.
Note
It is recommended to manage Data Security and Access with Azure Active Directory Groups.
Using this approach is preferred you can centralize management of security & user segmentation.
4. Provision Users Access to the Dataset:
Power BI: Users must be given dataset access according to the usage scenario.
App Audience: Users / their Azure AD Groups are added to the appropriate App Audience.
Workspace Viewer: Users / their Azure AD Groups are added as Workspace Viewers
Dataset Readers: Users / their Azure AD Groups are given Dataset-specific permissions via a Dataset or dependent item (i.e. Report).
Warning
Users given Admin, Member or Contributor Workspace Roles have write permissions to a dataset. As such, Data Security like RLS and OLS will not filter or block data for users with these roles.
If a user is an Admin, Member or Contributor, they will be able to see all the data.
As much as is reasonable, try to distribute and manage permissions via Power BI Apps.
5. Validating Security:
RLS and OLS can only be tested with impersonation once user groups have been added & granted access. Validate security via:
Figure 5: The easiest way to test Data Security is via Impersonation with Tabular Editor. The 'Impersonation' option is possible from any data querying feature (DAX Queries, Pivot Grid, Preview Data).
Note
To validate data security with impersonation, the below factors must be all true:
The user must be assigned a role.
The user must have read permissions to the dataset.
The user must have build permissions to the dataset.
Figure 6: A demonstration of RLS testing in Tabular Editor using impersonation. Shown is testing with (A) Data Preview, (B) DAX Queries and (C) Pivot Grid.
Important
Testing Data Security with Impersonation using Tabular Editor 3 is limited to dataset hosted in the Power BI Datasets service. TE3 Desktop Licenses cannot benefit from this feature. This is because roles are assigned in the Power BI Service.
How Does it Look?
Depending on how you have designed and configured Data Security, the experience may differ for users.
Below are common examples for common scenarios of RLS and/or OLS implementation in a dataset
Click a tab to see the example and get an explanation of each:
Without security, everyone with access to the dataset can see all the data.
The only restriction is whether they have access to the reports / datasets.
In the example, both Jack & Janet can see all of the data.
With RLS configured, data is filtered to only rows the user are allowed to see. This is done according to the Table Permissions configured in the model for that Table & Role. These Table Permissions are DAX Table Expressions configured for a specific model table. Rows that evaluate True are returned; rows that return False are filtered out due to RLS.
The most simple Table Permissions are Static:
// Table Permission for 'Regions' table and 'CTG' role
'Regions'[Territory] = "Central Transit Gate"
In the example:
Jack can see only rows for which 'Region'[Territory] = "Central Transit Gate", since they belong to the 'CTG' Role.
Executives, whom are permitted to see all the data, are added to a role with no Table Permissions.
Tommy, a user who can access the dataset but belongs to no Role, will not see any data. All visuals and queries will return a 'grey box of death'.
It is necessary to create roles when using Data Security even when there are users like Executives, who have unrestricted data access.
With RLS configured, data is filtered to only rows the user are allowed to see.
This is done according to the Table Permissions configured in the model for that Table & Role.
These Table Permissions are DAX Table Expressions configured for a specific model table.
Rows that evaluate True are returned; rows that return False are filtered out due to RLS.
Dynamic RLS relies on the USERPRINCIPALNAME() or USERNAME() functions to compare them to a security table.
The security table will then return logic that applies the table filter to that or another table in the model.
This is referred to as Dynamic RLS as the result will change depending on the user; the USERPRINCIPALNAME().
Below is an example of a Dynamic RLS Table permission:
// Table Permission for 'Regions' table and 'Territory Directors' role.
// Get Current User
VAR _CurrentUser =
SELECTCOLUMNS (
FILTER (
'Employees',
'Employees'[Employee Email] = USERPRINCIPALNAME ()
),
"@Name", 'Employees'[Employee Name]
)
RETURN
'Regions'[Territory Directors] IN _CurrentUser
The above table permission gets the Employee Name alias from the 'Employees' table, which is applied without a relationship to the 'Regions' table.
The result for any user added to this role is that they will only see data where:
Their e-mail is in the 'Employees'[Employee Email] column
Their alias in 'Employees'[Employee Name] matches one in 'Regions'[Territory Directors]
In the example, each Territory Director only sees the Territories for which they are responsible:
Jack sees "Central Transit Gate" and "Io".
Janet sees "Arcadia III".
Elisa sees all data, as the Execs role has no Table Permissions set.
Dynamic RLS is the most common way to secure an Enterprise Dataset. It usually requires configuration & maintenance of a central Security Table used across all Enterprise datasets.
When a user is assigned to multiple roles, each of which have different Table Permissions, they will see data permitted by either role. Users will see data where at least one Table Permission DAX expression evaluates to True for model table rows; it takes the logical or.
This is dangerous if it's not expected; some developers may anticipate the intersection to be taken; to only show rows where both Table Permissions return True. This will only happen if Table Permissions are configured for multiple tables in the model; within a Role, the intersection is taken for all Table Permissions in the model.
In the example:
Jack is assigned to the roles 'CTG' and 'FTL'. They will see any rows where 'Products'[Type] = "FTL" OR where 'Regions'[Territory] = "Central Transit Gate". This is likely not the expected behavior; the developer likely intends to produce the result of the 'CTG/FTL' role, which returns only rows where both are true.
_Elijah has the 'FTL' role, and will only see rows where 'Products'[Type] = "FTL".
Abdullah has the 'CTG/FTL' role, and will only see rows where BOTH 'Products'[Type] = "FTL" AND 'Regions'[Territory] = "Central Transit Gate".
Situations like this illustrate the importance of designing clear Data Security configuration during model design, ensuring it aligns with organizational policies and existing Data Security / Access Management practices.
With OLS configured as None, queries are prevented from being evaluated; they return an error. This is an important distinction from RLS; RLS filters data, but OLS prevents evaluation. If the OLS permission is set to Read, there is no effect. This is done according to the OLS Permission Level of the Column or Table, and affects all downstream dependents like Relationships and Measures.
In the example, the column 'Territory Sales'[Cost] has an OLS Permission of None for the role 'Sales'. The reason is because of the below requirement:
'Sales' users are allowed to see Sales data, but not Cost or Margin data.
This means that a user belonging to the 'Sales' role like Jack will not be able to see:
Any query or visual referring directly to the 'Territory Sales'[Cost] column
Any DAX measure or calculation item referring directly to the 'Territory Sales'[Cost] column, like [Margin %]
Any DAX measure or calculation item referring indirectly (downstream) to the 'Territory Sales'[Cost] column.
Any object that has a column with a relationship with 'Territory Sales'[Cost]
The result of 1-4 will be an error in query evaluation. A Power BI visual will return a grey box of death.
Warning
__Business users will often perceive expected OLS results as being a 'broken' report, visual, or query. __
If using OLS and users are expected to confront these evaluations, try the following:
Educate users about the security.
Try to handle the error and return a more meaningful message.
In Build scenarios, consider hiding the object.
A further optimization to be tested is setting IsPrivate to True or IsAvailableInMDX to False.
With both RLS & OLS configured, there are two possible outcomes:
The user has one role with RLS & OLS: The security will work as expected, presuming it is configured correctly.
The user has multiple roles where RLS & OLS are configured, separately: The role combination is unsupported and the user will get an error.
Because of #2, if you expect to use both RLS & OLS, this must be carefully considered during model design.
An example of #1 is below:
In the example:
Jack, who has been assigned the 'CTG' Role:
Can only see data for "Central Transit Gate" due to the RLS Table Permission on 'Regions'.
Can only see Sales data; they can't see [Margin %]. This is due to the OLS Object Permission None on 'Territory Sales'[Cost], which affects the dependent measure [Margin %]
Elisa, who has been assigned the 'Execs' Role, can see all the data. There has been no RLS Table Permission or OLS Object Permission (set to Default) configured for 'Execs'.
Tommy, who has not been assigned a Role, cannot see any data.
Warning
Scenarios combining RLS & OLS are not rare.
Scenarios using them correctly are.
Ensure that if you have a requirement for RLS & OLS together, carefully consider it during model design.
With both RLS & OLS configured, there are two possible outcomes:
The user has one role with RLS & OLS: The security will work as expected, assuming it is configured correctly.
The user has multiple roles where RLS & OLS are configured, separately: The role combination is unsupported and the user will get an error.
Because of #2, if you expect to use both RLS & OLS, this must be carefully considered during model design.
An example of #2 is below:
In the above example:
Jack, who has been assigned the 'Read Users' Role:
Can only see data for "Central Transit Gate" due to the RLS Table Permission on 'Regions'.
Can only see Sales data; they can't see [Margin %]. This is due to the OLS Object Permission None on 'Territory Sales'[Cost], which affects the dependent measure [Margin %]
Janet, who has been assigned both the 'Read Users' and 'Build Users' Role:
Cannot see any data. The combination of RLS / OLS permissions across Roles are invalid.
Users granted Build Permissions to the dataset are added to the Build Azure AD Security group, which is set for the 'Build Users' role. Build users can see tables not in existing reports, so None OLS permission is configured for the 'Employees' table. This produces a combination where RLS and OLS permissions cannot be reconciled, resulting in an error.
Warning
Scenarios combining RLS & OLS are not rare.
Scenarios using them correctly are.
Ensure that if you have a requirement for RLS & OLS together, carefully consider it during model design.
No user will be able to read any data until they are added to the role, so long as Data Security is configured in the dataset.
Note
Don't forget to give users access to the dataset AND add them to the security role.
If a user is added to a security role, this will not automatically grant them read access to the dataset. They will still not be able to access any dataset or reports.
Note
Don't forget to give users access to the dataset AND add them to the security role.
Warning
It is a best practice to avoid distribution via Workspace Roles, where feasible. If necessary, ensure that the Principle of Least Permissions is applied : users should have the minimum access necessary to do what they need.
If a user is given access to a dataset via the Admin, Member or Contributor roles, they will be able to view all data, irrespective of the data security configuration and their assigned roles. This is a common mistake in scaling or self-service Power BI ecosystems, resulting in data leaks and non-compliance.
Warning
It is a best practice to avoid distribution via Workspace Roles, where feasible. If necessary, ensure that the Principle of Least Permissions is applied : users should have the minimum access necessary to do what they need.
Hard Limitations
Some reporting or data modeling features will not work with RLS or OLS configuration. Examples are:
