Forum Discussion
Managing a large number of reports with RLS
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!
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!
6 Replies
- selimovd
Most Valuable Professional
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic 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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic 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!
