We have a fairly complex Meetings Management solution which would have one meeting to contain different agendas which would contain some tasks and also documents, etc. There are also different roles such as agenda creator, agenda reviewer, agenda approver, user responsible for actions etc. We could have participants at the Meetings level or at agenda level or at action level.
Depending on the user's role and permission level, the user should only see contents pertaining to his own permission and responsibility. However there are some catches as well, such as if a user is responsible for the action which is contained under some agenda, he should see his action and only see agenda title, however he could not click into see any detailed about that agenda. What end user sees at the page could vary depending on the Meeting status (open or completed, etc.) and some other status tracking.
Right now, the query for security trimming is very complex by joining multiple tables at application code. It worked well with reasonable good performance. The downside of this is that we would need to make sure that we would have the same code for handling this security trimming for any point of access. We thought about DB security trimming as well with Row Level Security, however based on what I saw, the row level security is more for security trimming based on data segregation with some fixed rules such as:
- sales could only access records of his own territory.
- nurse could only access records of his own patients
- user at one department could only access records of his own department
Plus the security policy could only applied on one table. Could we have security policy predicate to be evaluated from multiple tables? Here is the example at above link:
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) ON dbo.Sales WITH (STATE = ON);
At this point, I could not think of how to map our complex security trimming to DB security trimming. I am a little bit concerned about debugging as well in case that we have any security trimming issue if we push all these to DB. Could any expert shed some lights here?
Plus we are using Entity Framework, not sure how well Entity framework would work with this complex row level security yet.