Dynamic Row Level Security (Dynamic RLS)

[nandukrishnavs]

Requirement : 

We have a table with the User, Customer and Sales value.

Sales table

We need to show the total sales value for each customer. When user login to the report he wanted to see only his customer data.

Sales by Customer

Solution

We need to implement RLS.

Click on Modeling Tab and navigate to Manage Roles icon

Modeling Tab

Then define a role name and right-click on the required table. We are going to use USERPRINCIPALNAME() in the filter condition.

This function returns the name of the user as their email address. 

[User] = USERPRINCIPALNAME()

Manage Roles window

Now we have completed the configuration in the desktop tool. We can test the roles in the desktop itself. 

Under the Modeling tab, we have an icon called View as Roles. Click on that icon, we will get a new window.

We have to select both Other user and newly created Role. Then enter the email id of one user in the text box near to the Other user text. 

View as roles

Click OK and see the changes immediately.

Report viewData view 

Next step is to configure the RLS in app.powerbi.com

Go to the dataset tab and follow the below steps.

Click on 3 dots near to the dataset.

Click on security and you will be navigated to another page. 

Here you can add all the users to the Member section. You can use either individual email id or group id. 

Note: make sure your user does not have edit permission to this report. Otherwise, RLS will not work.

Dynamic RLS.pbix

[jiler]

This is great! Thank you for the information. 

I am having a problem and hoping that you can help. I have set up Dynamic RLS and have tested it in Desktop and it works great. I get to Service, add people to the security group and push the report out to the app. I have 4 users, 1 can see the report and just she her data and the other 3 users get an error saying "You do not have permission to the underlying dataset". I tested their email address in desktop and the report populates perfectly, but not in Service. 

Thanks

[nandukrishnavs]

Hi Jiler,

Did you add users to the role's member section? We have to add all the users to the user role.

Regards,

Nandu Krishna

[Wayfarer]

In case it's not so obvious - you can test the RLS in PBI Service as well.  Click the 3 dots on the role name then Test as role.

[namitace]

is there any way to aviod adding users to the role? I mean i have pulled user security from sql and then use the same list to view based on sql rather than additionally adding them into the PBI services.Plus its difficult & redundant when i need to do the same for all reports.

[nandukrishnavs]

@namitace , If you are using Power BI embedded, then I think we don't need to add users to the role. We just need to handle it at the code level.

[namitace]

 @nandukrishnavs 

- thanks for your response. But i am not a very technical and do not want to go into such deep code logic adding to my report as its already very slow performance wise. 

namita

[nandukrishnavs]

@namitace , We have to add all the required users to the Role. Then only they can view the restricted data. Otherwise, they will get an error message. Could you please describe your scenario in detail.

[abrahampraisely]

@nandukrishnavs : this is great stuff. good to get an understanding of RLS.

[nandukrishnavs]

Thanks @abrahampraisely 

[rperes]

I would like to know if there is any way to add members (people or group) to a specific role using some kind of power shell script or API or even programatically. The users with access will be sitting in a table populated by an external system. So bascically every time an user is added to the table, I will trigger a process to add the user as a member.

[nandukrishnavs]

@rperes I saw a similar request in the Power BI idea forum. Please vote for that. https://ideas.powerbi.com/ideas/idea/?ideaid=eb2b0e18-f2b5-4b10-a9e0-72165f91af0e