Power BI – Dynamic Row Level Security – Tips to get it working!

by Super User on ‎10-10-2016 10:02 AM

 

Below I am going to explain some of the caveats that you need to be aware of when implementing Dynamic Row Level Security (RLS) in Power BI. Without this I could not get Dynamic RLS security working for me and my data.

 

I found that there are a few things that are currently not mentioned anywhere and it took me some time to gain an understanding. So I am hoping that with this blog post it will make it easier for you to implement Row Level Security using Dynamic Security.

 

Below is the link to a blog post by Kasper De Jonge in which he explains how to very quickly get up and running with RLS, as well as providing a sample Power BI Desktop Model.

 

Power BI Desktop Dynamic security cheat sheet

 

And this is what the Relationship Model looks like, which will make the explanation below a little easier to understand.

 

PBI - Dynamic Row Level Security - 1.RelationshipModel.png

 

Things you need to Know!

Below are the things that I learnt and that will help with understanding the bits that make it all work together.

Testing RLS in Power BI Desktop

The first thing that you will need to update if you have downloaded Kasper De Jonge’s Power BI Desktop model is for in the relationships area. This is because it is missing a key tick box, which without it the Dynamic RLS will not work.

  • Go into the PBI - Dynamic Row Level Security - 2.Relationships Icon.png(Relationships) area in the Power BI Desktop model.
  • Then edit the relationship between the UserGroup and Group Table, when opening you will see the following below.
  • PBI - Dynamic Row Level Security - 3.Edit Relationship.png
  • Now in order for this to work, you will need to apply the tick next to “Apply security filter in both directions”
    • NOTE: If this is not enabled or ticked the Dynamic RLS will not work correctly.
  • So once done it will now look like the following below:
  • PBI - Dynamic Row Level Security - 4.Relationship Filter.png

 

The next thing to note is if you want to test RLS in Power BI Desktop you have to ensure that you have included yourself in both the Users and UserGroup Table.

 

If not, you will get the following screen when click on the PBI - Dynamic Row Level Security - 5.View Roles As.png button.

 

PBI - Dynamic Row Level Security - 6.Blank Sales.png

 

As you can see above the Bar Visual is Blank and the Sales Amount is Blank. This is because with my current login context I am not specified in any of the Dynamic RLS tables.

 

Which lead me onto the next piece in understanding how the Power BI Service works.

 

Testing RLS in the Power BI Service

What happened was when I was initially testing this, I put in myself as a user and then a fellow worker as a user in both the Users and UsersGroup table.

 

I then uploaded the Power BI Desktop file to the Service. Once it was uploaded I went into the Security for my dataset and put in name under the Roles.

 

PBI - Dynamic Row Level Security - 7.RLS Members.png

 

Now what I expected to happen is that when I went into the report I should only see the data for Group B and Group C, the reason is because in the UserGroup table I had rows for Group B and Group C

 

PBI - Dynamic Row Level Security - 8.RLS Users.png

 

But when I went and viewed the Report I saw the following below. As you can see I can see all the data and NOT Group B and Group C.

 

PBI - Dynamic Row Level Security - 9.See All Data.png

 

This took me quite a to understand and I did try a whole host of things to get it working.

 

This is unconfirmed by Microsoft but my own conclusion was that because I am the person who is uploading the Power BI Desktop model into the Power BI Service, I must by default have Admin (Server Administrator) rights to the model.

 

So no matter what I do, I will always see everything. Which makes perfect sense because I am the author of the model.

 

So to test this I then shared my Dashboard with another user who only had access to Group C, and when he viewed the dashboard as well as the reports he saw the following below. (NOTE: I did add his email address under Security in the Dataset)

 

PBI - Dynamic Row Level Security - 10.Filtered Security.png

 

Conclusion

Whilst it is great to now have Dynamic RLS in the Power BI Service I did struggle for some time to get it working, as well as to understand how it all pieces and works together.

And since I now know the above information I have been able to successfully roll out and test other Power BI Models successfully.

 

Comments
by Power BI Team sirui_sun
on ‎10-17-2016 02:58 PM

Hello - 

 

Great writeup! I can confirm that if you have edit permissions to the underlying dataset (i.e. you either uploaded it yourself or are an edit-enabled member of the workspace), then RLS will not take effect for you, and you will be able to see all the data.

 

Thanks,

-Sirui

by davekerby Regular Visitor
‎06-22-2017 01:45 AM - edited ‎06-22-2017 01:49 AM

Hi,

 

Can you advised if the user (member) still has to have read only permission on the workspace?

 

Just read the above comment. How do we get around this? i want to apply RLS to the model, however i also want the user to have access to the dataset? is there a way to applly dynamic security and have the user to have access to the data set?

by Super User
on ‎06-22-2017 02:29 PM

Hi @davekerby

 

If a user is part of the Workspace they are seen as having full access to the underlying dataset.


So unfortunately currently by design the user would need to be removed from the Workspace in order to see the RLS.

 

I am sure I might have read somewhere that they are working on changing it if the user is a Member of a Workspace to still be able to apply RLS, but that is not currently implemented.

by msudulag Regular Visitor
on ‎08-04-2017 05:53 AM

Hi @GilbertQ,

 

I have a User ,role and audit_fact tables are there,now 

 

if role is admin per one he can see all data else other person needs to see his related data only.

 

How can i achieve this,i am trying a lot not getting even how to implement.

 

can you please help me on this.

 

Regards

Msudulag

by Super User
on ‎08-05-2017 11:29 PM

Hi @msudulag

 

I would suggest that you work through the linked example in my blog post.


Download the items and get that working first. that will give you a good understanding on how it works and you will learn a lot from that. Then once the above is done you can then try on your own data. That is how I did it at first.

 

Regards

Gilbert

by chawalit Frequent Visitor
on ‎09-07-2017 03:33 AM

I have to download the smaple. And after test the role i found the Total does not equal with the data in table as in pithe picture as below.

 

 RLS.jpg

how to solve this problem?

 

Thanks

Chawalit

by Super User
on ‎09-07-2017 02:12 PM

Hi there, did you check to ensure that the user is in the correct table, so that when you apply the role it is being applied corretly?

by richardbadge Frequent Visitor
on ‎02-05-2018 08:38 AM

Hi, thanks for the blog above, I have now got my dynamic role level security working ( almost ). I just have a few questions on how to setup access in powerbi.com to get it working with apps, if that is OK?

 

I am fairly new to PowerBI so have been using the desktop version and got the dynamic security working as I want when I use the testing of the role in "View as Role". As I have a number of people who want to use report, I was advised to use a published apps as I can add users into an AD group to provide access without having to add users individually, so have not added the users to the app workspace, but only added them to the app list when I publish. However, when I do publish the people can't see any data. The only way I can seem to get it to work is if I add them as a member to the dataset security tab in the app workspace, This seems to add an overhead on managing access that I was hoping to avoid. Is this really necessary or am I missing something?

 

Thanks in advance

by Super User
on ‎02-05-2018 02:01 PM

Hi @richardbadge

 

It would appear that the people you are adding to the App have not been granted access within your Power BI Desktop model?

 

As well as you also will need to add these people under the specified role in the Power BI Service.

You can do that by going to your dataset in your App Workspace.

Then click on Datasets.

Then click on the breadcrumb (Ellipses) and select Security.

Then find the associated Role and add the users into the required role.

 

Once the above is done, get the users to test again.

 

What I have done in the past, is to ideally have someone sitting close to you, or next to you and add them into a specific role via all the steps above. So it is easy and quick to test to make sure you got it all working.

by richardbadge Frequent Visitor
on ‎02-05-2018 11:00 PM

Hi @guavaq,

 

Thanks for getting back to me on this.

 

It sounds like this is an admin step that I am unable to avoid.

 

When I first delevoped the model without any row-level security, all I had to do was add people into the access tab when I publish the app and did not need to add to the app workspace as a member or add them to the dataset, so this was a simple process as can apparently use AD groups when publishing the app, which will save lots of user administration. However by using the row-level security it looks like I will now have to add everyone as a member of the role in the dataset security section as well as to the app, for it to work.

 

I was hoping that the dynamic row-level security would avoid the need for this?

 

Richard

 

by Super User
on ‎02-05-2018 11:43 PM

Hi @richardbadge

 

There actually is another option using AD Groups 

 

If you can private message me I can let you know how to do it via email. Which I think will be easier  

by davekerby Regular Visitor
on ‎02-06-2018 12:04 AM

Hi Richard,

 

Have a look at this, this might also help you http://prologika.com/power-bi-group-security/ 

 

Thanks

Dave

by davekerby Regular Visitor
on ‎02-06-2018 12:07 AM

Hi @GilbertQ

 

I'd be interested in knowing an option for AD groups, can you share this with me? 

 

Thanks

Dave

by richardbadge Frequent Visitor
on ‎02-06-2018 12:23 AM

Thanks @davekerby that is very helpfull actualy.

by Super User
on ‎02-08-2018 05:53 PM

Hi @davekerby

 

If you can private message me, I can send you some details

 

Thanks

 

by ranbeermakin Regular Visitor
on ‎05-31-2018 05:46 AM

Hi there, was there any resolution to Richard's question?

 

If there is dynamic security why do I still have to add users/groups in Roles in Power BI Service?

 

Any alternative?

 

Hoping for a response

 

Thanks,

Ranbeer

by Super User
on ‎05-31-2018 03:24 PM

Hi @ranbeermakin

 

When uploading to the Power BI Service, you still have to map the PBIX file to the Power BI Service.

 

By using an Active Directory Security Group this will mean you do not have to add users manually and it will be managed as people are added and removed from AD Security Groups.

 

The reason it is dynamic is that you only need to create 1 role. And each user can have their own security settings dynamically created on login.

by jatneerjat Regular Visitor
on ‎06-07-2018 06:50 AM

Hi @GilbertQ,

 

I am working on a report which have data for different users and now i want that whenever a user login into power bi then the user should only see the data that is related to him.

I have a column [hiringmanageremail] in table [userinfo] which have the emailid of the users so I created a role in power bi desktop as

 

1.PNG

 

so when i login to the power bi it shows data related to me but when anybody else(whose id is present in table [userinfo] ) login to power bi and see the reports then it shows as below:

2.PNG

 

then i added this user as a member of role in power bi service 

 

 

3.PNG

then click on apply

 

4.PNG

 

then it shows all the reports to this user

 

5.PNG

 

Why it is not taking the email dynamically and i have to enter them in roles manually.

what i have to do so that power bi dynamically takes emailids for the login user and show the data related to it.

 

Thanks,

Jat

 

 

by Super User
on ‎06-07-2018 08:26 PM

Hi @jatneerjat

 

In order to make it Dynamic you have to use Dynamic Row Level Security, in which you create a single role and all the users are passed via the single role.

 

If all the users are in an Active Directory Security Group, they can then be added via the Security group.


Below is a great blog post by Kasper from Microsoft, in which he explains how to use Row Level Security with a working example. I would suggest that you work through this and get it working first. And then apply those learnings to your model.

 

http://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/