Secure the Sensitive Data in Power BI; Data Masking better with Row-Level Security

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

In my previous article/video, I explained how you can hide sensitive data from the user’s view. The method I explained there is a simple method, however, for a specific type of user. If you have users who are using Power BI Desktop to create reports on top of your model, and they are going to create DAX measures, then there is a better and more secure way. In this article, I am explaining that method.

Data Masking

If you haven’t read my previous article about the data masking, I just briefly explain that concept here:

Sometimes, you want to show all the information to all the users, but you want to hide the sensitive data, such as other user’s names, emails, any other sensitive information. In this article I explained a simple method about that;

Which Type of the User

The method mentioned above, is good for end-users, and everyone who uses either Power BI service or Power BI Desktop just to create visualization on the report, with no edit access to the dataset.

However, if you are dealing with a type of user who wants to create DAX measures from the Power BI Desktop using Get Data from the Power BI dataset, there would be a problem. Hidden columns and tables are not hidden from the DAX expressions. This means if a user can create a measure (even report level measure), that user has access to those columns.

Hidden columns and tables are not hidden from the DAX expressions. This means if a user can create a measure (even report level measure), that user has access to those columns.

In below, I have used Get data from Power BI dataset option, and you can see that the hidden columns are not visible in the list of fields. However, when I want to write a report level measure, they are accessible!

If you have this type of user, then it means there is a security breach, because user can write a measure and get the data columns that they did not supposed to see!

Row-Level Security

What is the solution? how you can really secure the data from even that type of the user? the answer is with row-level security.

I wrote several row-level security articles so far, and a book. If you are looking for some links to learn more, here are some;

Row Level Security in Power BI

Dynamic Row Level Security

Dynamic Row Level Security with Manager Level Access

Dynamic Row Level Security with Users and Profiles

Dynamic Row Level Security with Organizational Hierarchy

Row Level Security with Analysis Services Live Connection

Row level Security with Many to Many and Organizational Hierarchy

Calculating Totals in a row-level security implementation

The problem in the implementation of the data masking, can be solved using RLS. Here is a solution for it step by step.

Create a Copy of the Table with the Confidential Data (Not in DAX)

The first step is to create a copy of the table with the confidential data using a method which is not DAX calculated table. Why not DAX calculated tables? because we are going to remove the columns with the confidential data in it in the next step, and if the columns are removed, then DAX calculated tables won’t be able to get them anyways.

I use Power Query to create a duplicate of that table;

The duplicated copy, I renamed it as Current User Table (you can call it anything, this table would be hidden later on);

Remove the Confidential Data Columns

Now that this table has all the confidential data, You can remove the confidential columns from the source table; Sales Rep table in this case.

This table has now one column only, the ID, because it is needed to join to the sales transaction table. Now you can load the data into Power BI.

Remove the Relationship

This newly created table “Current User Table” should be disconnected from the rest of the model. Remove the automatic relationship (if created), and then hide the table from report view.

Setup RLS for the Current User Table

The “Current User Table” is the table that we are going to apply the row-level security on it, because this is the table with the confidential data, and user should only see their own data from it.

The RLS logic would be a very simple dynamic RLS filter as below;

Now this table would be only showing one row of the data for every user logged in.

DAX Measure for the Confidential Data

There are many different ways that you can write the DAX measure, here is one of those to get the Name of the sales rep;

This would lead a visualization like below;

This, of course, shows the confidential data (current user’ column) of all users. However, that is expected, because this is the view of the user with Edit access.

Publish and Set up RLS in the Service

The last piece of the implementation is to publish your solution to the Power BI service (or Report server if you are using PBIRS), and then set up the RLS on the dataset in the service. This article explains the details of it;

Assign users to the roles. I’d suggest create an Office365 group for everyone and add that group here because users will see only their own information. for my sample implementation thought, I just assigned my other user to it.

The User’s View

The user’s view would be exactly as expected, They would see all the data, but only their own confidential data;

Even a pro user (a user who can create report level measure, with no edit access to the dataset), won’t be able to access the data of others, because that table is secured and filtered by the RLS logic

Alternative Method; Single-Directional Relationship

There is a part of the implementation, which you can change, and keep the relationship between the “Current User Table”, and the “Sales Rep”. But that relationship needs to be single-directional, towards the “Current User Table”, otherwise, this would filter the entire sales data, which is not what we want.

However, note that this relationship comes with pros and cons. The benefit would be, you probably won’t need many DAX expressions for every confidential data field, and you can un-hide the “Current User Table”. The disadvantage would be; sometimes this relationship won’t work with other relationships because of the way that it filters the data.

Summary: Which Method

In summary, the two methods of data masking I explained in this post and the previous one, has their own pros and cons.

The previous method, which works without RLS is only working for users who are not going to create report level measures (mainly report visualizers or end users). That method, however, is very simple to implement.

The method I explained in this post that uses RLS, is more secure of course, and it works for the scenarios that even users are able to create report level measure (but not for users with the edit access on the dataset). However, it is more complex to implement. even reading this post itself, showed the process is longer than the previous one.

I hope you enjoyed reading it, and enjoy the solution too. If you have any questions, please feel free to write it down in the comments below.

Download Sample Power BI File

Download the sample Power BI report here:

Enter Your Email to download the file (required)

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply