Pass website permissions to embedded report

[MCassady]

I have a report embedded in a website page.

There are two SQL tables, which control what buildings/data a user on the website can see/access.

1. The User table holds things like user name, encrypted password, contact info.

2.  The UserSites table links those users with which buildings they will see on the website.

Example:

User

UserID	UserName	Email
1	Bob	bob@bob.ca
2	John	john@john.com

UserSites

UserID	BuildingID
1	10
1	11
1	12
2	10

Based on this data, Bob would be able to see building 10,11, and 12, but John can only see data for building 10.

Is there a way to pass this relationship into an embedded powerbi report, to limit what the authenticated user sees based on their login from the parent website?

I have read that row level security may help, but I think I would have to set it up, and republush the report every time we get a new user or new building. This seems like too manual of a process.

[TomMartens]

Hey @MCassady ,

you have to use Row Level Security but you can make it dynamic by using the function USERPRINCIPALNAME.
Next to that you have to create a relationship between the user and the usersites table.
Of course, you have to make sure that both tables are containing the proper data, meaning new users must be added to the user table and the usersites table has to be adapted as well.

If this is given each data refresh makes sure that the user are seeing what they are allowed to see.
Hopefully, this provides an idea on how to tackle your challenge.

Regards,

Tom

[MCassady]

I think I've figured it out. I have to have the role also checked, not just the "Other User"

Is there a way to set up RLS JUST for "other user." That way we do not have to add/remove employees roles as they come or go? It'd be nice to just have them in our User SQL table, as they will be in there without any extra powerbi steps.

[MCassady]

Hey @TomMartens ,

I am trying to set this up as you suggested, however I am having trouble testing it.

I created this relationship:

As well as the following RLS:

And here is my test data:

User:

UserSite:

However, when I click "view as 'Other User'", and use an email that IS NOT in this list... I am still able to see the data on the screen. Am I doing something wrong?

When I enter in cassady@cassady.com, I would expect the dashboard to only allow me to see data for TestSite1.

Any ideas?