Dynamic RLS Solution Options

[sendilc]

Hi, 

I have few fact table which have information specific to region (america, europe, apac, middle east etc...) and for each of the region i have product (pr1, pr2, pr3....) and for each product i have prodcut category (cat1, cat2, cat3....). 

I would want to implement dynamic RLS in such a manner that any user who logs in will have access to a region and then by product or product category. I have 4 region,  10 product and 10 product category. I will create a Permission table having user mail, region, product and product category access. Then I create a relationship with fact tables to determine what data the user can have access to.

I have 2 queries here : 

• Is this the best way to implement this; where for each user I define access levels at region, product and product category level. But then if i have 1000+ users then I will need to create 1000+ entries. 
• Can I create a user access table which has all possible combinations of region * product * product category and then map the role to user mail id. If I go by this route then I will have to create 4*10*10 user access roles which will increase as region or product or product category increase

Pls suggest what is optimal way to implement Dynamic RLS that will cater the above needs. Thanks for  your help. 

Regards

[sendilc]

Hi, 

I was able to successfully implement RLS by creating role that had restrictions defined for Dimesion tables (Product, Product Category and Region). 

I have a user table that has the following info. However, the issue I am facing is when I associate the same User (xyz@abc.com) to multiple Products, I get multiple rows as such my DAX function returns an error. Can you pls let know if there is an alternative to handle this situation. The DAX function is 

VAR _RLSProduct =
CALCULATETABLE(
VALUES('User'[RLS Product]),
'User'[email]=USERPRINCIPALNAME()
)

RETURN
SWITCH (TRUE(),
[Product] = _RLSProduct, TRUE(),
FALSE()
)

EmailId	Area	Product	Product Category
abc.xys@abc.com	US	ALL	CAT1
xyz@abc.com	ALL	PRD1	ALL
xyz@abc.com	ALL	PRD2	ALL

[sendilc · Accepted solution]

For completion of this query, I was able to resolve it using a IN operator. Thanks 

VAR _RLSProduct =
CALCULATETABLE(
VALUES('User'[RLS Product]),
'User'[email]=USERPRINCIPALNAME()
)

RETURN
SWITCH (TRUE(),
[Product] IN _RLSProduct , TRUE(),
FALSE()
)