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:
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.
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:
To solve this, I create a workspace with Premium Per User capacity and then publish my report to that workspace.
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:
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.
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.
By selecting the menu of a role, you can test the role without actually adding members.
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:
Take care.
