Managing a large number of reports with RLS

[Anonymous]

I have a report and would like to limit my users data access using RLS so they can only see the data that pertaints to them. I have about 75 differenct users that will all have their own specific data sets.

I am relatively new to PBI and RLS is a new concept for me.  I've read some tutorials and it seems fairly straight forward. I create roles in PBI desktop, upload to a workspace, and then give access by email address. I have a few questions:

I only have one pro account. Do all users have to have one?

Is there a way assign RLS based on a table so it is easier to manage?

Any other suggestions or references are greatly appreciated. Thanks!

[Anonymous]

Thanks @selimovd, I wish I could use the groups but I have 75 sales poeple and we don't want of them to see each other's performance. 

Is it theoretically possible to create one group per person? It would be a lot to implement but it is the only solution I could think of other than creating 75 individule reports. Are there any other ways to approach this problem? 

[selimovd]

Hey @Anonymous ,

yes, you can add a dynamic row level security, where every user just can see his own data.

For that you need a table with the user information, like an employee table with the email address of each employee.

This table you can filter on the USERPRINCIPALNAME(), what is the function to get the email address of the currently logged in user. Like this the user can only see his own data.

Check the following article on how to do that:

Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship - RADACAD

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Sumanth_23 · Accepted solution]

Hi @Anonymous -

• You can create a table in your data source where you can maintain the mapping of the required security as seen below in screenshot

• Create a single security role in Power BI report using [UserID] = USERPRINCIPALNAME()

• Ensure you incorporate the Security table in your data model so it filter the main DIM table based on the security defined in the Security table 

• Assign access to members to this single role in the service - this will ensure that folks see data based on the access setup in the Security table

Hope this helps! 

[Anonymous]

Thanks @Sumanth_23 this looks like an ideal solution. Would this work if I had only one premium account or would all my users have to have a pro account?

[Sumanth_23]

hi @Anonymous - your users would also be required to have a Premium per User (PPU) license to access the reports that you publish - else if you have a Pro license then other users would also require a Pro license to view the reports

Below is the Microsoft documentation on the features for each of the license types: 

https://docs.microsoft.com/en-us/power-bi/fundamentals/service-features-license-type

License type	Capabilities when workspace is in shared capacity	Additional capabilities when workspace is in Premium capacity
Power BI (free)	Access to content in My Workspace	Consume content shared with them
Power BI Pro	Publish content to other workspaces, share dashboards, subscribe to dashboards and reports, share with users who have a Pro license	Distribute content to users who have free licenses
Power BI Premium Per User	Publish content to other workspaces, share dashboards, subscribe to dashboards and reports, share with users who have a Premium Per User license	Distribute content to users who have free and Pro licenses

Happy to help! 

[selimovd]

Hello @Anonymous ,

usually you would not give access by individual email addresses. You would create active directory groups and assign the users to the group. So you could create a group "UK", one for "US", one for "France" and one for "Germany" and assign the user by what they should see. Then you would create a role for people who should be allowed to see only "UK", one role for people who should see only "US" etc.

Then for each dataset you have to assign the corresponding ad group to each role. When you have then a new user responsible for "UK" you just add him to the ad-group and he automatically has access to the correct data.

If the people work together in a workspace they all need a pro account.

If you have premium capacity you can let the workspace run in premium capacity, people who only consume reports don't need an extra license, everyone creating a report still needs a pro license.

Also take a look at the REST API to script a few of the tasks, also for ad groups.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic