As Power BI developers, we should be familiar with functionalities within Power BI that allow for the implementation of various levels of data security; the most common example may be Row-Level Security (RLS). Object-Level Security (OLS) is a less common implementation. Perhaps this is because it is not a native feature readily available within Power BI. For some teams, however, OLS provides the critical component to securely share their Power BI dashboards.
In this blog, we’ll discuss OLS and how it compares to RLS and show a brief example of implementing it using a third-party tool called Tabular Editor.
What is OLS in Power BI, and Why Does it Matter?
Many of you are likely familiar with the security features built into Power BI.
1. Row Level Security (RLS)
2. Object Level Security (OLS)
Let’s see a bit about Row Level Security.
Row-Level Security (RLS) is a data security feature that restricts access to specific rows of data for designated users or groups of users. In Power BI, RLS enables us to ensure that users only view data within a dashboard they are authorized to see. This is achieved by applying criteria defined in the data model to control access at the row level within a table. The underlying logic filters the data, effectively hiding unauthorized rows by applying these criteria as the report is generated.
Now, Let’s jump into Object Level Security (OLS)
Object-Level Security (OLS) is a feature that restricts access to specific tables or columns in a data model based on user roles. Unlike Row-Level Security, OLS operates on the metadata of the data model rather than the data itself. This functionality can be applied to entire tables or individual columns, ensuring that users only have access to the structural elements of the data model that align with their permissions.
For example, let’s say we have a table with employee details. Let’s treat the Salary column as a secured column. The Salary column should not be exposed to everyone after applying OLS on the Salary column, which is present in the Employee Details table
In the final report, view:
1. Public Role – Salary column – Hidden.
2. Admin Role – Salary column – Exposed.
Assume Public and Admin are the 2 security roles created.
Use Cases to Introduce OLS in Power BI Dashboards or Reports
Many organizations have data tables that contain both confidential and public information. We can control access to the confidential column(s) by applying OLS to that table in the following way:
Prerequisites
OLS deals with tables and columns in a data model, and indirectly, it deals with the metadata of a data model. Establishing OLS requires an external tool or any
.bimeditor.Tabular Editor is a widely used model metadata editor that provides a simple GUI for editing the Model Metadata instead of making changes in the script.
Tabular Editor: Please follow this link and install the latest Tabular Editor with version
2.x
Note:
You can use any
Model.bimfile editor, which would suffice for editing the metadata.After successful installation, Tabular Editor is automatically added to the Power BI desktop as an External Tool.
If Power BI Desktop was already opened at that instant, close and reopen it, and you can see the Tabular Editor.
Implementation of Object Level Security
Let’s apply OLS to the Adventure Works Power BI dataset. We will apply OLS to the columns City and State Province from the customer table. Assume City and State Province are confidential columns that shouldn’t be exposed to everyone.
Administration role – Expose the columns.
Public role – Hide the columns.
Go to the Modelling tab in Power BI desktop.
Click on Manage Roles.
Now click New, create 2 security roles, and rename the untitled roles Admin and Public.
Save the modifications.
Now, Go to the External Tools tab in Power BI desktop and click on Tabular Editor.
The Tabular Editor opens in a separate window. The AdventureWorks Dataset opened in the Power BI desktop is now connected to the Tabular Editor tool.
Expand the roles folder under the model to see all the Security roles created in the Power BI desktop. You can also create and manage the Security roles in the tabular editor.
Let’s apply OLS to the desired columns by updating the column properties. Expand the tables folder, click on the Customer table, and expand it to see its contents.
You can also apply OLS to the entire customer table. Refer above Image.
Click on Column name City. Under Translations, Perspectives, Security dropdown, Expand Object Level Security Properties in all column properties for City column.
Under object-level security, we see the Security roles that we created earlier in this article. The City column should not be exposed to the Public, but we can enable it for Admin roles.
Change the default to
Nonefor the Public and Admin roles and leave it as default.
Repeat the same for Column Name State-Province.
After updating the column properties, expand the roles folder, and you can see the customer table added under the public role. Click on the customer table under public role for more information.
Save the changes in the Tabular Editor. The changes will be reflected on the Power BI desktop, and you can now close the Tabular Editor.
Once again, save your changes on the Power BI desktop. By saving your changes, you have completed implementing the Object-Level Security on the required columns from the Customer Table.
Testing Security Implementation in Power BI Desktop
Before publishing and testing the Semantic model in the Power BI service, let’s perform validation using the inbuilt option View as in the Power BI desktop.
Go to the Power BI desktop Modeling tab and click View as option.
Now, you can see all the available roles in a dialog box. Among those, select the public role and click OK to check whether the City and State-Province columns are hiding for this role.
Once you click OK, you can check that City and State-Province aren’t available for public roles under the Customer table in the Data panel.
Observe that the City and State-Province columns are available for the Admin role to use and build visualizations, as we didn’t apply OLS to the Admin role.
I created the table below using City and State-Province columns from the customer table.
- Now, let’s see these visual elements from the Admin Role and Public Role perspectives. The elements errored when selecting the public role, stating that the City and State-Province fields were unavailable.
Note:
You are viewed as a public role, and you applied OLS for a public role on City and State-province columns. That is why users with public role access won’t be able to access columns on which OLS is applied, and visual elements would be errored out if visuals were built using columns on which OLS is applied.
OLS has not been applied for the admin role. Users who will use Admin as a role will not have any restrictions on columns. Publish your Report to the workspace and check the Role perspectives in the Power BI service.
Testing OLS in Power BI Service
Navigate to your workspace and click on Security for the dataset that you just published.
Now click on the Test as role (In more options) option in either the public or Admin roles.
The report is rendered without errored visuals when impersonating an Admin.
Try impersonating as Public, and let’s check what would happen,
Observe the above image. Among 3 visual elements, visuals built using columns having OLS (City and State-Province) errored out. The pie chart representing the Sum of Extended Amounts by Category is still rendered because we didn’t use any columns on which OLS was applied.
Note:
You must add your relevant user IDs or user groups to each role. A dataset with security implementation insists developers add User IDs or Groups to unblock the viewer access to the report.
In this article, you have learned how to implement and enable OLS security by creating roles and validating the OLS in Power BI Desktop and Service.
Combination of Row Level Security and Object Level Security
It’s time to note that we have implemented Object-Level Security so far. To be precise, we also involved RLS and OLS.
Earlier in this article, we created Roles specially designed for RLS. Creating multiple roles and applying filter conditions differently for each role on specific security tables would result in Static Row-Level Security functionality.
There is a constraint: We can validate RLS in Power BI Desktop using the “view as” option, and we can validate OLS in Power BI Desktop using the “view as” option, but we can’t validate both at the same time using Power BI desktop mode.
To validate a dataset with the established RLS and OLS, we must jump into app.powerbi.com and validate using the Test as Role option in security settings.
To implement both RLS and OLS on the same dataset, setting them up under the same roles is important. For example, if you’ve updated column properties for the Admin and Public roles in OLS, you should apply RLS to these roles rather than creating separate roles specifically for RLS.
If you create two new roles solely for RLS, the OLS applied to the Admin and Public roles won’t affect these new roles, and similarly, the RLS on the new roles won’t be applied to the Admin and Public roles.
Limitations
As discussed, any security feature only applies to workspace viewers and users with build permissions through shared access via apps; security would be overridden to workspace Admins, Members, and Contributors.
Creating dynamic calculated tables/columns using DAX with tables or columns with OLS applied is not supported. If you attempt to perform calculations using restricted data, the calculation will fail and result in an error when accessed by users subject to those object-level restrictions.
Sometimes, especially in large datasets or complex models, performance impacts due to additional checks on object access when queries are executed.
As discussed in Combining RLS and OLS, these security features are role-specific, so security for one role does not automatically extend to other roles unless explicitly configured. This can cause issues if the roles for OLS and RLS are not aligned.
Conclusion
Object-Level Security (OLS) is a critical component of Power BI’s security framework, providing granular control over access to specific data objects. When combined with Row-Level Security (RLS), OLS offers a robust solution to safeguard sensitive information. However, it’s crucial to implement OLS carefully to avoid unintended access and ensure optimal performance.
While OLS is effective, restricted users may sometimes encounter errors when accessing visuals with masked columns. You can prevent these errors by implementing a workaround using dynamic field parameters and data masking, ensuring a smooth user experience without compromising data security.
If you have questions about Object-Level Security (OLS) or need assistance implementing it effectively in Power BI, our team is here to help.
FAQs
What is the difference between Object-Level Security (OLS) and Row-Level Security (RLS) in Power BI?
In the data model, OLS restricts access to entire objects, like specific tables or columns, hiding them completely from users without permission. Conversely, RLS restricts access to specific rows within a table based on user roles. Both can be used together to provide robust, layered security.
Can OLS be applied in Direct Query mode?
Yes, you can apply, but sometimes it can be challenging to implement in DirectQuery mode with some data sources, especially if the source doesn’t support fine-grained security. Testing is recommended to ensure OLS works as expected with DirectQuery.
Can OLS be used in Power BI Embedded reports?
Yes, OLS is supported in Power BI Embedded reports. However, permissions should be configured correctly for the intended audience, as embedded reports may have different security needs.
Can OLS be applied to calculated columns or calculated tables?
No, OLS cannot be applied to calculated columns or tables. Attempting to do so may result in errors if restricted users try to access visuals based on these calculations. Instead, data masking or other techniques hide sensitive calculated data.
Is Tabular Editor free to use?
A free version of Tabular Editor (Tabular Editor 2) provides many essential features for managing Power BI data models, including setting up OLS. However, a more advanced, paid version (Tabular Editor 3) is also available, offering additional functionality, a refined user interface, and extended support for complex modeling needs.
