Tag: Security

Power BI Object-Level-Security

Power BI GuyLeave a comment

Many users are aware that Power BI offers Row-Level-Security to secure their data. As example you can use RLS so users from a specific country can only see the sales numbers from this country and not others. I did already a blog post about the different possibilities (see https://pbi-guy.com/2021/10/12/power-bi-row-level-security/ & https://pbi-guy.com/2021/10/15/power-bi-row-level-security-organizational-hierarchy/ & https://pbi-guy.com/2021/10/15/power-bi-rls-configuration-in-service/). But many customers don’t only want to secure on a row-base they also want to secure their data on a “column-” or “table-base”. And exactly for this purpose Power BI offers Object-Level-Security. Let me walk you through how to set up OLS in Power BI.

Prerequisites

  • Power BI Desktop
  • Power BI Service Account
  • Tabular Editor

How to

To enable OLS we start in Power BI and create first a data model. I’m going to use my standard Sales Report with Wide World Importers sample data. Further I created three visuals with Text boxes to show the different OLS options – No OLS applied, only on one specific column (customer), and on the whole table (dimEmployee). Every visual shows the Profit by different dimension. First one by Sales Territory, second one by Buying Group, and the third one by Employee.

As a next step we have to create the different Roles so OLS can be applied to it. Go to the Modeling Tab in the Ribbon and select Manage Roles.

In here I created two different roles – one where only the OLS for the column Customer should be applied and one where the whole Table dimEmployee should be secured. No DAX expression or anything else is needed – just the two empty roles. Once done hit the Save button.

After the test page and the roles are set up I connect to my model with Tabular Editor by selecting it through the ribbon External Tools.

Pro Tip: If you open Tabular Editor directly from Power BI Desktop you’ll be automatically connected to your data model.

Once Tabular Editor has opened you should see a similar screen like the below.

As a next step I expand the Roles and select first the “OLS on Table dimEmployee”. Once the role is selected in the property pane you see a property “Table Permissions” in the “Security” section. Expand it and configure “None” to the table which should be secured. In our case it’s dimEmployee. This means that every user who will be added to the “OLS on Table dimEmployee” role afterwards will not see any data coming from the dimEmployee table.

Now I select the other role and instead of “None” I set the dimCustomer Table to “Read”. The reason is we just want to secure one specific column and not the whole table. Therefore the table can be read in general but we have to configure specific columns which should be secured. After you set the dimCustomer table to read the role can be expanded on the left hand side which lists all tables in “Read” or “None” mode.

Next select the dimTable below the role, head over to “OLS Column Permissions” under “Security” in the property pane and set the column “Customer” to “None”. Every other column will use the Default behavior of the table which is “Read”.

After we set up everything now in Tabular Editor we can save our model and close Tabular Editor. Back in Power BI Desktop let’s test our roles. First I test the “OLS on Table dimEmployee” role by going to Modeling – View as – selecting OLS on Table dimEmployee – and hit OK.

We see that our OLS works because the right hand visual is not showing anything. Further the whole table dimCustomer is also not visible.

That’s exactly what we expected – great! Let’s test the second role. After we switched the view every visual is showing up but the “Customer” field in the table “dimCustomer” is hidden. This is also expected as we’re not using the Customer field in our report so far therefore everything can be shown.

Let’s turn of the role view and replace the “Buying Group” column with “Customer”.

Than we enable the role view again to see if security applies.

And as we can see yes it does! Because the visual is using the column Customer now it’s not showing up.

As a last step you would need to publish the report to the Service and assign user / groups to the desired role. One user / group can also be added to multiple roles if needed like with RLS.

Personally, I find the OLS very useful to secure your data model but the message which appears to end user is not very user friendly. I would love to see an update here which says at least it’s secured instead of “Something went wrong” because as an admin it’s expected behavior and not wrong. Best option would be if I could configure the message as I wish.

Please let me know if this post was helpful and give me some feedback. Also feel free to contact me if you have any questions.

If you’re interested in the files used in this blog check out my GitHub repo https://github.com/PBI-Guy/blog

Power BI RLS configuration in Service

Power BI Guy1 Comment

In my last two posts (see here and here) I wrote about Row-Level-Security (RLS) and how to configure it in Power BI Desktop. This time I would like to show how easy it is to publish a report to the Power BI Service and use the defined roles for specific users or security groups.

In this post I am going to use my last Power BI file with the RLS organizational hierarchy. As you guessed it right I am a big Star Wars fan and will use those characters for my demo. The picture below shows the current hierarchy.

Of course this hierarchy is not representative for the characters strenghts, popularity or similar. 🙂

The Power BI Report itself didn’t change a lot. Let’s imagine Obi-Wan is the author and creates a report with a table including all employees (Name and ID) and their salary, a card with the salary information and the current user ID. By pressing the Publish button in the Home Ribbon he can publish the report directly to his workspace in Power BI.

In this case Obi-Wan would like to publish it to the “PBI Guy” workspace. The publishing process will take a few seconds. Afterwards a success message will appear similar to the one below. With a click on the hyperlink the Power BI Report automatically opens in Power BI Services.

Once Power BI Services has loaded in Obi-Wan’s default browser he can extend the PBI Guy Workspace, hit the three dots of the Dataset and choose Security.

Now all in Power BI Desktop created roles are available and Obi-Wan can add users our groups to it. Because he created just one (Hierarchy) only one is available. Thanks to Azure Active Directory (AAD) suggestions are made while typing. For now he adds Luke and Yoda to the Hierarchy role. You can also add Security, Distribution, and Mail-enabled Groups. Once members are added they will be listed below.

Keep in mind that RLS works only for Viewers and users who has build permission on the dataset. Admin, Member, and Contributor of a workspace are not affected from RLS!

After saving one final step is required – test if RLS works as expected. To do so hit the three dots of her Hierarchy role and choose Test as role.

Choose the little arrow at the top and select on which behalf you would like to test the report. In my case I choose Luke and hit Apply afterwards. I do not select the Hierarchy role as it would test it than on my current logged in user.

As we can see RLS works perfectly fine!

One last test as Yoda confirms that RLS is working.

Now I can share the report with all users and only those who has the right permission will see what they are allowed to see – fantastic!

Please let me know if this post was helpful and give me some feedback. Also feel free to contact me if you have any questions.

If you’re interested in the PBIX and Excel file used in this blog post check out my GitHub repo https://github.com/PBI-Guy/blog