Handling row level security with Entity Framework Core
Its been written before but maybe this makes more sense to someone
At work we are currently going through a migration to modernize from a .net 4 winforms app to a modern web app with HTML, CSS, Javascript, and .net core on the backend. Part of this is also making our permissions more manageable and centrally applied so the same logic is not in 6 different places.
For the .net core web api we decided to go with JWT and Api Key authentication depending on the various ways users and applications will interact with the backend (Api Key for automated processes, JWT for users using the web app or winforms app during the soft migration).
The different authentication methods can live side by side very nicely together, it just takes a bit of configuration that is different from all the tutorials you see out there.
The Security Needs
The application is a multi tenant system where we have clients that can access only their data, office support staff that can access the data for the various clients they support, and accounting staff that have access to all the data in the system to generate reports and invoices to bill our clients.
The application also has user roles that limit what functions the user has access to in the app (set up new users, set up new customers, etc) which is handled by the asp.net core Authorization libraries (Policy based authorization with the Authorize attribute)
The Problem
Previously the logic for row level access had been spread across the forms (the winforms app had everything, business logic, data access, view logic etc. in the form.vb code behind). That also meant that the logic of what users could access what clients data was spread out across 50 different files and duplicated when the different forms needed to query the same tables. In some cases the logic was applied the same but in others it was applied slightly differently leading to fun bugs to track down when users complained they could not see a client they should have access to.
EF Core to the rescue
The bare data model
With the upgrade and migration to DRY and SRP we are adding EF Core as the data access layer. EF Core is a great repository and unit of work and super easy to use in the simple cases. Here we have a not so simple case.
The context looks similar to this (Entity Framework Core 2.1):
public class User
{
public User()
{
UserClientAccess = new HashSet<UserClientAccess>();
}
public int Id { get; set; }
public string UserName { get; set; }
public string PasswordHash { get; set; }
//... Other relevant fields
//... Other relevant fields
public ICollection<UserClientAccess> UserClientAccess { get; set; }
}
public class UserOptions
{
public int Id { get; set; }
public int UserId { get; set; }
public int OptionId { get; set; }
public int OptionValue { get; set; }
}
public class Client
{
public int Id { get; set; }
public string ClientName { get; set; }
public bool IsDeleted { get; set; }
public DateTime? DeletedDate { get; set; }
//... Other relevant fields
}
public class UserClientAccess
{
public int Id { get; set; }
public int UserId { get; set; }
public int ClientId { get; set; }
public Client Client { get; set; }
}
public class Context : DbContext
{
public Context(DbContextOptions<Context> options) : base(options)
{
}
public virtual DbSet<User> User { get; set; }
public virtual DbSet<Client> Client { get; set; }
public virtual DbSet<UserClientAccess> UserClientAccess { get; set; }
public virtual DbSet<UserOptions> UserOptions { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<User>(entity =>
{
entity.HasKey(x => x.Id);
entity.HasMany(x => x.UserClientAccess)
.WithOne()
.HasForeignKey(x => x.UserId);
});
modelBuilder.Entity<Client>(entity =>
{
entity.HasKey(x => x.Id);
entity.HasMany(x => x.UserClientAccess)
.WithOne(x=>x.Client)
.HasForeignKey(x => x.ClientId);
});
modelBuilder.Entity<UserClientAccess>(entity =>
{
entity.HasKey(x => x.Id);
});
modelBuilder.Entity<UserOptions>(entity =>
{
entity.HasKey(x => x.Id);
});
}
}
The old way to get some code reuse
In the past, before Global Filter Queries (using EF6) I have created query extensions in a static class
public static class AuthorizationExtensions
{
public static IQueryable<Client> ApplyUserAccessPermissions(this IQueryable<Client> clients,
User user)
{
return clients.Where(x => x.UserClientAccess.Any(y => y.UserId == user.Id));
}
}
public class ClientManager
{
private readonly Context _context;
private readonly User _user;
public ClientManager(Context context, User user)
{
_context = context;
_user = user;
}
public IEnumerable<Client> Get()
{
return _context.Client.ApplyUserAccessPermissions(_user)
.ToList();
}
public Client Get(int clientId)
{
return _context.Client.ApplyUserAccessPermissions(_user).Where(x=>x.Id == clientId)
.Single();
}
}
This has the benefit of centralizing the access logic but still has the limitation that anywhere we need to access the DbSet<Client> Client we need to remember to call .ApplyUserAccessPermissions(). Its better than nothing but maybe we can get better?
On that project it eventually went to this
public class ClientManager
{
private readonly Context _context;
private readonly User _user;
public ClientManager(Context context, User user)
{
_context = context;
_user = user;
}
private IQueryable<Client> ClientBase => _context.Client.ApplyUserAccessPermissions(_user);
public IEnumerable<Client> Get()
{
return ClientBase.ToList();
}
public Client Get(int clientId)
{
return ClientBase.Where(x=>x.Id == clientId)
.Single();
}
}
This got a little DRYer but is the same basic idea.
A modern solution for a modern time
EF Core 2.0 introduced Global Query Filters which allow for a global where clause on the entity. This opens up some nice things for soft deletes, multitenancy, user specific data etc. If you can think of a reason to limit access or apply a consistent query filter its really useful.
Soft Deletes - The quick intro to query filters
For soft deletes the query filter is pretty easy, call the HasQueryFilter and pass in a lambda on the field.
modelBuilder.Entity<User>(entity =>
{
entity.HasQueryFilter(x => !x.IsDeleted);
entity.HasKey(x => x.Id);
entity.HasMany(x => x.UserClientAccess)
.WithOne()
.HasForeignKey(x => x.UserId);
});
If you only have a few tables with soft deletes this will work fine however if you have more than a couple there are ways through interfaces, overidden .SaveChanges(), and dynamically adding query filters to simplify this further and reduce repetition.
User Specific filters
For multitenancy or user specific data we need to take a couple of extra steps. Global Query Filters can use properties on the Context. (They can also use fields but because of Model Caching the values are cached, use properties). The main way to get data into the context is through dependency injection.
If we add an interface similar to
public interface IClaimsProvider
{
int UserId { get; }
IEnumerable<int> AccessibleClientIds { get; }
}
We can change our context by injecting our IClaimsProvider and assigning some private properties.
public class Context : DbContext
{
private readonly IClaimsProvider _claimsProvider;
private int UserId => _claimsProvider.UserId;
private IEnumerable<int> AccessibleClientIds => _claimsProvider.AccessibleClientIds;
public Context(DbContextOptions<Context> options, IClaimsProvider claimsProvider) : base(options)
{
_claimsProvider = claimsProvider;
}
...
}
We now have access to the data we need for our filters. For a user data filter the model builder for user becomes
modelBuilder.Entity<Client>(entity =>
{
entity.HasQueryFilter(x => AccessibleClientIds.Contains(x.Id));
entity.HasKey(x => x.Id);
entity.HasMany(x => x.UserClientAccess)
.WithOne(x => x.Client)
.HasForeignKey(x => x.ClientId);
});
modelBuilder.Entity<UserOptions>(entity =>
{
entity.HasQueryFilter(x => x.UserId == UserId);
entity.HasKey(x => x.Id);
});
This sets up our context so we don’t have to worry about permissions anywhere else in the application. Whether that’s having a TenantId on every table or only having a few tables where row level permissions are needed.
But there’s this one place I don’t want filters
There will probably be some occasions you don’t want to apply the filters. In those cases using .IgnoreQueryFilters() will explicitly remove the row level protections in the few places you don’t need them.
How to inject the data into the context
Now that we have the code for the context written, when using Microsoft.Extensions.DependencyInjection (or another IOC container) all we do is register an implementation of IClaimsProvider.
One possible IClaimsProvider for asp.net core using asp.net core identity would be this. There is lots of null checking because there is the possibility that queries are ran before we have a user on the HttpContext so lets not bomb and introduce errors that may be hard to track down.
public class ClaimsProvider : IClaimsProvider
{
private readonly IHttpContextAccessor accessor;
public ClaimsProvider(IHttpContextAccessor accessor)
{
this.accessor = accessor;
}
public int UserId => int.TryParse(accessor.HttpContext?.User?.Claims?.SingleOrDefault(x => x.Type == "UserId")?.Value, out var bob) ? bob : 0;
public IEnumerable<int> AccessibleClientIds => accessor.HttpContext?.User?.Claims?.Where(x => x.Type == "AccessibleClientId").Select(x => int.Parse(x.Value)).ToList() ?? new List<int>();
}
Wrapping Up
EF Core 2.0 added some really nice features to the framework for row level security. What use to be hacked together with external libraries or handled with extension methods that needed to be applied every where and working around limitations have been replaced with relatively simple configuration / setup code in one place.
With Global Query Filters we can have security in place with out having to worry about adding security code to our Business Logic Layer (or if you want to put a repository over a repository, in a repository class)