Part 4: Building a robust and secure data authorization with EF Core

Last Updated: July 31, 2020 | Created: July 9, 2019

This article covers how to implement data authorization using Entity Framework Core (EF Core), that is only returning data from a database that the current user is allowed to access. The article focuses on how to implement data authorization in such a way that the filtering is very secure, i.e. the filter always works, and the architecture is robust, i.e. the design of the filtering doesn’t allow developer to accidently bypasses that filtering.

This article is part of a series and follows a more general article on data authorization I wrote six months ago. That first article introduced the idea of data authorization, but this article goes deeper and looks at one way to design a data authorization system that is secure and robust. It uses a new, improved example application, called PermissionAccessControl2 (referred to as “version 2”) and a series of new  articles which cover other areas of improvement/change.

UPDATE: See my NDC Oslo 2019 talk which covers these three articles.

Original articles:

TL;DR; – summary

  • This article provides a very detailed description of how I build a hierarchical, multi-tenant application where the data a user could access depends on which company and what role they have in that company.
  • The example application is built using ASP.NET Core and EF Core. You can look at the code and run the application, which has demo data/users, by cloning this GitHub repo.
  • This article and its new (version 2) example application is a lot more complicated than the data authorization described in the original (Part 2) data authorisation article. If you want to start with something, then read the original (Part 2) first.
  • The key feature that makes it work are EF Core’s Query Filters, which provides a way to filter data in ALL EF Core database queries.
  • I break the article into two parts:
    • Making it Secure, which covers how I implemented a hierarchical, multi-tenant application that filters data based on the user’s given data access rules.
    • Making it robust, which is about designing an architecture that guides developers so that they can’t accidently bypass the security code that has been written.

Setting the scene – examples of data authorization

Pretty much every web application with a database will filter data – Amazon doesn’t show you every product it has, but tried to show you things you might be interested in. But this type of filtering for the convenience of the user and is normally part of the application code.

Another type of database filtering is driven from security concerns – I refer to this this as data authorization. This isn’t about filtering data for user convenience, but about applying strict business rules that that dictate what data a user can see. Typical scenarios where data authorization is needed are:

  • Personal data, where only the user can read/alter their personal data.
  • Multi-tenant systems, where one database is used to support multiple, separate user’s data.
  • Hierarchical systems, for instance a company with divisions where the CEO can see all the sales data, but each division can only see their own sales data.
  • Collaboration systems like GitHub/BitBucket where you can invite people to work with you on a project etc.

NOTE: If you are new to this area then please see this section of the original article where I have a longer introduction to data protection, both what’s it about and what the different part are.

My example is a both a multi-tenant and a hierarchical system, which is what one of my client’s needed. The diagram below shows two companies 4U Inc. and Pets2 Ltd. with Joe, our user in charge of the LA division of 4U’s outlets.

The rest of this article will deal with how to build an application which gives Joe access to the sales and stock situation in both LA outlets, but no access to any of the other divisions’ data or other tenants like Pets2 Ltd.

In the next sections I look at the two aspects: a) making my data authorization design secure, i.e. the filter always works, and, b) its architecture is robust, i.e. the design of the filtering doesn’t allow developer to accidently bypasses that filtering.

A. Building a secure data authorization system

I start with the most important part of the data authorization – making sure my approach is secure, that is, it will correctly filter out the data the user isn’t allowed to see. The cornerstone of this design is EF Core’s Query Filters, but to use them we need to set up a number of other things too. Here is a list of the key parts, which I then describe in detail:

  1. Adding a DataKey to each filtered class: Every class/table that needs data authorization must have a property that holds a security key, which I call the DataKey.
  2. Setting the DataKey property/column: The DataKey needs to be set to the right value whenever a new filtered class is added to the database.
  3. Add the user’s DataKey to their claims: The user claims need to contain a security key that is matched in some way to the DataKey in the database.
  4. Filter via the DataKey in EF Core: The EF Core DbContext has the user’s DataKey injected into it and it uses that key in EF Core Query Filters to only return the data that the user is allowed to see.

A1. Adding a DataKey to each filtered class

Every class/table that needs data authorization must have a property that holds a security key, which I call the DataKey.

In my example application there are multiple different classes that need to be filtered. I have a base IDataKey interface which defines the DataKey string property. All the classes to be filter inherit this interface. The DataKey definition looks like this

As you will see the DataKey is used a lot in this application.

A2. Setting the DataKey property/column

The DataKey needs to be set to the right value whenever a new filtered class is added to the database.

NOTE: This example is complex because of the hierarchical data design. If you want a simple example of setting a DataKey see the “personal data filtering” example in the original, Part 2 article.

Because of the hierarchical nature of my example the “right value” is bit complex. I have chosen to create a DataKey than is a combination of the primary keys of all the layers in the hierarchy. As you will see in the next subsection this allows the query filter to target different levels in the multi-tenant hierarchy.

The diagram below shows you the DataKeys (bold, containing numbers and |) for all the levels in the 4U Company hierarchy.

The hierarchy consists of three classes, Company, SubGroup and RetailOutlet, which all inherit from an abstract class called TenantBase. This allows me to create relationships between any of the class types that inherit from TenantBase (EF Core to treat these classes as a Table-Per-Hierarchy, TPH, and stores all the different types in one table).

But setting the DataKey on creation is difficult because the DataKey needs the primary key, which isn’t set until its created in the database. My way around this is to use a transaction. Here is the method in the TennantBase class that is called by the Company, SubGroup or RetailOutlet static creation factory.

protected static void AddTenantToDatabaseWithSaveChanges
    (TenantBase newTenant, CompanyDbContext context)
{
    // … Various business rule checks let out

    using (var transaction = context.Database.BeginTransaction())
    {
        //set up the backward link (if Parent isn't null)
        newTenant.Parent?._children.Add(newTenant);
        context.Add(newTenant);  //also need to add it in case its the company
        // Add this to get primary key set
        context.SaveChanges();

        //Now we can set the DataKey
        newTenant.SetDataKeyFromHierarchy();
        context.SaveChanges();

        transaction.Commit();
    }
}

The Stock and Sales classes are easier to handle, as they use the user’s DataKey. I override EF Core’s SaveChanges/SaveChangesAsync to do this, using the code shown below.

public override int SaveChanges(bool acceptAllChangesOnSuccess)
{
    foreach (var entityEntry in ChangeTracker.Entries()
        .Where(e => e.State == EntityState.Added))
    {
        if (entityEntry.Entity is IShopLevelDataKey hasDataKey)
            hasDataKey.SetShopLevelDataKey(accessKey);
    }
    return base.SaveChanges(acceptAllChangesOnSuccess);
}

A3. Add the user’s DataKey to their claims:

The user claims need to contain a security key that is matched in some way to the DataKey in the database.

My general approach as detailed in the original data authorization article is to have claim in the user’s identity that is used to filter data on. For personal data this can be the user’s Id (typically a string containing a GUID), or for a straight-forward multi-tenant it would by some form of tenant key stored in the user’s information. For this you might have the following class in your extra authorization data:

public class UserDataAccessKey
{
    public UserDataAccessKey(string userId, string accessKey)
    {
        UserId = userId ?? throw new ArgumentNullException(nameof(userId));
        AccessKey = accessKey;
    }

    [Key]
    [Required(AllowEmptyStrings = false)]
    [MaxLength(ExtraAuthConstants.UserIdSize)]
    public string UserId { get; private set; }

    [MaxLength(DataAuthConstants.AccessKeySize)]
    public string AccessKey { get; private set; }
}

In this hierarchical and multi-tenant example it gets a bit more complex, mainly because the hierarchy could change, e.g. a company might start with simple hierarchy of company to shops, but as it grows it might move a shop to sub-divisions like the west-coast. This means the DataKey can change dynamically.

For that reason, I link to the actual Tenant class that holds the DataKey that the user should use. This means that we can look up the current DataKey of the Tenant when the user logs in.

NOTE: In Part 5 I talk about how to dynamically update the user’s DataKey claim of any logged in user if the hierarchy they are in changes.

public class UserDataHierarchical
{
    public UserDataHierarchical(string userId, TenantBase linkedTenant)
    {
        if (linkedTenant.TenantItemId == 0)
            throw new ApplicationException(
                "The linkedTenant must be already in the database.");

        UserId = userId ?? throw new ArgumentNullException(nameof(userId));
        LinkedTenant = linkedTenant;
    }

    public int LinkedTenantId { get; private set; }

    [ForeignKey(nameof(LinkedTenantId))]
    public TenantBase LinkedTenant { get; private set; }

    [Key]
    [Required(AllowEmptyStrings = false)]
    [MaxLength(ExtraAuthConstants.UserIdSize)]
    public string UserId { get; private set; }

    [MaxLength(DataAuthConstants.AccessKeySize)]
    public string AccessKey { get; private set; }
}

This dynamic DataKey example might be an extreme case but seeing how I handled this might help you when you come across something that is more complex than a simple value.

At login you can add the feature and data claims to the user’s claims using the code I showed in Part 3, where I add to the user’s claims via a UserClaimsPrincipalFactory, as described in this section of the Part 3 article.

The diagram below shows how my factory method would lookup the UserDataHierarchical entry using the User’s Id and then adds the current DataKey of the linked Tenant.

A4. Filter via the DataKey in EF Core

The EF Core DbContext has the user’s DataKey injected into it and it uses that key in EF Core Query Filters to only return the data that the user is allowed to see.

EF Core’s Query Filters are a new feature added in EF Core 2.0 and they are fantastic for this job. You define a query filter in the OnModelCreating configuration method inside your DbContext and it will filter ALL queries, that comprises of LINQ queries, using Find method, included navigation properties and it even adds extra filter SQL to EF Core’s FromSql method (FromSqlRaw or FromSqlInterpolated in EF Core 3+). This makes Query Filters a very secure way to filter data.

For the version 2 example here is a look the CompanyDbContext class with the query filters set up by the OnModelCreating method towards the end of the code.

public class CompanyDbContext : DbContext
{
    internal readonly string DataKey;

    public DbSet<TenantBase> Tenants { get; set; }
    public DbSet<ShopStock> ShopStocks { get; set; }
    public DbSet<ShopSale> ShopSales { get; set; }

    public CompanyDbContext(DbContextOptions<CompanyDbContext> options,
        IGetClaimsProvider claimsProvider)
        : base(options)
    {
        DataKey = claimsProvider.DataKey;
    }

    //… override of SaveChanges/SaveChangesAsync left out

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //… other configurations left out

        AddHierarchicalQueryFilter(modelBuilder.Entity<TenantBase>());
        AddHierarchicalQueryFilter(modelBuilder.Entity<ShopStock>());
        AddHierarchicalQueryFilter(modelBuilder.Entity<ShopSale>());
    }

    private static void AddHierarchicalQueryFilter<T>(EntityTypeBuilder<T> builder) 
         where T : class, IDataKey
    {
        builder.HasQueryFilter(x => x.DataKey.StartsWith(Datakey));
        builder.HasIndex(x => x.DataKey);
    }
}

As you can see the Query Filter uses the StartsWith method to compare the user’s DataKey and the DataLeys of the tenants and their Sales/Stock data. This means if Joe has the DataKey of 1|2|5| then he can see the Stock/Sales data for the shops “LA Dress4U” and “LA Shirt4U” – see diagram below.

NOTE: You can try this by cloning the PermissionAccessControl2 repo and running it locally (by default it uses an in-memory to make it easy to run). Pick different users to see the different data/features you can access.

B. Building a robust data authorization architecture

We could stop here because we have covered all the code needed to secure the data. But I consider data authorization as a high-risk part of my system, so I want to make it “secure by design”, i.e.  it shouldn’t be possible for a developer to accidently write something that bypasses the filtering. Here are the things I have done to make my code robust and guide another developer on how to do things.

  1. Use Domain-Driven design database classes. Some of the code, especially creating the Company, SubGroup and RetailOutlet DataKeys, are complicated. I use DDD-styled classes which provides only one way to create or update the various DataKeys and relationships.
  2. filter-Only DbContext: I build a specific DbContext to contain all the classes/tables that need to be filtered.
  3. Unit test to check you haven’t missed anything: I build unit tests that ensure the classes in the filter-Only DbContext have a query filter.
  4. checks: With an evolving application some features are left for later. I often add small fail-safe checks in the original design to make sure any new features follow the original design approach.

B1. Use Domain-Driven design (DDD) database classes

DDD teaches us that each entity (a class in .NET world) should contain the code to create and update itself and its aggregates. Furthermore, it should stop any other external code from being able to bypass theses methods so that you are forced to use the methods inside the class. I really like this because it means there is one, and only one, method you can call to get certain jobs done.

The effect is I can “lock down” how something is done and make sure everyone uses the correct methods. Below the TenantBase abstract class which all the tenant classes inherit from showing the  MoveToNewParent method that moves a tenant to another parent, for instance moving a RetailOutlet to a different SubGroup.

public abstract class TenantBase : IDataKey
{
    private HashSet<TenantBase> _children;

    [Key]
    public int TenantItemId { get; private set; }
    [MaxLength(DataAuthConstants.HierarchicalKeySize)]
    public string DataKey { get; private set; }
    public string Name { get; private set; }

    // -----------------------
    // Relationships 

    public int? ParentItemId { get; private set; }
    [ForeignKey(nameof(ParentItemId))]
    public TenantBase Parent { get; private set; }
    public IEnumerable<TenantBase> Children => _children?.ToList();

    //----------------------------------------------------
    // public methods

    public void MoveToNewParent(TenantBase newParent, DbContext context)
    {
        void SetKeyExistingHierarchy(TenantBase existingTenant)
        {
            existingTenant.SetDataKeyFromHierarchy();
            if (existingTenant.Children == null)
                context.Entry(existingTenant).Collection(x => x.Children).Load();

            if (!existingTenant._children.Any())
                return;
            foreach (var tenant in existingTenant._children)
            {                   
                SetKeyExistingHierarchy(tenant);
            }
        }

        //… various validation checks removed

        Parent._children?.Remove(this);
        Parent = newParent;
        //Now change the data key for all the hierarchy from this entry down
        SetKeyExistingHierarchy(this);
    }
    //… other methods/constructors left out
}

The things to note are:

  • Line 3: The Children relationship is held a private field which cannot be altered by outside code. It can be read via the IEnumerable<TenantBase> Children property (line 17) but you can’t add or remove children that way.
  • Lines 6 to 16: Similarly, all the properties have private setters, so it can only be changed by methods inside the TenanBase class.
  • Lines 22 to the end: This has the code to change the relationship and then immediately runs a recursive method to change all the DataKeys of the other tenants underneath this one.

B2. Build a filter-Only DbContext

One possible problem could occur if a non-filtered relationship was present, say a link back to some authorization code (there is such a relationship linking a tenant to a UserDataHierarchical class). If that happened a developer could write code that could expose data that the user shouldn’t see – for instance accessing the UserDataHierarchical class could expose the user’s Id which I wish to keep secret.

My solution was to create a separate DbContext for the multi-tenant classes, with a different (but overlapping) DbContext for the extra authorization classes (see the diagram below as to what this looks like). The effect is to make a multi-tenant DbContext which any contains the filtered multi-tenant data. For a developer makes it clear what classes you can access when using multi-tenant DbContext.

NOTE: having multiple DbContexts with a shared table can make database migrations a bit more complicated. Have a look at my article “Handling Entity Framework Core database migrations in production” for different ways to handle migrations.

B3. Using unit tests to check you haven’t missed anything

With feature and data authorization I add unit tests that check I haven’t left a “hole” in my security. Because I have a DbContext specifically for the multi-tenant data I can write a test to check that every class mapped to the database has a Query Filter applied to it. Here is the code I use for that.

[Fact]
public void CheckQueryFiltersAreAppliedToEntityClassesOk()
{
    //SETUP
    var options = SqliteInMemory.CreateOptions<CompanyDbContext>();
    using (var context = new CompanyDbContext(options, 
        new FakeGetClaimsProvider("accessKey")))
    {
        var entities = context.Model.GetEntityTypes().ToList();

        //ATTEMPT
        var queryFilterErrs = entities.CheckEntitiesHasAQueryFilter().ToList();

        //VERIFY
        queryFilterErrs.Any().ShouldBeFalse(string.Join('\n', queryFilterErrs));
    }
}
public static IEnumerable<string> CheckEntitiesHasAQueryFilter(
    this IEnumerable<IEntityType> entityTypes)
{
    foreach (var entityType in entityTypes)
    {
        if (entityType.QueryFilter == null &&
             entityType.BaseType == null  && //not a TPH subclass
             entityType.ClrType
                 .GetCustomAttribute<OwnedAttribute>() == null && //not an owned type
             entityType.ClrType
                 .GetCustomAttribute<NoQueryFilterNeeded>() == null) //Not marked as global
            yield return 
                 $"The entity class {entityType.Name} does not have a query filter";
    }
}

B4. Adding fail-safe checks

You need to be careful of breaking the Yagni (You Aren’t Gonna Need It) rule, but a few fail-safe checks on security stuff makes me sleep better at night. Here are the two small things I did in this example which will cause an exception if the DataKey isn’t set properly.

Firstly, I added a [Required] attribute to the DataKey property (see below) which tells the database that the DataKey cannot be null. This means if my code fails to set a DataKey then the database will return a constraint error.

[Required] //This means SQL will throw an error if we don't fill it in
[MaxLength(DataAuthConstants.HierarchicalKeySize)]
public string DataKey { get; private set; }

My second fail-safe is also to do with the DataKey, but in this case I’m anticipating a future change to the business rules that could cause problems. The current business rules say that only the users that are directly linked to a RetailOutlet can create new Stock or Sales entries, but what happens if (when!) that business rule changes and divisional managers can create items in a RetailOutlet. The divisional managers don’t have the correct DataKey, but a new developer might miss that and you could “lose” data.

My answer is to add a safely-check to the retail outlet’s DataKey. A retail outlet has a slightly different DataKey format – it ends with a * instead of a |. That means I can check a retail outlet format DataKey is used in the SetShopLevelDataKey and throw an exception if it’s not in the right format. Here is my code that catches this possible problem.

public void SetShopLevelDataKey(string key)
{
    if (key != null && !key.EndsWith("*"))
        //The shop key must end in "*" (or be null and set elsewhere)
        throw new ApplicationException(
             "You tried to set a shop-level DataKey but your key didn't end with *");

    DataKey = key;
}

This is a very small thing, but because I know that change is likely to come and I might not be around it could save someone a lot of head scratching working out why data doesn’t end up in the right place.

Conclusion

Well done for getting to the end of this long article. I could have made the article much shorter if I only dealt with the parts on how to implement data authorization, but I wanted to talk about how handling security issues should affect the way you build your application (what I refer to as have a “robust architecture”).

I have to say that the star feature in my data authorization approach is EF Core’s Query Filters. These Query Filters cover ALL possible EF Core based queries with no exceptions. The Query Filters are the cornerstone of data authorization approach which I then add a few more features to manage user’s DataKeys and do clever things to handle the hierarchical features my client needed.

While you most likely don’t need all the features I included in this example it does give you a look at how far you can push EF Core to get what you want. If you need a nice, simple data authorization example, then please look at the Part 2 article “Handling data authorization in ASP.NET Core and Entity Framework Core” which has a personal data example which uses the User’s Id as the DataKey.

0 0 votes
Article Rating
Subscribe
Notify of
guest
32 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Jeremy
Jeremy
2 years ago

Would this work with a table(a) which doesnt have the userid on it but links to another table(b) which also doesnt have a userid but the table(c) it joins to does in fact have one?

So if I wanted to all the data from table(c) would I only get their data?

If not is it possible, in a generic implementation?

Jeremy
Jeremy
2 years ago
Reply to  Jon P Smith

Basically i’m querying an entity which has no userid on it. But it does have a join to its parent table where it does have a userid. How can I ensure the sub table does belong to the user?

Because I have this sort of database design for serval tables I can’t just do a simple global query of x => x.userid because its not always x with a userid it could be x => x.table1.table2.userid

if that makes sense? I think it’s not possible

Henry Roeland
Henry Roeland
2 years ago

Hi Jon,
Great article, thanks for sharing it!
In my current project we build our security around this article and like it a lot.
But as we are evolving new feature requests come up and one of them is: Allow a user access to 2 or more Tenants(So not all with *). My college came up with a solution using Alternate Keys.
But what about regular expressions to do the match (or not)? Would you consider using regular expression in the User Claim and letting the Database (PostgreSQL in our case) match this regex?
Thanks for any thoughts about this.
Kind regards,
Henry

Henry Roeland
Henry Roeland
2 years ago
Reply to  Jon P Smith

Hi Jon,
Thanks for quick reply.

My answers/thoughts:

  1. Yes we are using 3 levels: Tenant/Organization/Department
  2. Its new user not a manager.
  1. With current alternate key solution we do a refresh / relogin after choosing another tenant.
  2. So yes (zie 1)
  3. As far as I know we do not want this. One current Tenant only.

So just was/am curious if regular expressions would be a valid choice. Setting the regex in the user claim, and let the C# code try to match it with the Data Security Key in the table(s).

Accoording to Postgres Entity Framework Core there is a Regex.IsMatch (https://www.npgsql.org/efcore/mapping/translations.html) which can be used for this but did not test it yet.

Leroux
Leroux
2 years ago

Hi Jon,
I finally figure it out. The code “claimsProvider.DataKey;” returns null when it is in the constructor of the DbContext (like in the code of the GitHub repository).
As soon as I moved the call to the claims outside the constructor, it worked. I am using EfCore 3.1 and maybe the repository is running under an older version.
Sebastien

Gerhard
Gerhard
3 hours ago
Reply to  Leroux

For an example of how to “move the call to the claims outside the constructor” refer to https://stackoverflow.com/questions/63612303/user-claim-in-dbcontext-constructor-sometimes-null

Leroux
Leroux
2 years ago

Hi Jon,
This tutorial is amazing and my source of inspiration for the project I am building.
I have an issue reading the DataKey claim from GetClaimForUser class (it is always null for every call to the DbContext) and my DbContext then doesn’t give access to any data.
If I use the below code to read the claim, it works in the app (controller) but crashes (in GetClaimForUser) when I build it because httpcontext or User is not set up yet:
var tenantDataKey = HttpContext.User.FindFirst(DataAuthConstants.HierarchicalKeyClaimName).Value;
I also tried with nullable (?) with no result.

As info : I use multi tenancy for separate companies and I added support so that users (like consulants) can be registered for accessing multiple tenants.
I also use the tenants with a boolean flag to be used as Libraries and proposed as readonly to chosen users. If an item is used by a tenant, It is copied (on the tenant data) with “UpdateDate” and “LibraryOriginalItemId” properties to allow later update on the tenant side. For access to that, I created a new claim called “ReadOnlyDataKeys” which is a List<DataKey> and combine everything with a Predicate.
I can share more about those topics if you want.
Best regards.
Sebastien

Mark
Mark
3 years ago

Hi Jon,

Based on your articles we are implementing (a derived) design. One of the restrictions we have is that we use Microsoft Identity Platform (Azure AD) for Authentication and the providing of the Identity. This means we don’t issue our own tokens, and I am not able to include the DataKey as a claim (for roles we rely on Application Roles that are available on Azure AD).

As an alternative I am considering to implement Microsoft.Extensions.Caching.Memory/IMemoryCache in my DbContext with a dictionary between UserId and DataKey. I would prevent it from getting stale by doing a check in my SaveChanges() and SaveChangesAsync() if the persisted mapping is touched.

I have no experience with MemoryCache and I am not sure my approach would be sound. Would you have any comments on my intended implementation?

Regards,
Mark

Mark
Mark
3 years ago
Reply to  Mark

(I did try to research this option, but most questions and articles are about caching the DbContext itself, which is not what I want. I also read about 2nd level caching, but that seems overkill for this one dictionary and I don’t want to rely on a third party library for this seemingly simple cache and core design of my data security.)

Mark
Mark
3 years ago
Reply to  Jon P Smith

Thanks Jon, I had seen that article (perhaps even in one of your posts (: ). However the issue is that I am using Javascript SPA as the client, and the tokens are issued to this application. I did discuss (in quite a bit of details) with Microsoft Azure support how I could use the OpenID Connect Events for adding claims. In the end we concluded it would not be possible.

Did your client use a SPA as client with a Web API? Or did they have a Web Application (like Razor) where the tokens were issued to the ASP.Net application?

Last edited 3 years ago by Mark
Sander
Sander
3 years ago

Hi Jon, thanks for sharing this great series of articles.

What if Joe suddenly needs access to 1|2|4|7 as well? So in fact, in two separated parts of the hierarchy? Then we need to store a list of DataKeys in the user claim? How would you check in the QueryFilter that Joe has access?

thanks!

Kml
Kml
3 years ago
Reply to  Jon P Smith

This is what i was looking for for last 1h reading your articles and trying to understand that complex scenarios. Why you don’t simply use existing column in entity/table instead of adding custom datakey – this would remove one step from tutorial. I still try to figure out how to assign “key” for filtering for the lowest level of the hierarchy i.e. retail outlets – why we can’t simply define retail outlet names in scope (claims) of user and filter table on column with outlet names? How to do this?

Last edited 3 years ago by Kml
Jon P Smith
3 years ago

Hi Jeremy @disqus_ii92PNQPeP

For some reason your comment was lost by Disqus, or you deleted it.

Thanks for sending me a message that some on my articles were “broken”. You were right, WordPress did an update that messed up my code. It took me the whole morning to get that fixed! Quite annoying.

I’m glad the articles have been useful to you. I agree that it would be nice to split the Roles/Permissions and DataKey but this series has already taken a great deal of time to code and write. I don’t regret that, but I have to minimise the effort. I also try to keep the various part in separate projects or folders so you can delete the bulk of the code and then see what breaks in the common code.

All the best with you project

Jeremy Navarro
Jeremy Navarro
3 years ago
Reply to  Jon P Smith

Not sure what happened to my comment, but thank you for replying, and fixing the article! Also, thanks for the idea about pulling the projects out of your complete solution. I might give that a go to help me speed up adopting it. I realize that these articles are an extra effort, so please don’t take my comment as a complaint, I really appreciate you sharing your work! 🙂 All the best.

Jon P Smith
3 years ago
Reply to  Jeremy Navarro

Hi Jeremy,

Glad that comment worked! If you want to ask any more questions then please do (but I might be a bit slow at replying with the weekend coming up).

Pelle
3 years ago

Why not just send the userid to sql and only allow executions of stored procedures, then have the mapping in database. You won’t be able to hack it even…

Jon P Smith
3 years ago
Reply to  Pelle

That’s certainly an option and you can do that. What I was describing was how you could implement a multi-tenant system using EF Core. Overall I think EF Core is solution is a good one. Sure, any software system can be hacked but I don’t think EF Core is less secure than a SQL solution.

Pelle
3 years ago
Reply to  Jon P Smith

If you allow sql-login other rights than execute then you have a higher risk.
Sure it is secure enough but it is less secure than a SQL-sokution, especially if you run the application as a serviceaccount and have integrated trust in fonnectionstrung then you have maximum security. 😉

But still, then it wouldn’t be EF Core multi-tenant. I really liked your system, even if I had a question in your part 1. 🙂

Brian Wied
Brian Wied
3 years ago

Great series of articles.

Quick question though, will you explain in a little more detail what you mean when you say “lose” data in the fail-safe check section?

Jon P Smith
3 years ago
Reply to  Brian Wied

I have added an answer next to your comment, but Disqus is not sending notifications (pain!). I hope sending this from the Disqus web site will get to you!

Jon P Smith
3 years ago
Reply to  Brian Wied

The way I designed this example the divisional manager didn’t have the right key to create new items in a RetailOutlet. If I didn’t do anything the divisional manager could add a item and it would look like it worked, but it wouldn’t have the correct key – so the item would be there, but “lost”.

I don’t like thinks that fails silently, so I came up with a format for the DataKey such that if the user’s DataKey didn’t end with a “*” then it would throw an exception.

Does that make sense?

Brian Wied
Brian Wied
3 years ago
Reply to  Jon P Smith

Perfect sense. Thank you!