One of the most common requirements to arise in BI projects is related to information security. When several users access the same report, it is often necessary for each user to have access and see only the data that competes with them.

For example, a company that has branches in several countries generally has managers in each branch, possibly managers by region or continent and also a global manager. There, a hierarchy for information security is established. The global manager must have access to all data from all branches, while the manager of one of the branches can only see sensitive data for his branch only.

Given this context, the resource that allows us to solve this requirement is called RLS, or Row-Level Security.

In this post, we will demonstrate how to configure RLS for reporting solutions consumed by Power BI Service, the Microsoft portal, at app.powerbi.com. We specify this because there is a difference in part of the process when it comes to Power BI Embedded.

First, we need to build a user table in the model. Below is an example consistent with the explanation of the first paragraph.

As, in this example, we have a defined hierarchy, one way to model the problem is to build an intermediate table (between ‘User’ and ‘Sales’) with the macro-region and region relationships, as can be seen below.

Below we show the ‘Sales’ table that we have built.

Next, we define the appropriate relationships, in a unidirectional way, so that ‘User’ filters ‘Region’ which in turn filters ‘Sales’.

Below, we demonstrate the creation of the RLS rule.

First, we must go to the “Modeling” tab and click on “Manage Roles”. A window will open, as shown in the figure. We then created a new Role (Function) that makes the model identify which column of which table will be considered when crossing with the email of the user logged in the Power BI Service. The function used for this in DAX is USERNAME ().

Before publishing the report, we can test whether the rule is being applied properly in Power BI Desktop. Still on the “Modeling” tab, we must click on “View as”, choose which RLS rules you intend to test and simulate the use of a specific user.

When clicking on “OK”, the report is already filtered correctly, as can be seen in the image below:

Having verified that the rule is working properly, we can publish the report, so that we can configure which users will fit into the created RLS rule. With the report already published, we can open the Power BI Service portal at app.powerbi.com.

In the workspace where the report was published, we now have 2 more objects: the report and the dataset that the report uses as a source. We must then click on the three dots next to the dataset and then on “Security”, as shown below.

From there we will go to another page, where we will add users or groups in our RLS rule.

After selecting all the users of interest, we must click on “Save”. Having done this, we should click on the three dots next to the name of the RLS rule and then on “Test as Role”.

On this page, we verify that, because you are logged in as ravi.quast@qdois.com.br, the report already shows only the data that correspond to this email. Here we could test how the view of one of the other 4 users who were added to the Role RLS Manager would be. For that, it would be enough to fill in the field “Select Person” with the e-mail referring to the user of interest.

We’ve completed all the necessary steps to set up row-level security in our report. Note that how the Users table filters data depends on each application. If our ‘Sales’ fact table had a column that identified the managers responsible for each salesperson, we could create an RLS rule using the managers’ ID, instead of using the macro-region. If we still had a table of salespeople, we could filter ‘Sales’ directly by the ‘Sales’ [Salesperson ID] column. In this case, we would create a Role so that salespeople could access the same report and analyze only their own sales data.

As a curiosity, when configuring RLS rules for a Power BI Embedded application, you can pass any type of data, via code, to the USERNAME () function. When creating the Role in Power BI Desktop, you must wrap it with the VALUE () function, if the value is numeric. To make it clearer, you can configure an RLS rule with a numeric ID for any filterable data in the report. In addition, both in applications where the consumption of reports will be by Power BI Service, as well as in applications where this is done by Power BI Embedded, it is possible to create and use more than one Role at the same time. There are cases in which we want to apply an RLS by ID’s not directly related to users and cases in which, due to some limitation or specificity of the model, we have more than one user table (3 tables for example: one for managers, one for customers and one for consultants).

Note
It is very important to note that the types of access given to users of a workspace override any RLS rules configured in the reports. For example, if a user has access to the workspace as an Administrator, Member or Contributor, the RLS rules will not apply to him, who will be able to see all the data present in the dataset. This is because all these types of access are allowed to edit the reports. In order for this not to happen, it is necessary to create the workspace application and access to users to be given to the application. If the user also needs access to the workspace, care must be taken to ensure that it is a Viewer.

We hope you have clarified the process of configuring RLS rules in general, more specifically when the report is going to be consumed via Power BI Service.

For more content on Power BI, Data Analytics and Productivity, follow us on our social networks!