Implementing Row Level Security in PowerBI

Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data at the row level. You can define filters within roles.

You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using DirectQuery, such as SQL Server. Previously, you were only able to implement RLS within on-premises Analysis Services models outside of Power BI. For Analysis Services live connections, you configure Row-level security on the on-premises model. The security option will not show up for live connection datasets.

Define roles and rules within Power BI Desktop

You can define roles and rules within Power BI Desktop. When you publish to Power BI, it will also publish the role definitions.

If you want to take advantage of dynamic security, you will need to enable the preview switch Enable cross filtering in both directions for DirectQuery. This will allow the ability to cross filter and apply the security filter in both directions.

To define security roles, you can do the following.

1. Import data into your Power BI Desktop report, or configure a DirectQuery connection.

   Note:

   You cannot define roles within Power BI Desktop for Analysis Services live connections. You will need to do that within the Analysis Services model.
2. Select the Modeling tab.
3. Select Manage Roles.
   
4. Select Create.
   
5. Provide a name for the role.
6. Select the table that you want to apply a DAX rule.
7. Enter the DAX expressions. This expression should return a true or false. For example: [Entity ID] = “Value”.

   Note:

   You can use username() within this expression. Be aware that username() will have the format of DOMAIN\username within Power BI Desktop. Within the Power BI service, it will be in the format of the user's UPN. Alternatively, you can use userprincipalname() which will always return the user in the format of their user principal name.
   
8. After you have created the DAX expression, you can select the check above the expression box to validate the expression.
   
9. Select Save.

You cannot assign users to a role within Power BI Desktop. This is done within the Power BI service. You can enable dynamic security within Power BI Desktop by making use of the username() or userprincipalname() DAX functions and having the proper relationships configured.

Validating the role within Power BI Desktop

After you have created your role, you can test the results of the role within Power BI Desktop. To do this, select View As Roles.

The View as roles dialog allows you to change the view of what you are seeing for that specific user or role. You will see the roles you have created.

You select the role you created and then select OK to apply that role to what you are viewing. The reports will only render the data relevant for that role.

You can also select Other user and supply a given user. It is best to supply the User Principal Name (UPN) as that is what the Power BI service will use. Select OK and the reports will render based on what that user can see.

Note:

Within Power BI Desktop, this will only display different results if you are using dynamic security based on your DAX expressions.

Manage security on your model

To manage security on your data model, you will want to do the following.

1. Select the ellipse (…) for a dataset.
2. Select Security.
   

This will take you to the RLS page for you to add members to a role you created in Power BI Desktop. Only the owners of the dataset will see Security available. If the dataset is in a Group, only Administrators of the group will see the security option.

You can only create or modify roles within Power BI Desktop.

Working with members

Add members

You can add a member to the role by typing in the email address, or name, of the user, security group or distribution list you want to add. This member has to be within your organization. You cannot add Groups created within Power BI.

You can also see how many members are part of the role by the number in parenthesis next to the role name, or next to Members.

Remove members

You can remove members by selecting the X next to their name.

Validating the role within the Power BI service

You can validate that the role you defined is working correctly by testing the role.

1. Select the ellipsis (...) next to the role.
2. Select Test data as role

You will then see reports that are available for this role. Dashboards are not presented in this view. In the blue bar above, you will see what is being applied.

You can test other roles, or combination of roles, by selecting Now viewing as.

You can choose to view data as a specific person, or you can select a combination of available roles to validate they are working.

To return to normal viewing, select Back to Row-Level Security.

Using the username() or userprincipalname() DAX function

You can take advantage of the DAX functions username() or userprincipalname() within your dataset. You can use them within expressions in Power BI Desktop. When you publish your model, it will be used within the Power BI service.

Within Power BI Desktop, username() will return a user in the format of DOMAIN\User and userprincipalname() will return a user in the format of user@contoso.com.

Within the Power BI service, username() and userprincipalname() will both return the user's User Principal Name (UPN). This looks similar to an email address.

Using RLS with app workspaces in Power BI

If you publish your Power BI Desktop report to an app workspace within the Power BI service, the roles will be applied to read-only members. You will need to indicate that members can only view Power BI content within the app workspace settings.

Warning:

If you have configured the app workspace so that members have edit permissions, the RLS roles will not be applied to them. Users will be able to see all of the data.