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.