1

I have set up a classic SQL Server Multi Tenant DB, Shared Database, Shared Schema, will be cloud hosted in Azure. All access to the DB is via a minimal api.

Every row has a tenantId, with a SQL Server security policy setup so tenants can only see data for their tenantId. This all works perfectly.

Each tenant has multiple users, with each user having multiple jobs. I thought it would be a good idea to prevent the possibility of users getting other users Jobs by setting up an additional policy filter by (tenantId, userId) on the jobs table. Also works perfectly, but I have to wonder if this is overkill and better handled in the API layer.

Thanks.

nb. Block Policy is applied as well.

1
  • Note that it's generally better (especially in Azure) to break out tenants into their own databases. But if you do have a multi-tenant database RLS is useful to help enforce tenant data isolation. Note that there are performance costs to RLS and to having a multi-tenant schema. And the only cost in Azure to having database-per-tenant is the automation necessary to manage it. Commented Jul 14, 2023 at 21:17

1 Answer 1

1

I'll preface this by saying Row-Level Security works pretty well but isn't a perfect security solution on its own when security is critical, as there are ways to work around it. In your full topology you should use redundancy when it comes to security, such as additional measures in your API layer.

I thought it would be a good idea to prevent the possibility of users getting other users Jobs by setting up an additional policy filter by (tenantId, userId) on the jobs table.

Yes, this is a good use case for Row-Level Security. It is not overkill in addition to your other policies on just the tenantId too. In fact, it's how some ERP systems operate to segregate which data certain users can see, even within the same organization.

Just be mindful to test the performance of your queries after implementing any Row-Level Security policies, since they get applied as additional predicates to the query, and affect the execution plan of those queries. This will be directly dependent on how well the function your security policy utilizes is coded and tuned.

5
  • Thanks, have a fair few paranoia checks in the API as well. Now I'm on the fence as to whether I should use RLS at all. Of course I'm confident that I would never make a mistake in the API code /s :) But there will be other maintainers. And it may be an advantage to reassure customers Admins that their data is isolated at the DB level. Commented Jul 15, 2023 at 5:14
  • @LindsayMathieson Np! To be honest, no single solution is perfect with security, no matter how well thought out it feels. If security is very critical, then I'd recommend doing both. Redundancy is generally a good thing with security. I use RLS in production applications.
    – J.D.
    Commented Jul 15, 2023 at 13:32
  • 1
    When you are storing multiple tenants' data in a single database, you need assure your customers (and prospective customers) that one tenant can't read another tenant's data. "We use separate identities for each tenant and row-level security" is a better answer than "our code takes care of that". And "Each tenant has a separate database" is an even better answer. Commented Jul 15, 2023 at 21:37
  • 1
    @DavidBrowne-Microsoft Yes agreed, especially with breaking out tenants by database. Improved security for multiple reasons, besides improved manageability and potentially performance.
    – J.D.
    Commented Jul 16, 2023 at 3:24
  • @LindsayMathieson You need to be aware that RLS can be circumvented by a malicious actor. It is subject to a lot of side-channel attacks, even when only accessed via an API. Eg someone could repeatedly try usernames and passwords for a tenant they don't own, and based on the response times could glean info on what is there. If particular errors could be triggered based on the data (divide by zero, string truncation etc) that is even worse. So it's not really a security solution, more of a convenience solution to filter rows in bulk based on users. Commented Jul 17, 2023 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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