Scenario: a large existing system (~300 tables, 500 stored procs, 200 views and a code base of several 100k lines) with most security level stuff in stored procedures needs to be refactored (for maintainability reasons and just availability of skills more will likely be moving to the C# layer, as well as we are hoping for performance since we'll be able to better control what gets pulled when better).
Entity Framework is something we are seriously considering to make things more easily extensible (inheriting the backend schema from a base class for example without having to track down a massive join yourself each time).
Question: how do you handle security with Entity Framework? The examples I've seen where just how to get your model/data model to handle service wide security (tokens for can this guy login? types of things). How can you say a normal user can see these 3 fields on a class but an admin can see these 10? These fields could be logically other classes tables (eg. a particular customers orders). How about things like "this post is read"? Do you just add a list of "haveRead" people to the class or is there a smarter way to get EF to return different versions of the same object depending on who you are? Is there a way to get EF to do this for you without needed a lot of logic in stored procs? If not how do you manage performance (say a person can see a single object and you hit the model for a list of objects then do the filtering higher up in C# meaning you might be getting 1000's of items but only passing on 1 to the client). Can you lazy load individual fields so that if only weak users are making requests all the admin fields don't get pulled over from the database?
Best Answer
I have similar problem: I have several users with different permissions. Some of them can do everything and some of them can select/update only certain columns in tables. The solution I have found is the using of
OnModelCreating
method.Now I'd like to explain the general logic. I have database with users who are authenticated by SQL login. These users are mapped to one of custom database roles, and these roles, in their turn, are granted permissions. If you use Windows Authentication, there's really no difference for code since the idea would be the same: you would create Windows Groups, map your users to those groups and, finally, grant permissions to groups.
The core of this idea is
Ignore
method ofEntityTypeConfiguration<T>
used inOnModelCreating
method (which you override in your custom context class). This method lets us ignore the columns in entities which won't be used by context. So, you should set some condition which branches to code with filtering:The condition for filtering is the name of Database Role in SQL Server (if you use SQL Login) or Windows Group the user belongs to (if you use Windows Authentication).
1. Windows Groups
For Windows Groups it's easy - just fetch all groups the user belongs to and compare it to the one you need. Here's helper class which would fetch user's Windows Groups and check whether he belongs to the group passed as parameter:
Now we can use it in
OnModelCreating
:Now any selection from Detail table will not select these two properties (although, as I said earlier, you can use them in your entity class - they just will be initialized to their default values, but changes to them won't propagate to database).
2. SQL Logins
With SQL Logins, the user's permissions depend on his database role, so you have to obtain it before any interaction with context. Here's where things become a bit complicated. Before going further, first, I will use
dbo.GetUserRole
stored procedure which just fetches a single value - role a user belongs to:Second, I will add static
Role
property indicating user's database role:Now there's one caveat that awaits us: You can't fetch role inside
OnModelCreating
method.Just because EF will throw an exception:
Well, if you can't use context, then we can use context's
DbConnection
directly from constructor:So, for now we have the following. The
OnModelCreating
is called in one of two cases:1) When you call
DbContext.Database.Initialize
method.2) When you make a query against database (in this case
Database.Initialize
runs implicitly).As a side note, the documentation says:
However, this is not true.
So, the solution is to use context's connection and request a role. This:
1) must be done before any query or
Database.Initialize
callor
2) can be done in context's constructor.
Now when we have a role, we can use it in our code. I chose
String
type for storing a role, but you can also use enumeration for convenience:I don't know whether this pattern I chose is correct, but I haven't found any info over this theme - and I'm surprised, because in reality this is how databases are designed in mind - with granting different permissions to different users. So, I hope this will help you.
Good luck!