RLS overwritten when republishing a Power BI report

[Anonymous]

At the moment, it is apparent that role level security is defined at the dataset level on the powerbi service. So each time the report is republished from the PBI desktop, existing RLS setup will be overwrittern and will have to be re-created, which is not a possible solution if you have a dozen roles with 10-20 users in each role. I am using direct connect or import from a SQL server database source directly (so no SSAS involved). Just wondering how you guys get around this and make the process less painful?

Thanks.

[ankitpatira · Accepted solution]

 @Anonymous Feature is still in BETA so unfortunately it will be overwritten. If you can't manage to recreate roles then you should consider using UPN in RLS in power bi. I'll try give you an example.

Say you have a employee data and employee belongs to department. In your data source create UserSecurity table with users email address and department (departmentID, basically identifier for department). Then join that departmentID with tables in your actual data tables. Create a single role in RLS and add all your power bi users in that role. Under RULES tab, select UserSecurity table and apply rule as [EMAIL] = Username(). Username() returns logged in power bi user email address. From UserSecurity table, departmentID for that returned username will be matched with departmentID in all your actual tables and dashboard will be filtered.

Hope that make sense!

[ankitpatira · Accepted solution]

 @Anonymous Feature is still in BETA so unfortunately it will be overwritten. If you can't manage to recreate roles then you should consider using UPN in RLS in power bi. I'll try give you an example.

Say you have a employee data and employee belongs to department. In your data source create UserSecurity table with users email address and department (departmentID, basically identifier for department). Then join that departmentID with tables in your actual data tables. Create a single role in RLS and add all your power bi users in that role. Under RULES tab, select UserSecurity table and apply rule as [EMAIL] = Username(). Username() returns logged in power bi user email address. From UserSecurity table, departmentID for that returned username will be matched with departmentID in all your actual tables and dashboard will be filtered.

Hope that make sense!

[Anonymous]

@ankitpatira it makes perfect sense. But 2 questions remain.

1. how do you easily add all powerbi users (say from 30 - 200 users) in the role? Is there any built-in group on Power BI similar to everyone group or All users  AD group I can use? Or I have use office 365 somehow?

2. Are you implying we will still have to re-create the RLS but in this case we only need to configure one role rather than multiple roles.

Thanks.

[v-yuezhe-msft · Accepted solution]

Hi pbuser,

Firstly, as per this blog, you can assign AAD groups (security groups and distribution lists) to a role, which makes it easier to assign roles to a large group of users at once.

Secondly, you will still need to re-create Row Level Security (RLS) after republishing report to Power BI service even if you use USERNAME () function with RLS.

Thirdly, the RLS feature at this stage is still in preview mode, regarding to the limitation that you will loose all row-level security after republishing report, you can submit a idea ( for example, make it possible to back up roles and restore roles after republishing report) on the Power BI Idea forum: https://ideas.powerbi.com/forums/265200-power-bi-ideas  .

Thanks,
Lydia Zhang

[Anonymous]

@v-yuezhe-msft Thanks for the infor about the AAD group assignment to a role. Just a bit off topic, can we assign AAD group to a Power BI group? Or can we reuse existing AAD groups without creating them in Power BI?