Home > Blog > Using Power BI Object Level Security

Using Power BI Object Level Security

The following article will demonstrate how to use Power BI Object Level Security to disable column data based on a user’s role. 

Power BI Object Level Security: Test Example

You can get started by inputting the following data in SQL Server…

• use master
  go
• create database ObjectLevelSecurityDemo
  go
• use ObjectLevelSecurityDemo
  go
• Create table DimCustomer (
  Id int not null primary key
  ,Name nvarchar(20) not null
  ,Country char(3) not null
  ,State nchar(2) not null
  ,Age int not null
  ,GovtId nvarchar(20) not null
  )

• insert into DimCustomer values (1,'Marky','USA','MA','59','123-45-6789')

  insert into DimCustomer values (2,'Ricky','USA','MA','44','234-56-7890')

  insert into DimCustomer values (3,'Danny','USA','MA','21','345-67-8901')

  insert into DimCustomer values (4,'Terry','USA','MA','16','456-78-9012')

  insert into DimCustomer values (5,'Mikey','USA','MA','32','567-89-0123')

  insert into DimCustomer values (6,'Davey','USA','MA','66','678-90-1234')

  insert into DimCustomer values (7,'Timmy','USA','MA','33','789-01-2345')

  insert into DimCustomer values (8,'Tommy','USA','MA','69','890-12-3456')

  insert into DimCustomer values (9,'Joey','USA','MA','24','901-23-4567')

  insert into DimCustomer values (10,'Robby','USA','MA','88','012-34-5678')

  insert into DimCustomer values (11,'Johnny','USA','IA','38','Unknown')

  insert into DimCustomer values (12,'Brian','USA','IA','83','Unknown')

  insert into DimCustomer values (13,'Meg','CAN','ON','74','123-456-789')

  insert into DimCustomer values (14,'Jo','CAN','ON','54','234-567-890')

  insert into DimCustomer values (15,'Beth','CAN','BC','42','345-678-901')

  insert into DimCustomer values (16,'Amy','CAN','NS','91','456-789-012')
  go

• Select * from DimCustomer

Then add a trivial Power BI report that looks like this:

Next up, add the following Roles in the Power BI Desktop Editor.

• For the CAN and CANAdmin roles, add this Table filter DAX expression
  [Country] = "CAN"
• For the USA and USAAdmin roles, add this Table filter DAX expression
  [Country] = "USA"
• For the Admin roles, you can leave the Table filter DAX expression blank and save.

Enabling Object Level Security is not implemented in the Power BI Desktop editor. In order to do that, you will need to use the Tabular Editor External Tool to enable Object Level Security. Add it to the External Tools section of your Power BI Desktop.

When the Tabular Editor opens, you can complete the security by modifying the CAN and USA roles as follows:

Then, you can save your changes in the Tabular Editor to test them in the Power BI Desktop.

You will notice the visual with the secured data - GovtID - appears to be broken. You should also note there will be no indication to the report consumer that the GovtId field even exists in the model fields.

If you publish this model to PowerBI.com, you can then apply the report role permissions to the data set.

Power BI Object Level Security: Additional Notes

Microsoft outlined the following caveat for this demo:

“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)

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.”

You can access that full blog post here.

Questions?

Please reach out to us if you have any questions about this or any other Stoneridge software solutions.

Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.