Forum Discussion

xiumi_hou's avatar
xiumi_hou
Post Partisan
3 years ago

URGENT!!!! ROW LEVEL SECURITY NOT WORKING.. please help

Hi there,

 

We have a dashboard and limit the access to see their own data.

 

The internal manager and admin role can see all the people.

The external manager can only see external staff assigned to them

The employee can only see their own data.

 

We have the role in user table and we use the email account to to login in.

 

However...... in the workspace, no matter I give the employyee viewer or memebr... they can see everyone's filter can data.. below is the RLS function: 

VAR _role =
CALCULATETABLE(
VALUES('Users'[HARDCODED_ROLE])
, FILTER( 'Users' , 'Users'[user_name] = USERPRINCIPALNAME() )
)

var _admin = USERPRINCIPALNAME() in ALL('EXCEL admin_users'[User Name])

RETURN
IF( _admin
, True
, IF (

_role = "INTERNAL_MANAGER"
, True
, PATHCONTAINS([Path], [Current User ID]) = True
)
)

 

Need Help

5 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    xiumi_hou 

    One way to test RLS is to add a column to the table you are trying to filter using the DAX for the RLS.  In the example below I am adding a column called [RLS Column].  You can see I added _UPN variable so we can set it to a sepcific user and see which rows in the table return TRUE.  This will show you the rows that the user would be able to see.

     

     

    RLS Column =
VAR _UPN = USERPRINCIPALNAME()
VAR _UPN = "[email protected]"
VAR _role =
    CALCULATETABLE (
        VALUES ( 'Users'[HARDCODED_ROLE] ),
        FILTER ( 'Users', 'Users'[user_name] = _UPN )
    )
VAR _admin =
    _UPN IN ALL ( 'EXCEL admin_users'[User Name] )
RETURN
    IF (
        _admin,
        TRUE,
        IF (
            _role = "INTERNAL_MANAGER",
            TRUE,
            PATHCONTAINS ( [Path], [Current User ID] ) = TRUE
        )
    )

     

    Once you get it working you can use the same code in the RLS filter, you would just remove the VAR _UPN = "[email protected]" line

    • xiumi_hou's avatar
      xiumi_hou
      Post Partisan
      3 years ago

      Thank you so much. It show the error: Variable '_UPNNN' cannot be created because a table or variable with the same name already exists. not matter which variable name I changed to. Any suggestion? jdbuchanan71 

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    Oh, sorry, comment out the first one.

     

    RLS Column =
--VAR _UPN = USERPRINCIPALNAME()
VAR _UPN = "[email protected]"
VAR _role =
    CALCULATETABLE (
        VALUES ( 'Users'[HARDCODED_ROLE] ),
        FILTER ( 'Users', 'Users'[user_name] = _UPN )
    )
VAR _admin =
    _UPN IN ALL ( 'EXCEL admin_users'[User Name] )
RETURN
    IF (
        _admin,
        TRUE,
        IF (
            _role = "INTERNAL_MANAGER",
            TRUE,
            PATHCONTAINS ( [Path], [Current User ID] ) = TRUE
        )
    )

     

    The first one is the dynamic one that reads the current logged in user and is what you would actually use in the RLS DAX.  The hardcoded one is for testing.

     

  • xiumi_hou's avatar
    xiumi_hou
    Post Partisan
    3 years ago

    jdbuchanan71  I just tried the very based create a measure on user_userprinciplename(), and create a role and the email address to be there. It;s showing failed to resolved because the userprinciple name not working....