I'm using ASP.NET with Entity Framework to load data to an API, the database is a multi tenant DB with multiple companies storing different data. I'm getting user information from a JWT token. When a user requests data, I verify the token, and load users data from the database.

Sample API queries are:

• /api/shops/{shopId}/orders the user passes in their shop id (a user can have access to multiple shops) and I validate if the user has access to the specified shop and load the data with filters.
• /api/shops/{shopId}/orders/{orderId}/items the user passes in their shop id and the order id and I validate if the user has access to the specified shop, then I validate that the shop has specified order, and that that the user has access to view the items, and load the data.

The way I implemented it at first is, I created a ServiceFilter at the controller level that validates the users access to the shop and/or order. This way queries from the database multiple times and I still need to pass in the shops info in my second query, but it has the benefit that I'm blocking the user at controller level (no other code runs when validating users access).

The issue with the old way is, I am doing same where clause in both queries, I need to pass in shop id in both queries...

Then I came up with another idea, why not save all ids that the user has access when I run the validation check and then only filter on those ids. The benefit is the same, I'm blocking at controller level. But the downside is that I am storing an unlimited number of ids in memory and it also runs two database queries.

The question is, should I stay with the old way of doing ServiceFilter only? Should only query the database once in the controller method and not block user at controller level? I'm still blocking unauthorized users. Or should I store the ids in memory? The ids are an indexed column and it will filter it further based on the ids I got from the service filter in controller method.

Maybe I don't fully understand the logic of validating user access. If that's the case, please help me understand.

I'm using ASP.NET with Entity Framework to load data to an API, the database is a multi tenant DB with multiple companies storing different data. I'm getting user information from a JWT token. When a user requests data, I verify the token, and load users data from the database.

Sample API queries are:

• /api/shops/{shopId}/orders the user passes in their shop id (a user can have access to multiple shops) and I validate if the user has access to the specified shop and load the data with filters.
• /api/shops/{shopId}/orders/{orderId}/items the user passes in their shop id and the order id and I validate if the user has access to the specified shop, then I validate that the shop has specified order, and that that the user has access to view the items, and load the data.

The way I implemented it at first is, I created a ServiceFilter at the controller level that validates the users access to the shop and/or order. This way queries from the database multiple times and I still need to pass in the shops info in my second query, but it has the benefit that I'm blocking the user at controller level (no other code runs when validating users access).

The issue with the old way is, I am doing same where clause in both queries, I need to pass in shop id in both queries...

Then I came up with another idea, why not save all ids that the user has access when I run the validation check and then only filter on those ids. The benefit is the same, I'm blocking at controller level. But the downside is that I am storing an unlimited number of ids in memory and it also runs two database queries.

The question is, should I stay with the old way of doing ServiceFilter only? Should only query the database once in the controller method and not block user at controller level? I'm still blocking unauthorized users. Or should I store the ids in memory? The ids are an indexed column and it will filter it further based on the ids I got from the service filter in controller method.

Maybe I don't fully understand the logic of validating user access. If that's the case, please help me understand.

• asp-core
• entity-framework-core

• Http is a stateless protocol. Use it as intended. For the rest the question is hard to answer without a coded example. Descriptions are ambiguous and easier than reality. For example, how would you "save all ids" when actually coded? – Gert Arnold Commented Feb 10 at 20:09
• Why pass Shop Id in the URL at all? Your code must not trust any passed tenant ID, the tenant ID should be part of the server session state and incorporated to every query. If users can belong to multiple tenants then that "selected shop ID" can be used. It would be unlikely to need to query data across multiple tenants. – Steve Py Commented Feb 10 at 20:45
• @GertArnold I would save it in a global list that I'll use in the calling class. I know it's stateless, the question is regarding accessing the DB multiple times. – Shmiel Commented Feb 11 at 13:51
• @StevePy The shop id is passed in because a user can be from multiple shops and I am checking if the user belongs to that shop. The question is if I should first check that at controller level and then do the normal query in the method. And if I'm doing it that way, should I rather save all Ids in controller level and the second query should be on those ids only? – Shmiel Commented Feb 11 at 13:54
• Still easier said than done. How will the calling class know it's their list? Note that global state is available to all requests. And when do you dispose the list? – Gert Arnold Commented Feb 11 at 14:07

1 Answer 1

It appears that you have a multi-layered authorization model. Role-based authorization is determining what users can do. Then you have a set of shopIds that limits what data users can access or modify when using their authorized functionality. I do something similar to this.

I create custom Claims (that get queried once - when the user authenticates) that then become the limitations for restricting what data the user can access or modify.

User Claims can be used within your ServiceFilter without having to hit the database and therefore injecting it into that filter or having to pull a context instance from the ServiceProvider.

This methodology may require that you create a way to authorize all shopIds to admin-type users - if this is part of your access model. It IS part of mine!

Good luck!