Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape 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.

Reply
Anonymous
Not applicable

Way to Query Users Security Role

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,

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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:

OwenAuger_1-1649742956727.png

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":

OwenAuger_2-1649743038992.png

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

Glad that was useful 🙂

That sounds like a great application, and you could certainly have a measure that determines the destination page based on visible rows of a Role table.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

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:

OwenAuger_1-1649742956727.png

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":

OwenAuger_2-1649743038992.png

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.