Tallan's Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

Row-Level Security in Power BI: Part 1 – Roles and Users

James Arey

Securing Reports

Let’s say the Finance Department of a clothing retailer has some great reports that let them see all the sales across the United States; so great, in fact, that they want to share them with all Regional Managers so they can communicate about the hot spots in their region. The problem is the Regional Managers aren’t permitted to see data outside their region, and giving them access to these reports would allow them to filter to any region they wanted. We could create separate Datasets and reports filtered to the region for the manager that is given access to them, but that would be time-consuming, and a nightmare to maintain. Luckily, Power BI provides the ability to implement Row-Level Security (RLS).

So, what is RLS? Simply put, it controls a user’s access to each individual row of the Dataset. In Power BI this is accomplished filtering the data in DAX based on the user. In the example below, we can see Billy requesting a list of all the states; Power BI then matches Billy to his roles and filters the states down to the ones in his roles.

 

RLS Example

Billy opens a Report with States on it

 

In the report, we might have a page that shows sales data by location, and we want our Regional Manager to only see the sales data in his region.

Roles in Power BI

In Power BI, RLS is defined in Power BI Desktop through roles; roles define DAX filters for whichever tables they are intended to secure. This means that we need to be careful with the structure of our Data Model to ensure that we are filtering on the highest-level entity possible. In the example below, we see a Dataset similar to the scenario above and a report that it drives.

 

Role RLS - DataModel

Access to Geography is restricted by selected Regions, and these Geographies can, in turn, restrict the Sales

 

Unsecured - Regional Sales

Report with no Roles applied

 

 

Creating Roles

Now that we have the appropriate structure set up for the data to be able to filter by region, we will need to set up our roles. In Power BI Desktop this can be done by going to Modeling > Manage Roles, we can then create new roles in this view on the left and define their table filters to the right.

 

Manage Roles

Here we can add the two Roles from the example above

 

Now that we have a couple of Roles set up, we can use Power BI to test them out using the View as Roles button next to the Manage Roles button.

 

Roles - View as Roles

Here we can see the two Roles we added earlier and select those we would like to test

 

Roles - View as Mountain

Report as someone with the Mountain Role would see it (Note the bar at the top denoting the Roles that we are viewing the Report as)

 

Roles - View as Mountain & New England

Report as someone with the Mountain and New England Role would see it

 

 

Dynamic RLS

In addition to locking down access to tables with hard-coded filters, we can also leverage security that is built into the data. Let’s say that we are sourcing this data from an application that handles security in its database by granting user’s access to specific states. In this case, we can bring in this security information and use these relationships to filter data by who is viewing the report. Below, we can see these Security Tables brought into Power BI; the User Table (greyed out because we are hiding it in the final report) filters the State User Table to a combination of the user that is logged in and the state’s data they are permitted to see. This table further filters the State Table, which then filters the Geography Table.

 

Dynamic RLS - DataModel

Note the relationship between State User and State; State User will likely have many users that will be able to see the same state, so, this is a one-to-many relationship, and in this instance, the State Users are the many. Normally, in Power BI, it is the one side of a relationship that filters down the many sides (the same way that state filters geography) but, for our purposes we want the State User to be able to filter the states. To achieve this, we must make the relationship filter in both directions and apply the security filter in both directions.

 

Dynamic RLS - Cross Filter Relationship

Here we see our Guest User has access to CT, NY, and MA

 

Now that the Data Model is set up, we need to create a role that will be able to filter by the logged in user’s name. To do this, we can create a role that filters the user using the DAX function USERPRINCIPALNAME(); not to be confused with USERNAME() which may be the Domain\Login (Depending on your AD,) the USERPRINCIPALNAME() returns the user’s email address.

 

Dynamic RLS - Role

At this time, anyone with this role applied will have the User Table filtered by their email address and subsequently, will have the rest of the tables down the line. To test this, we will have to define who we are logging in as to see the report they will see.

 

Dynamic - View as Roles

We set our UPN in the Other user field (as you can see this is case-insensitive)

 

Roles - View as Dynamic

Now we only see the three States our Guest has access to

 

 

Assigning Roles

Now that we have all of our roles created we will need to assign them to the appropriate users.  We will need to publish a PBIX to a Workspace in the Power BI Service; then we go to Datasets [Our Dataset with RLS]  > Security. Here we can assign users to roles; we select the role we want to add to and start typing, Power BI will be able to filter by email or name within your organization. Note that anyone that is not an admin viewing a report that they have no roles assigned to, but that uses RLS will not be able to load any visuals.

 

Assign Roles - Dataset

Assign Roles - Assignment

Here we have given the PowerBI Guest user the Mountain Role

 

Guest User - App View Mountain Role

When PowerBI Guest views the App all that is shown is the Mountain Region data

 

These Security Roles will be applied to anyone viewing the report in a Published Power BI App, and changes to the security will not require the app to be published again. These roles will also affect anyone that is a member of the Workspace; provided that the Workspace is set up to allow members to only view content and not edit.

 

Workspace Configuration

Stay tuned for Row-Level Security in Power BI: Part 2 RLS in Embedded Reports; in which we will go over handling RLS for you reports embedded in your internal and customer-facing Applications.

_________________________________________________________________________________________

Feel free to start a conversation below or visit our BI team’s page to learn more about how Tallan can help your organization get the most out of Power BI and your existing data infrastructure.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\