Variable RLS

[Anonymous]

I am beginning to think that what I want is not possible. Help me out here.

I have a report with several different pages. Each page has various stats broken down by user. I want to set up security so that on different pages, users have different levels of access. So on Page 1, users can see stats for all users. On Page 2, users can only see their own stats.

I thought of using a second disconnected table called SecurityBehavior with a colum called PageType. The column has 3 values: "All", "Team Restricted", "User Restricted". Ignore "Team Restricted" for now. Each page would have a page level filter from this column. Then the security role has a filter on the Users table:

IF(
	FIRSTNONBLANK(SecurityBehavior[PageType], 1)  = "User Restricted",
	Users[EmailAddress] = USERNAME(),
	NOT(ISBLANK(Users[UserID]))
)

But it doesn't work. All users have unrestricted access to everything on every page, whether the filter is set to "All" or "User Restricted".

So is there a way to do what I want?

[GilbertQ]

Hi @Anonymous

I would think that you would have to configure your data with the Roles, and then have each Role look at a different table. So possibly you would have 3 tables one for each of the requirements. And then apply a role to each table based on how you want to apply the RLS?

[Anonymous]

This is a huge dataset with a lot of tables. Everything comes back to the user table though.

Imagine a sales report. On page 1 you have sales for the whole company, plus a breakdown of sales by sales rep. On page 2 you have individual sales reps' weekly performance. I want the sales reps to see everything on page 1, but only their own results on page 2.

[GilbertQ · Accepted solution]

I would think that it might be possible, but if it is all linked back to the User table, then the way RLS is designed it would then apply that security across the entire data model.

Which would mean that the user would only see their own data.