Forum Discussion

perezco's avatar
perezco
Advocate III
2 years ago

Dynamic RLS with masked user and partner

I am testing out row level security in my pbix file and it is not working as expected.

Looking  in use a  “Single Role for all the User”

The model has three tables : One is Fact table that has aggregate data,  second Dim -person is just general information of the agent   and third table is RLS security.

Current active relation between tables

Fact table    (user_id)  <--- dim person )person_id)
Dim person (badge_id) <---> RLSE security (badge_id)  M2M.

Reference: https://radacad.com/find-the-data-value-using-lookupvalue-dax-function-in-power-bi-simple-and-useful

 

MODELmodel

Type of Data from  RLS security table

type of data

 

partner

role partner

example data

  • "Role Partner column" is designed to limit outsources' access to their data while allowing local users to see all data.
    The "Masked column" is a component of the "Masked Protected flag," and it serves to identify the user who was impacted by the masking. This allow only managers from the same location to view their agents, with the agent's name being Masked= 1 from managers in other locations.
  • Under table 'RLS Security' to have the parent/child relation>> created column
    Relationship = PATH('RLS Security'[USER_BDGE_NBR],'RLS Security'[MGR_BDGE_NBR])
  • Then, under ‘manage roles’ > ‘Manage security roles’
    • Created the role called : RLS Security Mask Partner
    • Then, went to the Table>> ‘RLS Security’
    • And under dax editor added the following
    • But this logic is failing(no error message 😞

 

 

VAR upn = SUBSTITUTE(SUBSTITUTE(USERPRINCIPALNAME(), "_","."),"@mall","@mell")
VAR bdg = MAXX(FILTER('RLS Security‘, 'RLS Security ‘[user_Email_ID] = upn),'RLS Security ‘[user_BDGE_NBR])
VAR condition1= PATHCONTAINS('RLS Security ‘[Relationship], bdg)
VAR condition2 = 'RLS Security'[Masked]
VAR condition3= 'RLS Security' [role partner]

Return
( NOT(condition1) && NOT(condition2) && NOT(condition3) )
||
(NOT(condition1) && NOT(condition2) && condition3)
||
(NOT(condition1) && condition2 && NOT(condition3) )
||
(NOT(condition1) && condition2 && condition3)
||
(condition1 && NOT(condition2) && NOT(condition3) )
||
(condition1 && NOT(condition2) && condition3)
||
(condition1 && condition2 && NOT(condition3) )
||
(condition1 && condition2 && condition3)

 

 





Need Help
Tips and Tricks

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Your Return statement will always evaluate to True. Please review it.

    • perezco's avatar
      perezco
      Advocate III
      2 years ago


      Thank you. This means that I have to mantain what I need.

  • perezco's avatar
    perezco
    Advocate III
    2 years ago

    In this moment I can get  from the 'all_in_one RSL' >> user role_partner =  0 or 1   see all .

     

    'existing code' 

     

    VAR upn = SUBSTITUTE(SUBSTITUTE(USERPRINCIPALNAME(), "_","."),"@mall","@mell")
VAR bdg = MAXX(FILTER('RLS Security', 'RLS Security'[user_Email_ID] = upn),'RLS Security ‘[user_BDGE_NBR])
VAR condition1= PATHCONTAINS('RLS Security'[Relationship], bdg)
VAR condition2 = 'RLS Security'[Masked]
VAR condition3= 'RLS Security' [role partner]
Return
// 1,0,1
(condition1 && NOT(condition2) && condition3)
||
//1,1,1
(condition1 && condition2 && condition3)
||
//0,1,0
(NOT(condition1) && condition2 && NOT(condition3) )
||
//0,0,1
(NOT(condition1) && NOT(condition2) && condition3)

     

     


    But I am  looking

    • user role_partner =  0    see Everything.

    user role_partner =  1  see only their data



    Conceptual -I 'm not sure this can be done

     

    IF ('RLS Security'[Role Partner] = 0, 
'existing code'...,
'only show own data')