Forum Discussion
RLS with internal users file
Hi there!
I need to set RLS in my sales dataset. I plan to have just two access levels:
- AM (Account Manager) - The system will get user email and filter relevant clients via related dimension table
- FULL- Unrestricted access (all visible)
I have setup the user table as follows:
| Type | Account Manager | Surname | Name | Active | |
| [email protected] | AM | Doe John | Doe | John | True |
| [email protected] | FULL | Smith Harry | Smith | Harry | True |
| [email protected] | FULL | Simpson Caroline | Simpson | Caroline | True |
| [email protected] | FULL | Sanchez Romon | Sanchez | Romon | True |
| [email protected] | FULL | Garcia Esteban | Garcia | Esteban | True |
| [email protected] | AM | Bianchi Jane | Bianchi | Jane | True |
| [email protected] | FULL | Johnson Petra | Johnson | Petra | True |
The user table is connected to a client dimension table on the Account Manager column.
I need the system to work in a way that:
- If the user is of AM type, only relevant clients are visible
- If the user is of FULL type everything is visible (no filter)
- If the user is not present in the user table nothing is displayed!
For AM and FULL type all seems to work. If instead I simulate an user not censored in the user table everything is still displayed!!
I cannot therefore find an easy way to avoid that unknown users (not listed in the table above) are displaying everything. I know that I could restrict access via dataset security in PBI service but we are using groups so there is always the chance of a new user to be activated without being censored in my PBI user table.
Hope this is clear enough to provide me a bit of direction.
Regards,
C.
Hi Anonymous ,
You can refer the following links to get it by configuring dynamic RLS:
Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship - RADACAD
Dynamic Row-Level Security in Power BI with Exclude and Include Rules - RADACAD
powerbi - How does RLS behave when user does not match record in users table? - Stack Overflow
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:
And each user can see the the following customersSo if I view as the role as '[email protected]' I'll see only the data mapped to that user.
However if I set it as '[email protected]', you don't see anything.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:
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.
Best Regards
2 Replies
Hi Anonymous ,
You can refer the following links to get it by configuring dynamic RLS:
Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship - RADACAD
Dynamic Row-Level Security in Power BI with Exclude and Include Rules - RADACAD
powerbi - How does RLS behave when user does not match record in users table? - Stack Overflow
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:
And each user can see the the following customersSo if I view as the role as '[email protected]' I'll see only the data mapped to that user.
However if I set it as '[email protected]', you don't see anything.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:
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.
Best Regards