With the general availability preview of Power BI’s Object-Level Security (OLS) and when combined with Row-Level Security (RLS), you can secure and hide specific metrics, measures, tables, or columns from report viewers when using Roles. This walkthrough and list of resources will provide an introduction and details on implementing these new features in Power BI Premium, Premium per User, and Pro.
What is Object-Level Security?
You can not only restrict access to data but sensitive object names also. Therefore, model authors can secure and hide metadata from discovery. This feature hides the availability of sensitive data from the user, which is secured from the caching layer.
Reference: Analysis Services tabular model object-level security | Microsoft Docs – Provides documentation on the service, including restrictions.
What does Object-Level Security look like?
Object-level Security hides tables, columns, and measures but also secures them. Your secured tables and columns are obscured in the field list when using reporting tools like Excel or Power BI. So, if you do not have permission, you cannot access secured metadata objects via DAX or any other method. The most important part of OLS is that to viewers that don’t have permission to access the object, the secured tables or columns do not exist.
The example pictured below shows when a user sees a report that contains a blocked object, like Budget Sales, they will see an error. Selecting Details, as here, shows the field that is hidden. Remember, they were able to get to a report that contains something they are blocked from. Of course, they should not have access to the report first, but this security gives more ability to secure and manage your information.
How do Set-Up Object-Level Security?
The original Public Preview announcement has a step-by-step on how to set this up. Let’s walk through some of the steps here.
Note: There is an interesting point that the user or group has to be configured with members having Read-Only or Viewer access. Then the role will be applied to those members. If they are marked to have edit or contributor rights, the roles will not be applied. I- Working on validating this.
Step 1 – Create Roles in Power BI Desktop
You need to Create Roles in Power BI Desktop, the same way you would do this for Row-Level security. Under Modeling (1), select Manage Roles (2), then Create (3) your roles. Finally, click Save; this will set up your roles on the model and prepare you for the next step.
Remember, we only create the roles at this point and will use the Tabular Editor to continue the setup, so there is no need for the Dax Expression (5).
Step 2 – Using Tabular Editor to create Security.
Tabular Editor can be reached on the External Tools ribbon. If you don’t see the Tabular Editor button, install the program from the website, Tabular Editor 3 Tabular Editor will connect to your model here in Power BI Desktop as soon as it is opened from the toolbar. (Note: Tabular editor also has a best practice editor.)
Showing the screen print below, when opening up the Tabular editor (2) from the External Tools (1) menu, which shows the External Tools you have installed, the Power BI workbook data model connection (3) will be made automatically.
With the data model open, selecting the Roles (1) will show the roles you created in the previous step. Next, open the Table Permissions (2), and you can change what Object Level Security is available for the Role (3). Here we chose to make the access Read for HR and None for Reader.
Once you have finished setting it up, Click Save (1) to write your modifications to the model. However, there is a validity check on saving.
Step 2a – Problems with Relationship Chains
In the documentation, Analysis Services tabular model object-level security, table-level security cannot be set for a model if it breaks a relationship chain. An error is generated at design time. For example, suppose there are relationships between the Employee Table (1) and the Fact Table (2) and another relationship between Facility Table (3) and the Employee Table (1). In that case, you cannot secure the Employee table (1). If Employee table (1) is secured, a query on Facility table (3) cannot transit the relationships between tables 1 and 2, nor 2 and 3. In this case, a separate relationship between Facility Table (3) and Fact Table (2) could be configured.
Note that the error below when I tried to save the changes. We did not give the reader access to the Employee table, but a relationship that passed through the table, so the OLS would not allow that. So, in this “Play” example, we could make only Budget sales access NONE and the changes save.
We could Hide the BudgetSales and the Facility table from the HR Users. These are the two tables on the left of the Employee and FactPay relationship.
Step 3 – Publish to Power BI
Once published to Power BI, go to the Security page (3), located on the More Options (2) menu on the dataset. Pictured below is the path to security. Note the Email Address (5) of the user added to the HR Role (4). Once you go back to the report that contains BudgetSales or even just look at the data model for the report, you will no longer see the table and get an error for any reports that contain that information.
Note that the user I am using to test does not have admin or contributor to this workspace.
Step 4 – Testing the Final Result.
I created the test user pictured above and provided them with Viewer access. When I go to a report that has Object-Level Security enabled on the table, the BudgetSales, in this case, you will see an error (1) saying that “Fields need to be fixed” (2).
Visuals containing no blocked information come up fine for this user.
Important to keep in mind the Row-Level security rules. The following is from the Power BI Documentation.
“Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. In the Power BI service, members of a workspace have access to datasets in the workspace. RLS doesn’t restrict this data access. (Contributor Access, for example)Citation: Row-level security (RLS) with Power BI – Power BI | Microsoft Docs
If you publish your Power BI Desktop report to a new workspace experience in the Power BI service, the RLS roles are applied to members who are assigned to the Viewer role in the workspace. Even if Viewers are given Build permissions to the dataset, RLS still applies. For example, if Viewers with Build permissions use Analyze in Excel, their view of the data will be protected by RLS. However, workspace members assigned Admin, Member, or Contributor have edit permission for the dataset and, therefore, RLS doesn’t apply to them. If you want RLS to apply to people in a workspace, you can only assign them the Viewer role. Read more about roles in the new workspaces.”
Now that Power BI Object-level Security and Row-Level Security (RLS) have been released, both enable enhanced enterprise-grade security on your reports and datasets. This ensures that only users with the requisite permission have access to view and interact with sensitive data.
With Object-level security, a user who should not have access to a table, but gets to a report containing some restricted data, will not see the visualization and will get a visual error. This includes table-level security and column-level security in the Roles object. When configured, table and column data are secured, along with metadata, to hide their existence from users.
Resources, Tutorials and Next Steps
- Analysis Services tabular model object-level security | Microsoft Docs – Microsoft Docs overview of the feature and reviews combination with row-level security.
- Announcing public preview of Object-Level Security in Power BI | Microsoft Power BI Blog | Microsoft Power BI – Public Preview announcement with a walkthrough.
- Object-Level Security (OLS) is now generally available in Power BI Premium and Pro! | Microsoft Power BI Blog | Microsoft Power BI – Object Level Security GA Announcement.
- Video: What is Row-Level Security (RLS) in Power BI??? – YouTube – Video from Guy in a Cube – Row-Level Security
- Video – Power BI Row-Level Security And Where To Filter – YouTube – Video from Guy in a Cube, Patrick looks at Power BI Row-Level Security And where to filter. He found that, after applying dynamic security, customers showed up in a slicer when they should not have. Challenge accepted! He walks through Power BI Desktop to show how he fixed his Row-Level Security problem.
- Announcing public preview of external tools in Power BI Desktop | Microsoft Power BI Blog | Microsoft Power BI – Integration with external tools such as Tabular Editor, Dax Studio, Power BI Report builder, and the ALM Toolkit – SQLBI which is a schema compare tool for Power BI datasets used for application lifecycle management (ALM) scenarios