Permission system LINQ Query with 3 Hierarchies

This is slightly difficult for me to explain in writing and I appreciate any of you that take the time to read this long question. I am open to not using a LINQ query to solve this and just doing some type of looping or recursion if that works better.

I am working on a permission/authorization system that is somewhat based off of NETSqlAzMan.

These are the main concepts:

• Permissions are rights that can be authorized for two given "securables".
• "Securable" is an object to which both authorizations can be made (Think user or user group here), and the objects they can be made for (think a file or document). Example: User1 and File1 are both "securables" and User1 can be authorized to edit File1.
• "Securables" can have parent and children "securables".
• "Permissions" can have parent and/or children permissions.

This is a more concrete example of how this works.

Securables:

- Administrators  
    - Tom  
    - Mike  
- Editors  
    - John  
    - Lisa  
- Documents  
    - Document1  
    - Document2  

Permissions:

• Edit_Document
  • Edit_Title
  • Edit_Body
  • Change_BGColor

The above data is stored in 2 tables, Securable and Permission with 2 intermediate tables, GroupMembership (Securable hierarchy) and PermissionRelationship (Permission Hierarchy).

There is also a table called Authorization which holds the authorizations that are made up of one Securable as "AuthorizedID", one Securable as "SecurableID", one Permission as "PermissionID", and one Enum that can either be Inherit(0), Allow(1), or Deny(2).

I have LINQ2SQL Entities setup for all tables as well as a function that returns all of a given Securable's ancestors and one that returns all of a given Permission's ancestors.

I am struggling to determine a query that properly checks all 3 hierarchies for each permission to determine the effective permission and the nearest level of authorizedID that the authorization is set on for a given AuthorizedID and SecurableID.

For example: If Administrators are granted "Allow" on "Documents" for the "Edit_Document" permission, then the query for the given AuthorizedID of "Administrators" and SecurableiD of "Documents" should return:

• AuthorizedID:Administrators SecurableID:Documents Allow Permission:Edit_Document
• AuthorizedID:Administrators SecurableID:Documents Allow Permission:Edit_Title
• AuthorizedID:Administrators SecurableID:Documents Allow Permission:Edit_Body
• AuthorizedID:Administrators SecurableID:Documents Allow Permission:Change_BGColor

If additionally an authorization is added for "Mike" on "Document1" with Deny "Change_BGColor" (Deny overrides any allow further down in a chain), then the result of the query for AuthorizedID of "Mike" and SecurableID of "Document1" would return:

• AuthorizedID:Administrators SecurableID:Documents Allow Permission:Edit_Document
• AuthorizedID:Administrators SecurableID:Documents Allow Permission:Edit_Title
• AuthorizedID:Administrators SecurableID:Documents Allow Permission:Edit_Body
• AuthorizedID:Mike SecurableID:Document1 Deny Permission:Change_BGColor

This is what I have so far that handles the hierarchy of AuthorizedID and SecurableID fine but doesn't take into consideration the hierarchy of Permissions properly. I'm not sure how to have the permission hierarchy taken into account so that if one of the permission's ancestors are set to deny then the child permission also shows deny and the result includes the AuthorizedID and SecurableID for which the deny is set.

public List<LocalPermission> GetSecurablesLocalPermissions(Guid authorizedID, Guid securableID)
    {
        var localAuthorization = (from eff in
                                       (from p in dc.Permissions
                                        let a = (from sa in dc.AllSecurablesAuthorizations(authorizedID)
                                                 where sa.PermissionID == p.PermissionID
                                                 group sa by sa.PermissionID into g
                                                 select g.OrderBy(x => x.Lvl).OrderByDescending(x => x.AuthorizationBits).First()).FirstOrDefault()
                                        select new
                                        {
                                            PermissionID = p.PermissionID,
                                            PermissionName = p.PermissionName,
                                            AuthorizationBits = a.AuthorizationBits ?? 0,
                                            SecurableID = a.SecurableID ?? Guid.Empty,
                                            SecurableName = a.SecurableName,
                                            AuthorizedName = a.AuthorizedName,
                                            AuthorizedID = a.AuthorizedID ?? Guid.Empty
                                        })
                                   join l in
                                       (from p in dc.Permissions
                                        join la in dc.Authorizations
                                           on p.PermissionID equals la.PermissionID into laJoin
                                        from localJoin in laJoin.Where(x => (x.SecurableID == securableID /*|| localJoin.SecurableID == Guid.Empty*/) && (x.AuthorizedID == authorizedID /*|| localJoin.AuthorizedID == Guid.Empty*/)).DefaultIfEmpty()
                                        select new
                                                   {
                                                       PermissionID = p.PermissionID,
                                                       PermissionName = p.PermissionName,
                                                       AuthorizationBits = localJoin == null ? 0 : localJoin.AuthorizationBits,
                                                       SecurableID = localJoin == null ? Guid.Empty : localJoin.SecurableID,
                                                       SecurableName = localJoin == null ? null : localJoin.SecurableName,
                                                       AuthorizedName = localJoin == null ? null : localJoin.AuthorizedName,
                                                       AuthorizedID = localJoin == null ? Guid.Empty : localJoin.AuthorizedID
                                                   }) on eff.PermissionID equals l.PermissionID
                                   select new LocalPermission
                                   {
                                       PermissionID = l.PermissionID,
                                       PermissionName = l.PermissionName,
                                       AuthorizationBits = l.AuthorizationBits,
                                       SecurableID = securableID,
        开发者_如何学Go                               SecurableName = (from s in dc.Securables
                                                        where s.SecurableID == securableID
                                                        select s).SingleOrDefault().SecurableName,
                                       AuthorizedID = authorizedID,
                                       AuthorizedName = (from s in dc.Securables
                                                         where s.SecurableID == authorizedID
                                                         select s).SingleOrDefault().SecurableName,
                                       EffectiveAuthorizationBits = eff.AuthorizationBits == 0 ? l.AuthorizationBits : eff.AuthorizationBits
                                   }).ToList<LocalPermission>();

        return localAuthorization;
    }

This is so nasty.

Answer: "so that if one of the permission's ancestors are set to deny then the child permission also shows deny" this means that you need recursion. Parent/Child relationships in a database often mean recursion. linq does not support recursion but native sql does (common table expressions).

My strategy would be to pull all permissions into a list and then write ordinary procedural recursive code to answer your query (which I did not fully comprehend). I recommend TDD in this case. Hope this helps to get you on track.