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.
- Part 3: A better way to handle authorization – six months on.
- Part 4: Building robust and secure data authorization with EF Core (this article).
- Part 5: A better way to handle authorization – refreshing user’s claims.
- Part 6: Adding user impersonation to an ASP.NET Core web application
- Part 7: Implementing the “better ASP.NET Core authorization” code in your app
UPDATE: See my NDC Oslo 2019 talk which covers these three articles.
Original articles:
- A better way to handle authorization in ASP.NET Core – original article.
- Handling data authorization in ASP.NET Core and Entity Framework Core.
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:
- 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.
- 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.
- 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.
- 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.
- 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.
- filter-Only DbContext: I build a specific DbContext to contain all the classes/tables that need to be filtered.
- 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.
- 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.
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?
Hi Jeremy,
I’m not quite sure what you are trying to achieve, but let me cover some things that might help.
Firstly, you can have tables that anyone can access in a multi-tenant system – you simply don’t configure a Query Filter on that table.
If you are thinking about hierarchical system, then it depending on where you are in the hierarchy defines what you can see: for instance
Finally, if you just need a one-level multi-tenant database, where a user, or a group of users, are linked to one set of data, then you should at this example in Part 2 of this series.
I hope that helps
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
Hi Jeremy,
OK, now I understand. If you read an entity class with relationships EF Core will apply your query filters to ALL parts of that query, i.e. the main entity class and any relationships.
So, in your example when you query table1 (which doesn’t have a query filter) with table2 (which DOES have a query filter) then it will return table1 and all the table2 rows that are a) linked to table via a foreign key, and b) the query filter return true (i.e. userId == table2.UserId).
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
Hi Henry,
There are so many options here, so I need more info. Here are the options/questions:
Then these questions:
Hi Jon,
Thanks for quick reply.
My answers/thoughts:
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.
Hi Henry,
If your problem is that a user is registered with multiple tenants, then you can catch this (they will have multiple DataKey) at login time and offer a list of which tenant thy want to work on today. I had just that situation with my client work. You need to make the
UserDataAccessKey
have a composite key containing UserId and the TennantKey. Its easy and don’t change too much, but the user has to log out/back in again if they want to change tenant.If you want to want to dynamically your going to have to change the users DataKey claim. To do this you need to tap into the ValidateAsync event that happens every HTTP request. Part 5 uses this to change the users permissions, but you would just change the DataKey. Be warned, you need to be careful to not access the database every time the ValidateAsync event happens otherwise you will slow down the app for everyone. Memory cache is one way, but depends on your app.
I did think about Regex in the query filter and don’t think it would work.
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
Great, glad you worked it out.
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
Hi Leroux,
Get you like the tutorial. It was a lot of work but really useful if I need to do it again.
It’s pretty hard to debug your code as so much is going on. The only thing I can say is that accessor.HttpContext can be null, which happens in some cases (startup being one of them), so you need to handle that (in my code the DataKey is null). Other than that I can’t say what is going on.
I’m glad you are adding to the design. Why don’t you write an article yourself and share it with others.
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 betweenUserId
andDataKey
. 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
(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.)
Hi Mark,
I have good news for you in that I worked with a client that uses Azure AD. They had found a way to intercept the Azure AD OpenID Connect and we could add our own claims – see the link below.
https://joonasw.net/view/adding-custom-claims-aspnet-core-2
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?
Ah, that’s a pity.
I would assume ASP.NET Core’s authorize system will kick in so you could catch the login via the
UserClaimsPrincipalFactory
and add the DataKey there (see this section in part 3 of this article).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!
Hi Sanders,
Its hard to get a hierarchical key combined with a multiple keys that is efficient. If the data keys aren’t hierarchical then you can make a comma delimited string containing all the data keys, e.g. “,123,456,543,” and use DataKey.Contains($”,{context.DataKey},”) in the Query Filter.
That’s the best I can offer. If you work out how to do it then do write about it and send me a link.
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?
Hi Kml,
I’m not sure I fully understand what you are asking, but here is my answers.
This is about performance. Its easier to look up a user and get the datakey when they log in. By putting the datakey in the user’s Claims it is saved by the ASP.NET Core authorise system in either a cookie or token. That means you don’t have to an extra lookup for every database access, and it also makes it easier to the EF Core Query Filter to control what the user can see.
This is a bit complicated because this example is about building a hierarchical system. That means you might have two shops called “Shop” in two branches, say San Fran and LA, so you can’t just use the shop’s name. Also the manager needs to be able to access the data in all the shops in his branch, for instance the manager with a data key 1|2| can access a shop with a key of 1|2|3 and shops with a key of 1|2|99 (and 1|2|99|4 etc. down the hierarchy).
If you don’t need a hierarchical system, then its a lot simpler. The part 2 article in this series covers a simple system with a key per person.
I hope that helps.
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
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.
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).
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…
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.
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. 🙂
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?
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!
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?
Perfect sense. Thank you!