Row level security with multiple rules

Question

I need some assistance with designing a system which implements row-level security that could have multiple conditions that need to be satisfied.

Context

Suppose 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 case

Suppose 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 1
  • in organization X, Department 1, Position A
  • in organization X, Position A
• only users in Department 1
  • in Department 1, Position A
• only users in Position A

A 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.

Answer 1

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 table

DocID 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 A

For any given user, the test would be

select count(*) from DocPerms
where 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 document
var 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.IsDeptWildCard

If 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)

• alexhuang: Hey thanks for the response! If I were to avoid NULL, then how would go about storing a value that signifies ANY in SQL? 0? < 0 values? Edit: to clarify I would prefer my type for all three columns to be ints
• alexhuang: A user also belongs to a list of orgs, departments, positions. So is there a way to write a query that checks all the lists instead of a separate select for each one?