Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Power BI Row Level Security


By:   |   Updated: 2019-09-09   |   Comments (2)   |   Related: More > Power BI

Problem

How do I use Row Level Security in Power BI?  Is it true that I need to use Roles to manage security?  Where are the permissions granted?  Can you walk through an example demonstrating how to setup Power BI Row Level Security?

Solution

Of course, security is on the mind of everyone especially as the amount of data has continued to grow; with the privilege of so much data, we need to be clear about what data can and cannot be viewed by those individuals who are consuming the data.

Security in Power BI comes in several forms and levels. First, of course, the appropriate license (pro vs premium) is required to access the Power BI Service on the cloud (or to use the related on-premises resources in Power BI Report Server). Next, access must be granted to datasets, reports, and more appropriately the APP housing the dashboard, reports, and datasets. This level of access is centered on access to the whole report or dashboard. The final layer of access at the data set level centers around row level security. Although a bit of a misnomer, as it is not truly row level (due to possible aggregations and such at the data level), the row level security features do work on a line by line level by providing a way to restrict access at the data level based on specific values in a dataset. Certainly, with this sort of access, some limitations do exist which we will outline in this tip.

The best way to see the benefits of using row level security is through a sample case study. However, before we get into the example, you need to be sure to download the latest version of Power BI desktop from here. Additionally, we will be using the WideWorldImportersDW database as a basis for our data sources; this database can be downloaded from GitHub.  If you need a refresher on bringing data into Power BI please see this tip.

Create a Power BI Report to Setup Row Level Security

To help get us started, I created a Power BI report file and added two tabs to the report. The first tab contains a bar chart and slicer while the second tab includes a matrix and another bar chart, as illustrated in the next two screen prints.

Report Page 1
Power BI Page 2

Now that the report is set up, we can begin the process of setting up row level security. The process overall is a two-step process with step 1 taking place in Power BI Desktop and step 2 occurring online either within the Power BI Service in the cloud or on a Power BI Report Server. Both steps must be performed, or the security will not work as desired. 

Setting Up Roles in Power BI

The first step actually requires the use of roles. Each role is established by naming the role and then using a quasi-filtering process to establish what specific data that role has access to see.  Of course, if you have many roles, maintenance could be a challenge as could be assigning report consumers to these roles.  However, the alternative of setting up a user access cross reference table could be equally as daunting. 

To setup the roles, you must select the Modeling tab, and then Click on Manage Roles, as shown below.

Modeling Tab

The process continues by first selecting the Create option and then naming the role, Far West in the below example. Be sure to name the roles appropriately and consistently as it will be used in Step 2. After naming the role, we need to select which table will be utilized to limit the assigned roles level of data access. Next, select Add filter and finally select the field that will be used for filtering. In the below illustration, Sales Territory from the Dimension City table is selected as the table / field to be used for row level security.

Manage Roles

Once Sales Territory is selected, the Table filter DAX expression field is prepopulated with the column name and = "Value". The "Value" argument must be replaced with an actual value that this role will be limited to seeing.

Manage Roles table filter

For this illustration, "Value" is replaced with "Far West" as the sales territory data filter for this role. Additionally, you will notice that a total of ten roles were created based on the various sales territories in the data.

Thus, in a similar fashion, we can see that the Great Lakes role uses the Sales Territory of Great Lakes. We follow this same process for each of the rest of the roles we would like to create.

Manage Roles Great Lakes

In the upper right corner of the DAX expression box, you will notice a checkmark and a x button. The checkmark button validates the DAX expression that was entered whereas the "x" button acts as an undo button by reverting back to the original value for the filter. Please note that the "x" revert button only works while actively working on the role filter; if you save and exit the manage role window, the revert option is no longer available.

As shown below, the addition of two random exclamation points creates an invalid DAX statement. Utilizing the checkmark, we can see three places that indicate where a problem exists: 1) under the Manage Roles column, 2) under the Tables column and 3) under the DAX expression box where a full error message is given indicating the location of the problem.

Incorrect DAX

If you attempt to save the role with the incorrect DAX, you can save it, but you will receive both a warning error message upon saving, as shown next.

Filter Error

Additionally, on the main design grid, a warning message is also displayed which indicates an issue exists with the role security filter.

Manage Role Error.

Some other items to mention about the manage roles process is that you can actually add multiple DAX filter expressions to a particular role. As shown in the subsequent screen print, the GreatLakes and Plains role actually includes both the Plains and Great Lakes sales territories.  Double bars, "||" act as an OR statement in the DAX expression.

Alternately, we could use the IN keyword for the DAX, as shown below.

DAX IN Keyword

We could create an "AND" condition by, for instance, adding a second filter for a certain zip code which is illustrated in the below screen print. Also notice how two filter icons appear in the Tables column.

2nd DAX filter for Role

It is easy to see how the possibilities are endless on creating various role-based security filters and functions.  We do need to be careful about creating a large number of roles as it could get unmanageable if one is not careful in the definitions.  There are certainly ways to work around these issues.

Now that we have created several roles, but before we publish the dashboard(s) and report(s), we will likely want to test our setup. Fortunately, the View as Roles button allows us to complete just such testing.

 Modeling Tab-View as Roles

Clicking the View as Roles button opens the view as roles selection window.

View as roles


By clicking on the Far West role, we enable the filter for that role.

Far West role

Now only the Far West sales territory profits are shown on the visual, only the States \ Provinces in the Far West sales territory are shown on the slicer, and the legend only shows Far West.

Far West Report

The role is applied to ALL pages on the dashboard or report that use the selected data sets. Unrelated data sets and queries would not be impacted.

Report page 2 Far West

We can also see what occurs if someone is part of multiple roles, Far West and New England, in the below example.

Far West and New England

A report or dashboard consumer belonging to both roles would see both sets of data, acting in an OR fashion.

New England and Far West Roles combined.

Under Manage Roles, selecting the None option reverts the report designer back to seeing all data.

At this point, we are ready to publish our data to the Power BI Service in the cloud; once we publish to the cloud, we will need to move on to the second part of the Row Level Security process.

Setup Security for Power BI

Now that the data set and report are published, we would want to navigate to our Workspace where the data set resides. Once in the workspace, you will find the data set involved and select the security option.

Power BI Service Dataset

Now, individual users, distribution lists, or active directory groups can be added as members of roles. This process is where we assign actual users or groups to the roles we have defined; hopefully you can see the importance of naming the roles appropriately now. It would be very beneficial if the members added were active direct groups created specifically to add users to set roles, sales territory in our example. Thus, if an active directory group already was established for the Far West views, we could simply add that AD group. Of course, individuals can also be added, but that becomes an administrative burden if the users with access becomes substantial.

Add Members to role, enter email address

As you type an AD group name, a distribution list, or a user's ID or email address, Intellisence automatically shows you a list of available users or groups. Once you enter all the groups or users, the next step is to click the Add button.

Add members to roles

Finally, you must click save to save additions that were made to the role membership.

Save Add Members to roles.

You can also click on the X next to the user or groups name to remove a particular user. However, those changes must also be saved.

Delete Member

User or groups can be added to multiple roles, but these additions have a combining effect by adding to the data that a group or user can view.

Our final step in the process is to once again test how our role is working. Click on the ellipse button next to the Role name, allows for the selecting the Test as role link.

Test as Role

As shown below, we can see that the role is set properly, with only the Great Lakes and Plains sales territory showing.

Test as Role

Summary

One question that comes to mind is what happens when a user has access to get to the report, dashboard, or APP, but has not been assigned as a member of a role.  That individual will see NO data. Also, if a user had edit permission for the report and dataset (generally a content administrator for instance), then that user will see all data and can change their own membership within a role.

As you can see Row Level Security in Power BI could really be called Role Level Security; it is a powerful tool that can be used in conjunction AD groups, email addresses, and user ID's in such a way to create limited access via a role-based filter.

Next Steps


Last Updated: 2019-09-09


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, September 09, 2019 - 10:59:28 AM - Scott Murray Back To Top

You can certainly use a UserSecurity table that assigns each user to tables they can seen.  Then you can use the user lookup to that table as a way to control the data to be viewed.  Of course, someone has to maintain that table in one fashion or form.


Monday, September 09, 2019 - 10:20:21 AM - Patrick Murphy Back To Top

Nice How-To for basic row level security. I would love to hear your ideas on the next level of Row-Level Security.

The idea is a global data set from a Gateway into a corporate workspace. Hundreds of users access the report/dashboard and the data needs to be filtered based on the user's country in sucha away that the user can only see their data.


Learn more about SQL Server tools