Improving performance of security related queries

Hello,

I'm trying to optimize my application. At this stage I'm performing numerous requests to filter entities based on whether a user has access to them. I'd appreciate any help to reduce the number of queries. Here is a simplified class model that represents my entities. Assume I have all the necessary attributes etc to make this usable by LightSpeed.

class Account : Entity<long> {
    List<Member> Membership { get; set; }
 }

class Member: Entity<long>  {
    Account Account { get; set; }
    User User { get; set; }
    Role Role { get; set; }
}

class Role: Entity<long>  {
    string Name { get; set; }
    List<Member> Members { get; set;}
};

class User : Entity<long> {
    List<Member> Memberships { get; set; }
    string Username { get; set; }
}

class Right : Entity<long>  {
    string Name { get; set; }
};

abstract class AccessRule {
    Role Role { get; set; } 
    Right Right { get; set; }
    bool IsAllowed { get; set; }
};

class Entity1AccessRule : AccessRule {
      Entity1 Entity1 { get; set;} 
};

class Entity2AccessRule : AccessRule {
      Entity2 Entity2 { get; set;} 
};

class Entity3AccessRule : AccessRule {
      Entity3 Entity3 { get; set;} 
};

class Entity1 : Entity<long> {
    List<Entity1AccessRule> AccessRules { get; set; } 
    User Owner { get; set; }
    List<Entity2> Entity2s { get; set; }
}

class Entity2 : Entity<long> {
    List<Entity2AccessRule> AccessRules { get; set; }
    User Owner { get; set; }
    Entity1 Entity1 { get; set; }
    List<Entity3> Entity3s { get; set; }
}

class Entity3 : Entity<long> {
    List<Entity3AccessRule> AccessRules { get; set; }
    User Owner { get; set; }
    Entity2 Entity2 { get; set; }
}

In essence, a user can belong to one or more accounts through the role they play in the account. A role can be something like "Administrator", "Member", "Guest". An administrator can define an access role that an "Administrator" has "read", "write", "delete" rights on a specific entity, while a "Member" only has access to "Read" access. This model allows administrators to create their own roles and assign users to those roles, similar to ACL security.

Now when a user requests access to an say an instance of Entity1, I first check to see if the user is the owner, if so, assume they have full access. Then I perform several queries to determine whether the user belongs to a role that has the right to access that instance of Entity1. The method looks similar to this:

public static class UnitOfWorkExtensions {
    public static Entity1 FindEntity1ById(this IUnitOfWork unitOfWork, long id) {

        string username = Thread.CurrentPrincipal.Identity.Name;
        var user = unitOfWork.Query<User>().SingleOrDefault(u => u.Username == userame);
        if (user == null) {
            throw new UnauthorizedAccessException();
        }

        var entity = unitOfWork.FindById<Entity1>(id);
        if (entity == null) {
            throw new NotFoundException();
        }

        if (entity.Owner == user) {
            return entity;
        }

        var right = unitOfWork.Query<Right>().SingleOrDefault(r => r.Name == "Read");
        if (right == null) {
            throw new InvalidOperationException();
        }

        bool hasAccess = false;
        foreach (var rule in entity.AccessRules) {
            foreach (var membership in rule.Role.Members) {
                if (membership.User == user && rule.Right == right) {
                    hasAccess = true;
                    break;
                }
            }

            if (hasAccess) {
                break;
            }
        }

        if (!hasAccess) {
            throw new UnauthorizedAccessException();
        }
        return entity;
    }
}

This results in a number of queries. My gut tels me it can be optimized. If I didn't use LightSpeed, I could have written it as follows:

    internal static Entity1 FindByEntity1Id(this UnitOfWork unitOfWork, long id)
    {
        var username = Thread.CurrentPrincipal.Identity.Name;
        var entity = unitOfWork.Query<Entity1>().SingleOrDefault(e => e.Id == id && (e.Owner.Username == username || e.AccessRules.Any(r => r.Role.Members.Any(membership => membership.User.Username == username && r.Right.Name == "Read"))));
        if (entity == null) {
            throw new UnauthorizedAccessException();
        }

        return entity;
    }

However, LightSpeed reports that "Any" isn't supported in a query like this. Is there a way I can rewrite the query so that it is supported by Lightspeed and to limit the number of requests to database?

Thanks, Werner

Hi Werner,

If I am thinking about this correctly you may be able to express this using joins, e.g. something like

var entity = (
  from e in Entity1s join a in AccessRules on a.EntityId equals e.Id 
  join r in Roles on r.AccessRuleId equals a.Id join m in Members on m.RoleId equals r.Id
  where e.Owner.Username == username && m.User.Username == username && r.Right.Name == "Read" 
  select e
 ).FirstOrDefault();

I suspect some of those look like they might be ThroughAssociations so you would probably have a few more joins involved :)

Another approach you could look at is if the set of access rules is manageable you could look at caching this and then filtering against that client side in your method after you have loaded the unfiltered set of candidates. e.g. Find all AccessRules from the cached set that relate to the loaded entity and then perform the .Any() filtering against that set to validate that at least one access rule would be satisfied.

Jeremy

Thanks Jeremy,

I'll be looking to see whether caching may actually simplify the query and improve its performance.