question

RyanCole-6309 avatar image
0 Votes"
RyanCole-6309 asked Bruce-SqlWork answered

Is it possible to have an EF Core middleware or something that can apply to filter to every query?

Hi folks,

I've got an existing ASP.NET Core web application that uses EF Core for the database queries. A requirement has come up that requires me to basically filter every query's result using some logic that takes into consideration a user's "permissions". So like, if a user should not be allowed to ever see a particular column in the results, or if a user should never be able to see a particular row with a specific id, etc.

Like I said, this is an existing application and so I'm wondering if there's a nice spot to put some sort of global EF filter in place? As opposed to writing my own method and going through the entire ASP.NET application and modifying every location that uses EF Core.

Is there something like a middleware for EF Core perhaps? Or global filtering before or after a query is realized?

I'm trying to get an idea of what my options might be here before I go and modify hundreds of lines of code.

Thanks!

dotnet-entity-framework-core
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Just wanted to mention that I'm aware of EF Core Global Query Filters, but to my knowledge those look like they're static filters that take no run-time parameters. I'm trying to apply a particular filter logic to my queries based on some parameters that will be different for every user. So, if the EF Global Query Filters could be passed in a parameter that would be perfect for me, but it doesn't look like that's possible. Is it?


0 Votes 0 ·

@ RyanCole-6309, Welcome to Microsoft Q&A, you could refer to the question How to implement for a Web API project field level permission with EF Core 6 and value objects to deal with the problem related to ef core permissions.


0 Votes 0 ·

Permission is a design requirement that affects the table schema. I doubt that you can accomplish this requirement if the application was designed without permissions in mind.

Can you show us an example of the global filter and how/why the global filter would work with the current unknown database schema?

0 Votes 0 ·

1 Answer

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered

as long as the permission are in the database, and mapped to EF, and the connection is logged in as the user, you could use global filters to filter rows, but not columns.

to handle column filtering you could use column masking (again requires the connection use the user login):

https://docs.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview?view=azuresql

note: security should never be an after thought, you should always design the system with security requirements. in this case, you should probably recode the system, rather than looking for a quick hack.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.