.Net core query data from database based on the roles of the logged in user

Question

NET 3.1 core project. I have registered the application in azure AD and I have made configurations to receive roles in JWT token. I have roles like Global Admin, Regional Admin and User. currently In .Net core I have APIS and APIS will send and receive data from database. Most of the APIS will be accessed by all the roles as below.

[Authorize(Roles = "GlobalAdmin,RegionAdmin,User")]

Now coming to DAL or data later part, I need to retrieve data based on the roles. For example,

If the Role is GlobalAdmin then return all the data If the Role is RegionalAdmin then return region specific data If the role is user then return only his data.

I have below implentation in DAL layer

  var returnData = await _mysamplerepository.GetAsync(x => x.id == request.rId, null, x => 
  x.ReferenceSystem).ConfigureAwait(false);

This is some sample query before RBAC. Now I want to return the data based on users roles. I am thinking something like this

if(role == "GlobalAdmin")
Then return all the data(Similar to select * from)
if(role == "RegionAdmin")
Then return data for that region(similar to select * from table name where region = some region)
if(role == "user")
Then return only his data(similar to select * from table where createdby = 'currentuser')

Above is dummy code used to explain the logic what I have in my mind. I am wondering this is the right approach or any other formal approaches available to handle this kind of situations.

Now Another question I have in my mind is In JWT token I have roles. When I apply

[Authorize(Roles = "GlobalAdmin,RegionAdmin,User")]

automatically .Net will read roles from token and do the authorization. Now If I want to pass role details down to DAL, I need to read roles and pass it to DAL. So I am just wondering how can we read grammatically roles from JWT token and pass it down to DAL layer to apply some business logic based on the roles.

Can someone help me regarding this? Any help would be appreciated. Thanks

Are you able to pass ID of the user to the SQL statement (or EntityFramework, if you use it)? — TheMixy, Jan 06 '21 at 15:19
Then, how can you tell which user created which record (for what you're trying to filter when user role is "user") ? — TheMixy, Jan 07 '21 at 07:34
I am storing username in db. In my db I have some fields like recordcreatedby,region. when any user wants to see records created by themself then I will compare with recordcreatedby. this is for normal user. But when regional admin logs in he/she has to see all the records created in that region. For that we have region field in db. Let me know If still not clear Thank you TheMixy — Niranjan Godbole Hosmar, Jan 07 '21 at 08:18

unknown · Answer 1 · 2021-01-07T06:55:20.810

You could use User.IsInRole("roleName") in methods, refer to here.

if(User.IsInRole("GlobalAdmin")) {
    // Then return all the data(Similar to select * from)
}
if(User.IsInRole("RegionAdmin")) {
    // Then return data for that region(similar to select * from table name where region = some region)
}
if(User.IsInRole("user")) {
    // Then return only his data(similar to select * from table where createdby = 'currentuser')
}

If the roles you mentioned are app roles in azure ad, you could call the Microsoft Graph API.

string graphRequest = $"https://graph.microsoft.com/v1.0/users/{user-object-id}/appRoleAssignments";
HttpClient client = new HttpClient();
HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Get, graphRequest);
request.Headers.Authorization = new AuthenticationHeaderValue("Bearer", authResult.AccessToken);
HttpResponseMessage response = await client.SendAsync(request);

Update:

user-object-id in portal:

or you could list users with API, the id will show you object-id.

https://graph.microsoft.com/v1.0/users?$filter=startswith(displayName,'xxxx')

OP's solution: I can get roles from claims as well right as below.

User.Identities.SelectMany(s => s.Claims).Where(s => s.Type.Contains("role")).Select(s => s.Value);

seems refer to here: https://stackoverflow.com/a/21690079/13308381

Thank you very much for your answer Pamela Peng. As you explained in the above answer How can I get User object? As explained in other example, they are retrieving user details from DB but in my case I am not storing user details in DB. All managed in Azure AD — Niranjan Godbole Hosmar, Jan 06 '21 at 09:29
Also what I observed is I can use User.IsInRole("roleName") in only in controller level and cannot be used in my data access layer. I want to query data from db based on the roles — Niranjan Godbole Hosmar, Jan 06 '21 at 12:01
just question, instead of calling graph api, I can get roles from claims as well right as below User.Identities.SelectMany(s => s.Claims).Where(s => s.Type.Contains("role")).Select(s => s.Value); — Niranjan Godbole Hosmar, Jan 07 '21 at 04:45

score 0 · Answer 2 · answered Jan 07 '21 at 08:44

We have a business app with a very similar situation. Having 'user', 'group admin' and 'admin' as roles.

In your scenario you can avoid using If statements in your 'business' logic and move the conditions to SQL queries (or EF queries). For example:

SELECT * FROM [table]
WHERE [UserRole]='GlobalAdmin' 
OR ( [UserRole]='RegionAdmin' AND [Region] = 'get region from user' )
OR ( [UserRole]='User' AND [CreatedBy] = UserId );

This approach is also very straightforward to implement in EntityFramework, if you use it.

.Net core query data from database based on the roles of the logged in user

2 Answers2