In this blog lets see how we can implement OLS or Object Level Security in Power BI.
In simple terms the difference between RLS and OLS is that in RLS you prevent users from accesing certain rows, whereas in OLS you prevent users from accessing columns and tables.
To begin with you need a model in Power BI:
Then you also need to have Tabular Editor Installed, you can use version 2 or version 3. I am going to use TE3 because it has high DPI feature which doesn't blurs the UI when the screen is scaled to 125%, which is helpful for taking screenshots used in a blog.
Now, navigate to Modelling tab on the ribbon and click on Manage Roles under Security option.
Add a new role using the Create button and then click on Save, just don't type any DAX expression in here. Everything after this point needs to be done in Tabular Editor.
Now open Tabular Editor, either directly from system or from within Power BI
Once Tabular Editor Open, click on the Role that you have just created and in the properties pane you will see that against Table Permissions it says "OLS enabled on 0 out of 10 tables", 0 because we haven't restricted access to any tables yet.
Next, expand the Table Permissions section and start setting the properties to none of the tables that you want to restrict.
Now if you notice we get an expansion arrow to the left of the Role, expand it and you will see table names there.
Now, save the model and go back to Power BI.
Build a report with one column from a table you have restricted and other one from the column that isn't restricted.
Now got to Modeling table and click on View As option under Security, then select the role that you have created and click on OK.
You will notice that the report is now broken.
The tables Dates and Categories are no longer visible in the Data and Report view.
Just remember this isn't applicable to the Model view
Implementing OLS on Columns
It is highly likely that you will sometime need to restrict access to only specific columns and not the whole table so lets go back to Tabular Editor and un-restrict the tables.
As you can see in the below image everything is back to how it was before we modified anything.
Now select a column that you want to restrict and go to Table Permission in Properties window and set the option to None.
Now save the file and go back to Power BI.
Right now I am not viewing as the role and you can see Unit Price column is visible.
If I start viewing as the role then you can see the column is no longer visible.
Now we need to publish the model to Power BI service and then test the OLS.
Testing OLS
To test the OLS I have built a simple matrix with Unit Price and Total Sales.
Now go to your report and select the Security option
Now click on the elipses and select Test as role option
You can see that the report is broken because this role doesn't have the access to Sales[Unit Price] column.
The error also confirmt that.
Comments