Isolating row-level data access doesn't need to be difficult and we'll see how to implement a neat solution to achieve this.

Situation

Your mission is to create a BlockNote type application that allows you to manage user notes. It will therefore be necessary to ensure that there is a partitioning of the data to prevent client A from being able to see the notes of client B.

Access management

ASP.NET Core identity server will be used to manage authentication and authorization. We will keep this simple by creating a DbContext that inherits from ApiAuthorizationDbContext<User>. Keep in mind that this part of the system could be seen as an external service and many applications could rely on it.

So, the User class will be tied to the identity of the user and hold only generic information. By deriving from IdentityUser all the required propreties will be defined like the userId, email and password.

/// <summary>
/// Identity user that can provide information for many applications.
/// </summary>
public class User : IdentityUser
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Setting up the application database

Specific applications propreties concerning a user will be hold in an other table called ApplicationUsers. To make it clear, ApplicationUsers and User witch will translate to the table AspNetUsers could be hosted in two seperate databases. No relations will exists between the two.

/// <summary>
/// Contains user defined propreties for this specific application.
/// </summary>
public class ApplicationUser 
{
    /// <summary>
    /// Primary key and also a foreign key to the user's identity id.
    /// </summary>
    public string Id { get; set; }

    public ThemeColor ThemeColor { get; set; }
}

The last entity created is for the user notes.

public class Note : AuditableEntity
{
    public string Text { get; set; }
}

As you can see, we are inheriting from the AuditableEntity class to enable record tracking by user. This is a modified version of what we find in the CleanArchitecture.

public abstract class AuditableEntity
{
    public string CreatedByUserId { get; set; }        
    public string ModifiedByUserId { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime? DateModified { get; set; }

    public ApplicationUser CreatedByUser { get; set; }
    public ApplicationUser ModifiedByUser { get; set; }
}

Now, we are going to model the secure database with a DbContext.

public class ApplicationDbContext : ApiAuthorizationDbContext<User>, IApplicationDbContext
{
    public DbSet<ApplicationUser> ApplicationUsers { get; set; }
    public DbSet<Note> Notes { get; set; }
}

To keep the article short, I'm going to avoid going into the details of how to do the plumbing of IdentityServer4 with authentication. Do not hesitate to leave a message if that would interest you.

Retrieving the user from the HttpContext

To let us know who is authenticated and currently making a request to our API, we are going to create the following service:

public interface ICurrentUserService
{
    string UserId { get; }
}

public class CurrentUserService : ICurrentUserService
{
    public const string SUBJECT_ID = "sub"; //Works with JwtSecurityToken

    public CurrentUserService(IHttpContextAccessor httpContextAccessor)
    {
        UserId = httpContextAccessor.HttpContext?.User?.FindFirstValue(SUBJECT_ID);
        IsAuthenticated = UserId != null;
    }

    public string UserId { get; }

    public bool IsAuthenticated { get; }
}

Note that depending on how your authentication is configured, you may need to replace SUBJECT_ID with ClaimTypes.NameIdentifier.

To allow dependency injection, we register the service like so :

public void ConfigureServices(IServiceCollection services)
{
    services.AddSingleton<ICurrentUserService, CurrentUserService>()
}

DbContext enhancement

Next, we inject the ICurrentUserService into our ApplicationDbContext to take advantage of it.

public class ApplicationDbContext : ApiAuthorizationDbContext<User>, IApplicationDbContext
{
    private readonly string _userId;
    private readonly IDateTime _dateTime;
    
    public ApplicationDbContext(
        ICurrentUserService currentUserService,
        IDateTime dateTime)
    {
        _userId = currentUserService.UserId;
        _dateTime = dateTime;
    }
    
    public DbSet<ApplicationUser> ApplicationUsers { get; set; }
    public DbSet<Note> Notes { get; set; }
}

Audit data changes

Now that we have all the necessary elements to track who is inserting or modifying DB records, we will insert the following method into the DbContext.

public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken())
{
    foreach (var entry in ChangeTracker.Entries<AuditableEntity>())
    {
        switch (entry.State)
        {
            case EntityState.Added:
                entry.Entity.CreatedByUserId = _userId;
                entry.Entity.DateCreated = _dateTime.Now;
                break;
            case EntityState.Modified:
                entry.Entity.ModifiedByUserId = _userId;
                entry.Entity.DateModified = _dateTime.Now;
                break;
        }
    }

    return base.SaveChangesAsync(cancellationToken);
}

The centralization of this logic will greatly simplify any modifications to the database.

Implicitly protect data leaks

To ensure that queries only retrieves data associated with the user in context, we will take advantage of Global Query Filters. They can be configured as follows in the DbContext:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    ApplyProtectionFilters(builder);
}

private void ApplyProtectionFilters(ModelBuilder builder)
{
    builder.Entity<Note>()
        .HasQueryFilter(x => x.CreatedByUserId == _userId);
}

We can now simplify many of the Linq expressions because we no longer need to specify the user when reading from the database. In addition, the use of the CurrentUserService service will no longer be necessary in a lot of places.

//Before
var notes = _context.Notes.Where(n => n.CreatedByUserId == _userId;
//After
var notes = _context.Notes; //Implicit user filtering

In the optics that you want to perform data set operations regardless of the user, you can remove the filter with the IgnoreQueryFilters method.

var nbNotes = _context.Notes.IgnoreQueryFilters().Count();

Conclusion

Thanks to the centralization of the logic within the DbContext, all the operations on the entities will be tracked and partitioned, thus ensuring data security and a reduction of repetitive code.

You can find a working solution of what I talked in this article in the DataProtection branch on my GitHub. I also opened a pull-request on the CleanArchitecture repository from Jason Taylor as there is a lot of good stuff.

For further reading, I recommend this very interesting article:
https://www.thereformedprogrammer.net/part-2-handling-data-authorization-asp-net-core-and-entity-framework-core/