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.

Lindsay Mathieson
  • 11
  • 2

1 Answers1

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.

J.D.
  • 40,776
  • 12
  • 62
  • 141