How to apply custom cascading security on Entity Framework ASP.NET Core application

Setting the scene

We have an ASP .NET Core application using Entity Framework code-first.

In our application, we have a number of different relational objects with user customisable data access permissions. These permissions consist of user allow/deny rules and group allow/deny rules (groups being a collection of users).

We also have a number of inheritance lines occurring. As an example, there may be a Town object, a Street object, a House object and a Room object. Permissions (user/group allow/deny) can be set against each of these objects.

• Town has Streets as a navigation property
• Street has Houses as a navigation property
• House has Rooms as a navigation property

You cannot access a Room unless you satisfy the allow/deny rules set on the Room itself, the parent House, the parent Street and the parent Town.

What is the best way to configure this in Entity Framework keeping in mind performance issues?

Current Configuration

We currently have a custom filter expression in our DbContext which checks whatever object is being accessed:

// Allow access if user on user or group allow list and not a denied user or group
entityExpression = t =>
            ((((IFullAuditedEntityWithSecurity)t).Permissions.Any(security => security.UserId == AbpSession.UserId && security.PermissionState == PermissionState.Allow) ||
            ((IFullAuditedEntityWithSecurity)t).Permissions.Any(security => security.Group.Members.Any(p => p.UserId == AbpSession.UserId) && security.PermissionState == PermissionState.Allow)) &&
            !((IFullAuditedEntityWithSecurity)t).Permissions.Any(security => security.UserId == AbpSession.UserId && security.PermissionState == PermissionState.Deny) &&
            !((IFullAuditedEntityWithSecurity)t).Permissions.Any(security => security.Group.Members.Any(p => p.UserId == AbpSession.UserId) && security.PermissionState == PermissionState.Deny)) ||
            ((IFullAuditedEntityWithSecurity)t).AllowPublicAccess;

Problems with security start to occur when checking the inheritance line, for example:

// If street, check parent town security
if (typeof(IStreet).IsAssignableFrom(typeof(TEntity)))
{
    Expression<Func<TEntity, bool>> parentSecurity = t => ((IStreet)t).Town != null;
    parentSecurity = isBeingAdded != null ? parentSecurity.Or(isBeingAdded) : parentSecurity;
    expression = expression == null ? parentSecurity : CombineExpressions(expression, parentSecurity);
}

// If house, check parent street security
if (typeof(IHouse).IsAssignableFrom(typeof(TEntity)))
{
    Expression<Func<TEntity, bool>> parentSecurity = t => ((IHouse)t).Street != null;
    parentSecurity = isBeingAdded != null ? parentSecurity.Or(isBeingAdded) : parentSecurity;
    expression = expression == null ? parentSecurity : CombineExpressions(expression, parentSecurity);
}

// If room item, check parent house security
if (typeof(IRoom).IsAssignableFrom(typeof(TEntity)))
{
    Expression<Func<TEntity, bool>> parentSecurity = t => ((IRoom)t).House != null;
    parentSecurity = isBeingAdded != null ? parentSecurity.Or(isBeingAdded) : parentSecurity;
    expression = expression == null ? parentSecurity : CombineExpressions(expression, parentSecurity);
}

We also have some additional filters so that users can get auto access to child elements if they already have access to parent objects. For example, if a user is a town member they get auto access to any child streets:

if (typeof(IStreet).IsAssignableFrom(typeof(TEntity)))
{
	Expression<Func<TEntity, bool>> allowTownMembersExpression = t =>
				((IStreet)t).Town.Members.Any(townMember => townMember.UserId == AbpSession.UserId && townMember.StartDate <= DateTime.Now && (townMember.EndDate >= DateTime.Now || townMember.EndDate == null)) &&
				!((IFullAuditedEntityWithSecurity)t).Permissions.Any(security => security.Group.Members.Any(p => p.UserId == AbpSession.UserId) && security.PermissionState == PermissionState.Deny) &&
				!((IFullAuditedEntityWithSecurity)t).Permissions.Any(security => security.UserId == AbpSession.UserId && security.PermissionState == PermissionState.Deny);

	entityExpression = entityExpression == null ? allowTeamMembersExpression : entityExpression.Or(allowTeamMembersExpression);
}

This is all working nicely, except for the fact the performance is not the greatest. Using profiler we can see that the scripts generated any time we try to access any object are huge to say the least. The security checks on all the parent objects and the subsequent get of users, groups, group members foreach one of those parent objects in resulting in scripts that are way too slow and way too big.

Considering that this is our business case and we need to enforce this inheritance security system, is there any other way of going around it that would still satisfy the business logic but be less of a load on the database every time we access any object?

Thank you in advance

** Note: The problem with relational database is the depth of relations. We get the best where we reduce these relations.

I have a world of objects and each object has a set of permissions and through this permissions actors of our world are able to access to specific object.

In my point of view I break it to two phase validation on Permissions: 1.Submission and 2.Evaluation

1.Submission Phase:
In this phase a I'm about to edit these permissions (I assumed that permission editing is a low repetition task vs reading permissions, That's a key point). In this phase I will apply the most overheads possible. I will check permission inheritances, cascading permission evaluating, conditional permission checking and the all other situations that are imposed by my application requirements.
At the end of the story I just store something like this Alice has permission x on Object y, maybe something like this:

 ObjectY.PermissionSet.Add(new PermissionGrant() 
 { 
     Type = "Walking", 
     Actor = "Alice", 
     Access = "Granted" 
 }); 

I have only 1 relation in this implementation. MyWorldObject to PermissionSet. Thats a lots of performance enhancement.

2.Evaluation Phase:
In this phase I just want to check whether Alice has permission X on street Y or not. Thats a simple task, low overhead. I just fetch the street object and I will looking for Alice there.

I'm not satisfied with this implementation still, because I have a growing number of Actors and I have to store a lots of records on PermissionGrant table (in my example). I will go through using indexes on this table. maybe composite indexes. (Read more on indexes.)

If you are still not satisfied with this implementation you should go for caching scenarios.

Main issue with this implementation is updating permissions and lake of permission inferences.

• You can lower updating cost based on the occurrence of Acccess and Update operations. to lower this cost you should balance these checkings between Submission and Evaluation phase.

• About permission inference, someone might say What happens when I update the database manually? whole application crashes. That may happens we are not responsible for illegal access to database. There might be a tons application crash caused by illegal access to our database.