Security: RLS & filters

[Yousra]

Hello,

We would like to share our report with 3000 different users. Each user has to see his related data only.

If we use RLS to achieve this, the role is based on the user's id so we need to create 3 000 roles in Power BI Desktop and assign 3000 roles to 3000 users in Power BI Service which is hard to do manually. 

Question 1: Is there a way we can create the roles and maintain them in a dynamic way (by using a script for example)?

Question 2: Is there a different way we can achieve this ? Can we use filters only and share the reports in a way that each user sees the report with a predefined filter that he can't change ?

Question 3: Can we use Power BI Report Server to achieve the same goal? If yes, how ? 

Thanks

[henriquesilveir · Accepted solution]

@Yousra hello, the better choice i hope that is integrating your AD with PBI, using RLS.

about your's question:

Question 1: Is there a way we can create the roles and maintain them in a dynamic way (by using a script for example)?

https://www.fourmoo.com/2018/02/20/dynamic-row-level-security-is-easy-with-active-directory-security...

This article you will learn how to integrate an AD to make a dynamic RLS using security groups.

Question 2: Is there a different way we can achieve this ? Can we use filters only and share the reports in a way that each user sees the report with a predefined filter that he can't change ?

You can filter the report using your URL, take a look these articles:

Filter a report using query string parameters in the URL

https://docs.microsoft.com/pt-br/power-bi/service-url-filters

https://powerbi.microsoft.com/en-us/blog/power-bi-report-url-filter-improvements/

Question 3: Can we use Power BI Report Server to achieve the same goal? If yes, how ? 

when you said Report Server, you till looking to use SSAS, correctly? because if you want to do in report server, Microsoft announced at this years Ignite that Row Level Security will come in the November/December Release of Power BI Report Server.

If you wan to use SSAS:  http://bifuture.blogspot.com/2011/09/ssas-setup-dynamic-security-in-analysis.html

let me know if you need something more...

Don't forget to mark this post as solution if you got it,

Best Regards, 

[nirvana_moksh]

You dont have to create 3000 roles on the Desktop, you can use Username() function against the column containing the user's email id's while defining the role.

On the Service as well you can just add the applicable Group which contains all the users. For instance Finance, HR, Legal etc, that will greatly limit the number of people you have to associate the RLS with individually.

[gsharrow]

Also you will need something in your data mapping the user to what "items" they have access to,  Typically you create a security data table and create a relationship between that and the data you want to secure.

Look ar something like this http://angryanalyticsblog.azurewebsites.net/index.php/2016/12/27/dynamic-rls-via-hierarchy-in-power-...

or do a search on dynamic row level security for power bo or tabular analysis services