2

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:

  1. sales could only access records of his own territory.
  2. nurse could only access records of his own patients
  3. 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.

  • 1
    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. -- Create a common C# library that could be used at all security endpoints? – Robert Harvey Dec 16 '19 at 21:31
  • 1
    Excellent question! Can you tell us how "secret" the records need to be? For example, lets say the application filters out some items, but how bad would it be if somewhere else on a random SSRS report (which reads straight from the dbase) the 'hidden' items were not truncated? If you can live with that, then I'd leave things as-is. If you can't, then you need to force all report-rendering to flow through a code API, which does the complex trimming, and then you can't offer out-of-the-box reporting straight against the dbase. – Graham Dec 16 '19 at 21:56
  • Thanks @RobertHarvey, yep, a common library or API end point would definitely help and the easy way to go. I guess I still need to convince people that DB security trimming is not suitable for the complex requirements here per my understanding, Do you concur with that point? – windfly2006 Dec 17 '19 at 16:29
  • Thanks @Graham. The data is quite "secret". At this point, we don't have any reporting directly from DB yet although I hear your points. At this point, the access to DB is locked down and most probably reporting would be done via our application which would go through the Code API. – windfly2006 Dec 17 '19 at 16:36
  • 1
    I guess I still need to convince people that DB security trimming is not suitable for the complex requirements here per my understanding -- The logic in your question seems reasonable to me. – Robert Harvey Dec 17 '19 at 16:46
2

I doubt the MS SQL row-level security is fine-grained and flexible enough to handle all your use cases. For example, just seeing the title but not description. Also, SQL doesn't really have a comprehensive toolkit for RBAC and authentication. It's a long way from JWT claims to that row-level security.

On other hand, you have to push down some security functions down to the database. For example, you will need a reasonable function to load the meetings the user has access to, otherwise you'd have to load the whole table to memory.

I'd adopt as design principle to treat the database as an admin level access object store, avoiding business logic as much as possible. Approval of direct database access would then require the same approval as other business confidential or PII information.

Entity framework can help you with callbacks.

| improve this answer | |
  • Thanks. As for "you have to push down some security functions down to the database", we are already doing so with writing majority of those through SQL statement while in some cases we do the trimming at the code as well. Basically combination of two approaches. – windfly2006 Dec 18 '19 at 18:13
  • Unfortunately I have never seen a relational database that allows a clean expression of tenancy. I've done table-based tenancy, with every table having a tenantId and that was the first layer of security. Unfortuately that's a method that fails open, you have to include tenantId in every query and if you forget, it will leak information. With some databases, database-per-tenant or schema-per-tenant is possible. – Martin K Dec 18 '19 at 21:37
  • Yep, you are absolutely right here. Right now we are using database-per-tenant. Actually our software is on premises, however there are different groups for meetings and their data could not be mixed up. so we create different database for those different groups ( aka tenant here). My question is more about security trimming inside one tenant. – windfly2006 Dec 18 '19 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.