Power BI datasets and Object Level Security (OLS)

I once had a customer who had a large collection of data from different subsidiaries. These companies were only allowed to see their own, anonymized data, but the Security Officer of the main company also needed to see the Personal Information (PI) of all employees.

Eventually we “solved” this by creating two reports, which also means two reports to maintain. The February release of Power BI finally has a solution: Object Level Security (OLS).

In combination with Tabular Editor you can now control access to certain columns or even entire tables for certain roles. In this blog, I’ll show you how!

I created a simple table with ID, Name, and Phone number:

Information in my table

The next step is to create two roles, I call them consultant and Privacy Officer (PO). A consultant may only see the other names but not the phone number. The PO is allowed to see all the information. In Power BI, go to Modeling -> Manage roles > Create.

Create two roles in Power BI

You don’t need to express any filtering at this point, just create the roles. Now, according to the Microsoft blog I should be able to connect with Tabular Editor and apply OLS. To do this, go to External Tools tab and select Tabular Editor. Unfortunately, I can’t apply extra security:

Tabular Editor.

To solve this, I create a workspace with Premium Per User capacity and then publish my report to that workspace.

Workspace Settings
Publish the report

The next thing I can do is using the Workspace Connection to connect my Tabular Editor with the newly created workspace (you do need XMLA Read/Write for that!). When I am connected with my dataset, I can now use my OLS settings:

Security settings in Tabular Editor

In the table settings, I can change the permissions per role for complete tables, or only a column. I don’t want consultants to see all the phone numbers, so I change the phone column settings to None for the role Consultant.

Changing column to none for consultants

After you save the changes, we can go back to the Power BI Service and take a look at our roles. You can do this by selecting the dotted menu of the dataset and select Security.

Dataset settings

By selecting the menu of a role, you can test the role without actually adding members.

Test as role
Consultant can’t see phone numbers

Now as you can see, a table with phone numbers isn’t available for consultants, but it is for the Privacy Officer! This would solve my clients problem by just adding an extra tab in the report. Regular users can then open the tab, but won’t be able to see anything!

I’m still not sure why I can’t change the security settings before publishing it to a workspace. Maybe it isn’t converted to a tabular model before? Feel free to let me know if you got the answer!

Another thing: what if I want to be able to see my own phone number, but not others? Unfortunately, that's not possible at this moment. To solve this, the best way is to create another table without the phone number and add row level security (RLS) to the table with phone number.

You can create a relationship between these tables and create a measure:

_phone =
IF (
ISBLANK (
VALUES ( ‘Personal Information’[Phone] )
),
“Not allowed”,
VALUES ( ‘Personal Information’[Phone] )
)

The result is this:

As seen by consultant Erik
As seen by the PO

Take care.

--

--

--

Enthousiastic Power BI'er

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

{UPDATE} 魔法校園 (Magic School Story) Hack Free Resources Generator

Self-Sovereign Identity: Achieving privacy by design

Solving the cyber-security Gordian Knot

@RealTryHackMe #AdventOfCyber Series: Challenge 7 — Migration Without Security…

AMA, December 16: the CellVerse development and the updated roadmap of the project

Venturing Bug bounty: Beginners Look

New Contract Update: 21-Day Lock

Fileless attacks and the surrounding myths

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Erik Hamoen

Erik Hamoen

Enthousiastic Power BI'er

More from Medium

PowerBI | Extract data from SQL using Views

Process Data from Dirty to Clean: Week4

Consistency of Starting 11: A look at Shannon Entropy and Football

Tableau as A BI Tool