Security roles

Per Chapter 1, Planning Power BI Projects, the required data security for this project is to limit the visibility of the Adventure Works sales team users to their respective sales territory groups. There are three sales territory groups (North America Sales Group, Europe Sales Group, and Pacific Sales Group), and, as described in the previous chapter, cross-filtering relationships exist between the Sales Territory dimension table, and all three fact tables (Internet Sales, Reseller Sales, and Sales and Margin Plan). Therefore, security roles with a filter condition on the given sales territory group will also filter the fact tables, and business users mapped to these roles will only see data associated for their Sales Territory group.

Security roles are defined in Power BI Desktop via the Manage roles dialog of the Modeling tab as shown in the following screenshot:

Managing security roles

In this example model, the Sales Territory dimension table has a single direction one-to-many relationship with the Internet Sales and Reseller Sales fact tables. For the Sales and Margin Plan fact table, the Sales Territory filter first flows to the bridge table and then uses a bidirectional cross-filtering relationship from the Sales Territory bridge to Sales and Margin Plan. Therefore, a user mapped to the Europe Sales Group role will only have access to the Internet Sales, Reseller Sales, and Sales Plan data associated with Europe.

Just like a filter selection on a column of the Sales Territory table in a report, a security filter also flows across the cross-filtering relationships of the data model. However, unlike report filters, security filters cannot be overridden by DAX measures. Security filters are applied to all report queries for the given dataset and any additional filtering logic or DAX expression respects the security role definition.

Given the automatic filtering of security role conditions, it's important to implement efficient security filters and to test the performance of security roles. For example, a complex filter condition applied against a large dimension table could significantly degrade the performance of reports and dashboards for users or groups mapped to this security role.

In addition to defining security roles, security roles can also be tested in Power BI Desktop via the View as roles command on the Modeling tab. In the following screenshot, a chart that displays sales by the sales territory country is only displaying the countries associated with the European Sales Territory group due to the View as roles selection:

View as roles in Power BI Desktop

Similar to the View as roles feature in Power BI Desktop, a Test as role option is available in the Power BI service. This feature can be accessed from the ellipsis next to each RLS role in the Security dialog for the dataset. Additionally, other users can test the security roles by connecting to published Power BI apps. In this testing scenario, the user would not be a member of the app workspace, but a member of an Azure Active Directory Security group which is mapped to a security of the dataset.

Individual users and groups of users are mapped to security roles in the Power BI service. For this project, and as a strongly recommended general practice, Azure Active Directory (AAD) security groups should be created for the users accessing Power BI content. The following screenshot from AAD displays the properties of a North America Sales security group:

The Azure Active Directory security group

Users can be added or removed from AAD security groups in the Azure portal or via PowerShell scripts. In the previous screenshot, the Assigned membership type is used but alternatively, a Dynamic User membership type can be created based on a membership rule query. With Dynamic User AAD security groups, a user can be automatically added or removed from groups as their role in the organization changes.

The AAD security groups can then be mapped to their respective security roles for the published dataset in Power BI. In the following screenshot, the North America Sales AAD security group is recognized as a potential group to be added as a member of the North America Row-Level Security (RLS) role:

Member assignment to Row-Level Security roles

With the Azure AD security groups created and mapped to their corresponding RLS roles of the Power BI dataset, security filters will be applied based on the user's membership of the Azure AD group. When RLS roles have been applied to a dataset, the users accessing the reports and dashboards based on that dataset will need to be mapped to at least one of the roles. For example, if a Power BI app is distributed to a user who is not included in one of the Azure AD security groups mapped to one of the RLS roles, and this user account is not mapped individually to one of these RLS roles, the user will receive the following error message in the Power BI service:

Error message: User Not mapped to an RLS role

In the event that a user is mapped to multiple RLS roles, such as both the North America Sales Group and the Europe Sales Group, that user will see data for both Sales Territory groups (and not Pacific Sales Group). For users that require access to the entire dataset, such as administrators or executives, an RLS role can be created on the dataset that doesn't include any filters on any of the tables. Chapter 11, Creating Power BI Apps and Content Distribution, and Chapter 12, Administering Power BI for an Organization, contain additional details on Azure AD's relationship to Power BI and the role of security groups in securely distributing Power BI content to users.

Table of Contents for Security roles

Create new playlist

Sign In

Sign Up

Table of Contents for
Security roles