I need some assistance with designing a system which implements row-level security that could have multiple conditions that need to be satisfied.ContextSuppose I have a user. A user can belong to 1...n organizations, 1...n departments, and 1...n positions.Maybe related/useful: These tables follow a hierarchical flow where departments belong to 1...n organizations, and positions belong to 1...n departments. My use caseSuppose I have a document entity. Only those usergroups that satisfy the associated visibility rule for this document can access it. Examples (indented lists are possible examples of narrowing the defined rule for the document)only users in organization X.in organization X, Department 1in organization X, Department 1, Position Ain organization X, Position Aonly users in Department 1in Department 1, Position Aonly users in Position AA document will have a list of these rules. Therefore any combination of the above is possible, but as long as any rule is satisfied, then the user is granted access.My current implementation includes creating this table that includes creating these rules where the 3 categories are nullable/optional fields.In the following example, assume i have a document with Id 1. So only users in organization X would have a row that looked like {DocumentId: 1, Organization X}. Another example in organization X, Position A would look like {DocumentId: 1, Position A, Organization X}To Check access, I would have to grab a list of rules for the specified document. If a rule has all three properties specified, then the user must belong to all three properties. But this grows in complexity because a document could have multiple rules with the only condition that the user must satisfy one of these rules. So that same document could have a rule where only 1 property is specified. This implementation generates a ton of if statements How would I go about implementing this design in a cleaner way? TIA.
Row level security with multiple rules
c#;database design;asp.net;entity framework;authorization
I think you're going down the right path.  I'm not sure I'd use nullable/optional fields though.  In my mind, NULL means unknown, not ANY.Using your example data you could create a tableDocID    Org    Dept    Posn  1       X      1       any  1       X      1        A     // redundant, due to the record above  1       X     any       A  1      any     1        A  1      any    any       AFor any given user, the test would beselect count(*) from DocPermswhere DocID = @docID    and (Org = @myOrg or Org = 'any')    and (Dept = @myDept or Dept = 'any')    and (Posn = @myPosn or Posn = 'any')If that count is greater than zero, the user has access.Note that we don't necessarily have to do this in SQL.  You might want to do it in code instead:(Per your comment, this now assumes the user may be in more than one org/dept/posn, whereas the code above assumes the user is in exactly one)var docRules = allDocRules.where(e => e.DocID == docID);  // get the set of rules for this documentvar firstApplicableRule = docRules.FirstOrDefault(e => MatchRules(user, e));bool userhasAccess = (firstApplicableRule != null);....private bool MatchRules(Person u, Rule r){    // return true if any of u's roles apply to the specific rule    bool result = false;    foreach (Role theRole in u.Roles)     {         if ((theRole.Org == r.Org || r.Org == any)            and (theRule.Dept == r.Dept || r.Dept == any)            and (theRule.Posn == r.Posn || r.Posn == any))         {             result = true;             break;         }    }    return result;}If you do this in code then you can add Booleans for the 'any' fields:r.IsDeptWildCardIf you're doing this in the database, you can use an out of range value (-1) or link to a special any record (not the best design, I'm sure someone will propose a better one)