Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello,
Is there a way to query users' security roles through which they're viewing reports in the service?
Basically, if I create a security role for RLS or OLS--"TestSecRole"--and assign a user to that role in the service, I know that I could get the user's username through USERPRINCIPALNAME(), but could I also see that the user is viewing through/belongs to TestSecRole?
Thanks,
Solved! Go to Solution.
Hi @Anonymous
Are you looking for a way to query current user's role(s) in a way that can be accessed in the report itself, e.g. returned by a measure?
I'm not aware of a built-in function that can do this, however, you could set up a table containing the names of the roles, and define a RLS filter on that table within each role, filtered to the value corresponding to that Role.
To take a simple example, if you had 4 roles, "North", "South", "East", "West", then you create a table Roles with column Role:
Then for each Role, define a RLS table filter on the Roles table, filtered to the value with the name of the current role, e.g. for North role, filter Roles using the expression Roles[Role] = "North":
Then when the user accesses a report connected to this dataset, the visible rows of the Roles table will correspond to that user's roles.
A user who is in both North & South would see those two values in the Roles table (since the user would see the union of all rows visible under each role that they are a member of).
You could create a measure such as this to return the current user's roles as a concatenated string:
Current User Roles =
CONCATENATEX (
Roles,
Roles[Role],
",",
Roles[Role]
)
For the North/South user, this measure would return "North, South".
Is this along the lines of what you were wanting to do?
Regards,
Owen
Hi @Anonymous
Are you looking for a way to query current user's role(s) in a way that can be accessed in the report itself, e.g. returned by a measure?
I'm not aware of a built-in function that can do this, however, you could set up a table containing the names of the roles, and define a RLS filter on that table within each role, filtered to the value corresponding to that Role.
To take a simple example, if you had 4 roles, "North", "South", "East", "West", then you create a table Roles with column Role:
Then for each Role, define a RLS table filter on the Roles table, filtered to the value with the name of the current role, e.g. for North role, filter Roles using the expression Roles[Role] = "North":
Then when the user accesses a report connected to this dataset, the visible rows of the Roles table will correspond to that user's roles.
A user who is in both North & South would see those two values in the Roles table (since the user would see the union of all rows visible under each role that they are a member of).
You could create a measure such as this to return the current user's roles as a concatenated string:
Current User Roles =
CONCATENATEX (
Roles,
Roles[Role],
",",
Roles[Role]
)
For the North/South user, this measure would return "North, South".
Is this along the lines of what you were wanting to do?
Regards,
Owen
Thanks, @OwenAuger, this is clever.
Basically, the reason I'd want to do this is to use it with conditional navigation and object-level security. A home-page on a report with an "Enter" button could direct users to a page corresponding to their security role and OLS settings. This would prevent the user from seing a bunch of broken visuals depending on their security role.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |