Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Row Level Security not working for users with no role

I have dynamic row level security set up in my report that works in a sort of hierarchical sense where you are given access at one of three levels. I have included the DAX for each level below:

 

Top Level:
VAR _t =
CALCULATETABLE (
VALUES ( 'Access_Table'[Access_Required] ),
FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
)
RETURN
IF (
CONTAINS ( _t, 'Access_Table'[Access_Required], "National" )
|| CONTAINS ( _t, 'Access_Table'[Access_Required], "Regional" )
|| CONTAINS ( _t, 'Access_Table'[Access_Required], "Individual" ),
'TableA'[Company]
IN CALCULATETABLE (
VALUES ( 'Access_Table'[Compan] ),
FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
),
TRUE()
)

Middle Level:

IF(
CONTAINS(
CALCULATETABLE(
VALUES( 'Access_Table'[Access_Required] ),
FILTER( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
'Access_Table'[Access_Required], "Regional"
),
'TableB'[Region]
IN CALCULATETABLE (
VALUES( 'Access_Table'[Region]),
FILTER('Access_Table','Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
TRUE()
)

Bottom Level:
IF(
CONTAINS(
CALCULATETABLE(
VALUES( 'Access_Table'[Access_Required] ),
FILTER( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
'Access_Table'[Access_Required], "Individual"
),
'TableC'[AGENCY]
IN CALCULATETABLE (
VALUES( 'Access_Table'[Agent_ID]),
FILTER('Access_Table','Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
TRUE()
)


All three queries refer to an access table which basically consists of the relevant information needed to filter the report. The problem I have is that when a user is not contained in the access table they are able to view all data in the report.

 

Is there an error in the DAX that is allowing this to happen?

  • Hi Anonymous ,

     

    _t will return blank value when a user is not contained in the access table. It will always return TRUE in your original IF() formula. 

    So you need to replace TRUE() with FALSE() or BLANK().

     

1 Reply

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

     

    _t will return blank value when a user is not contained in the access table. It will always return TRUE in your original IF() formula. 

    So you need to replace TRUE() with FALSE() or BLANK().