Row Level Security (RLS) and Object Level Security(OLS) in Power BI
This article will dive into more detail about RLS and OLS how to use and when to use each.
RLS(Row Level Security) name tells us it is used to secure the row’s data.
OLS(Object Level Security) secures the column or table.
RLS is two type
- Static RLS
- Dynamic RLS
For illustration purposes, I will use a small example of a region-wise sale.
Problem Statement: Display the sales data of each region such that the sales figures are shown for their respective regions
we will solve it by using static RLS.
for that, we will manage the role in the modeling tab on the home ribbon.
- Go to the modeling tab
- Click on manage roles.
- In the manage security roles window create roles for respective regions by adding new roles.
- By selecting each role add a filter for a particular region as shown in the below image and save the page.
5. to view whether the role is working properly we can click on the view roles tab
Now publish it to your workspace in Power BI Service and manage access to determine who can view the report.
And here’s an important point to note: Only viewer access should be given to the user to apply RLS.
After that click on three dotes of the semantic model of the report and go to Security
Assign the RLS to the respective region member and click on the save button
Now, when a member assigned to view the central region’s data opens the report, they can only see the data of their respective region.
Limitation: In the above example, we only have four regions to assign RLS. However, if we need to apply RLS to states and cities, manually entering roles for each city becomes cumbersome. In such cases, Dynamic RLS can be used.
Dynamic RLS
For Dynamic RLS we require a user access table containing the user mail ID and respective state they can access usually, we will get this data in Excel format but in this example, we will directly enter the data in a table.
In the modeling tab, we have to make a relationship between two tables
Similar to Static RLS now we have to manage the role but the difference is here we will use the “USERPRINCIPNCIPALNAME” DAX function to the role and save it.
and also create a measure to check the user value
when we drop that measure in the report it will display our system name on the desktop but when we publish it in the Power BI service and repeat the same steps that we have done in static RLS such as managing access and assigning RLS in the security tab of semantic model it will display the user-id/mail-id of the logged in user.
OLS (Object Level Security )
As discussed earlier, RLS is used to secure data by Rows, while OLS is used for Columns and Tables.
We can’t directly use OLS in Power BI. To achieve this, we need to use the external tool/third-party tool Tabular Editor. you can install it https://tabularediat tor.com/ and connect it with Power BI.
Problem Statement: In a table containing State, Sales, Profit, and Qty, the requirement is that when we apply OLS (Object-Level Security) to the Profit column or the entire table, the data should not be visible to the user.
Similar to RLS, we also need to manage the role in Power BI desktop and Power BI services.
In Tabular Editor if we select the order table, set OLS as None and save it. it means that the user has not been given access to see the complete table of data
And when the user tries to view the data, they will not see any records or will encounter restricted access
But now we want to apply OLS on the Profit column in the order table
Now you can see that the user can access the table only which does not have a profit column.
Conclusion
As per our project requirements, we can apply security on rows, columns, and tables using RLS and OLS
I hope you found this article helpful! If you did, please consider clapping to show your support. If you have any questions or thoughts to share, feel free to leave a comment below. I’d love to hear your feedback!
Happy Learning…!!!
Don’t forget to subscribe to
and join our Power BI community
