How does RLS behave when user does not match record in users table?

Question

Suppose I have sales and users table. The sales table has userid to link to the users table.

Dynamic RLS filter is configured on the users table's email column:

Email = USERNAME()

There is a role called User and I have added couple of users to it via the power bi service.

Hence whenever a user logs in he can see his own sales data.

However, when a user is a member of the User group but there is no record for that user in the Users table, then when he logs in there will be no filter applied because that email doesn't exist in the users table. Thus the user can see everyones data?

What is the workaround for this?

• Jon: In the service, if the user isn't mapped they should not see anything, just the visuals with a warning. But RLS only applies in the service when the users are the 'Viewer' role, and not admin, contributor or member
• variable: User is mapped to the role via power bi service. But the record with that username doesn't exist in users table. So effectively there should be no filter applied and all data exposed?

Answer 1

For those people not in a mapping table for RLS, they will not see any data. You can test this in Power BI Desktop For example in my mapping table of users, which link to customer, then to the data, I have two users:

Power BI User List

And each user can see the the following customers

Customer List

So if I view as the role as '[email protected]' I'll see only the data mapped to that user.

RLS Report Example

However if I set it as '[email protected]', you don't see anything.

Security trimmed report example

In the Service you have to added the users to roles as set up in the dataset security setting, if your not in the role in the service, you get a security warning:

enter image description here

If you are in the role, but are not in your mapping table, it will return no data, like it would in Power BI Desktop.

Please note: for RLS to work, the users must be in the 'Viewer' role at the workspace level. If they are Admin, Member or Contributor, they will be able to see all the data.