Complex Dynamic Row Level Security

[Anonymous]

Hello PowerBI Community,

I have a requirement to configure Dynamic Row Level Security.

Below is the scenario:

We have 2 dimension tables for RLS and multiple Facts.

The Fact table contains columns for Market and Function.

Dim_Markets :

1. NAM
2. LAM
3. Asia

Dim_Functions:

1. Operations
2. Finance
3. Sales
-----------------------------------------------
I need to configure RLS at 3 Levels:

1. Global User
-- Should have access to all the markets for all the functions.

2. Market User
-- Should have access to a specific market for all the functions.

3. Head Of Market & Function. (i am finding this tricky)
-- Should have access to all the markets for one specific function (known beforehand).
-- Also, He/She will have access to all other functions for one market (known beforehand).
-- e.g: If I am the Head of Market for 'LAM' and 'Operations',
   then i should be able to access
   1. 'Operations' data for all the markets
   2. 'LAM' data for functions other than 'Operations'

I would really appreciate if someone can assist me here.

Thanks.

[dapster105]

The implementation of RLS for ISV "app owns data" scenarios is truly awful. Using username as a dynamic can-be-anything string is nothing more than a hacky workaround for the fact that Embed has always been an afterthought.

For complex RLS scenarios the additional model bloating and maintenance required to translate complex permissions logic into table relationships is grotesque when the simple and obvious solution would be to allow the ISV to maintain the RLS logic outside the PBI and then pass an explicit set of filters in when getting the embed token. User filters would then apply 'within' the security filters set by the ISV application. Simple.

[Anonymous]

Hi @amitchandak ,

Thanks for the quick response.

But i also need the RLS at Function level. I am finding the third scenario that i have mentioned a bit difficult.

3. Head Of Market & Function. (i am finding this tricky)
-- Should have access to all the markets for one specific function (known beforehand).
-- Also, He/She will have access to all other functions for one market (known beforehand).
-- e.g: If I am the Head of Market for 'LAM' and 'Operations',
   then i should be able to access
   1. 'Operations' data for all the markets
   2. 'LAM' data for functions other than 'Operations'

Thanks.

[amitchandak]

@Anonymous , You need to have table like

My advice would be to have table like that have Many to many bi-directional join with Region

Region	Email
NAM	a@abc.com
LAM	a@abc.com
Asia	a@abc.com
NAM	b@abc.com
LAM	c@abc.com
Asia	d@abc.com

a is global and other are regional user

How to use Row Level Security (RLS): https://youtu.be/NfdIA0uS6Nk