Forum Discussion

MrKETATA's avatar
MrKETATA
Frequent Visitor
3 years ago

multi RLS > subtractive rather than additive

hello community,   USECASE 1 Suppose we have a classic star schema, with dim product and dim region. requirement = we need to implement an RLS rule, users under this rule should only see "Bikes" ...
Need Help
v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
3 years ago

Hi MrKETATA ,

 

Your usecase 2 is the usual method.

1 add a role table( seperate parameterized dataset)

2 filter product and region table by two rls rules.

rls code like this:

 

product table:

[product_id] in calculatetable( values(roletable[productid]), filter( roletable, [useremail] = username()))

 

region table:

[region_id] in calculatetable( values( roletable[region_id]), filter( roletable,[useremail] = username())) 

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • MrKETATA's avatar
    MrKETATA
    Frequent Visitor
    3 years ago

    Thanks v-chenwuz-msft 


    The thing is, table in RLS1 will be used to assign our end userprincipalname() to a specific page name (has no business with data model), although, RLS2 is meant to filter our data model, finally i'll go with multi parameterized dataset, and leverage RLS1 only, I would suggest to disable RLS additive behavior on tables that are obviously not related.

    Regards.