Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

GilbertQ

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

 

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

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

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?

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.

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

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

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

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?

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

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.

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

 

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  

Hi Richard,

 

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

 

Thanks

Dave

Hi @GilbertQ

 

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

 

Thanks

Dave

Thanks @davekerby that is very helpfull actualy.

Hi @davekerby

 

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

 

Thanks