RLS with IF condition

[Aashishjain27]

Requirement is if following condition meets then only RLS should apply else RLS wont apply and user sees entire data.

following code not working id condition is meeting still user seeing entire data and RLS not applying. this all logic from single table which is filtering the data to fact and connected. workaround ? extratable ? Column ? 

= IF(
(
(CONTAINSSTRING([terr_id], "SCC") || CONTAINSSTRING([terr_id], "SNN"))
&& [terr_type] = "Territory"
),
LOWER(TRIM([emp_email])) = LOWER(TRIM(USERPRINCIPALNAME())),
TRUE()
)

[lbendlin]

Your condition translates into 

=
IF (
    ( CONTAINSSTRING ( [terr_id], "SCC" ) || CONTAINSSTRING ( [terr_id], "SNN" ) )
        && [terr_type] = "Territory"
        && LOWER ( TRIM ( [emp_email] ) ) <> LOWER ( TRIM ( USERPRINCIPALNAME () ) ),
    FALSE (),
    TRUE ()
)

This may not be what you intended.  You are also mixing static and dynamic RLS, which makes it even more complex.

[Aashishjain27]

Its dynamic only. following logic is working but the issue issue when the condition doesnt meet like [terr_id] <>"SCC", I want user to see entire data but with this logic user not seeing any data

IF (

    ( CONTAINSSTRING ( [terr_id], "SCC" ) || CONTAINSSTRING ( [terr_id], "SNN" ) )

        && [terr_type] = "Territory"

        && LOWER ( TRIM ( [emp_email] ) ) = LOWER ( TRIM ( USERPRINCIPALNAME () ) ),

    True (),

    FALSE()

)

[lbendlin]

That's not how dynamic RLS works. You are supposed to hold a permission mapping table that is keyed off the USERPRINCIPALNAME and then controls the rest of the data model.

You don't seem to have the required dimension tables for this?

[v-menakakota]

Hi @Aashishjain27 ,
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @lbendlin   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @Aashishjain27 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team