Schema based multitenancy with SQLServer and Hibernate

Question

I am interested at implementing a multitenancy solution using hibernate as provider and SQLServer as database. I am using the schema based approach meaning one database and different schemas.

Specifically, my question is how to alter the execution schema with SQLServer. I have used this approach with MySQL use $database, PostgreSQL SET search_path TO $schema and Oracle ALTER SESSION SET CURRENT_SCHEMA = $schema and i was able to successfully switch from one schema to an other, noting that the schema concept differs from one database to another.

However, with SQLServer, I understand that there's no such thing as altering execution schema. I am aware of the possibility of switching the default schema of a given user in case the user is not granted the sysadmin role. I am also aware of the impersonation concept using the folllwing query : EXECUTE AS USER = $user. Impersonation allows one user to impersonate an other user, which gives the possibility to change the current user.

Switching the current user allows by definition to switch the execution schema since once the user is changed the execution schema is the new user's default schema. However, the impersonation has a limitation since we can't perform impersonation over 32 times.Although it's possible to execute revert after each impersonation in order to avoid reaching the limit impersonation number, this solution is not suitable in my case and I'm looking for an alternative.

Does anyone have any suggestions about multitenancy implementation using SQLServer and a schema based approach?

Also is there any solutions other than the ones I mentioned to switch execution schema.

Your help is very much appreciated.Thank you so much.

Answer 1

The following are the points for consideration

1. The SQL Server differentiates the schema's by the prefix in the tables like [dbo].[Users] and [Tenant01].[Users]
2. Since you have not mentioned the way in which you resolve the database connections for each schema in SQL Server, I would like to suggest that you can take a look at Azure Shard Map which supports your use-case or build your own in which case you would pickup the connection string from a centralized store based on the Tenant Context established. For ex: Tenant01 will be mapped to a ConnectionString C01 which will have the same database but with a different userid and password. This approach is the one that is more commonly followed. However you can also opt for Azure Shard Map which does this mapping behind the scenes for you.
3. In this case, when you provision a schema, you also create a SQL User or map an Azure AD User to the schema and grant the necessary permissions on the Schema. This ensures that the access is fine.

The above option of persisting the tenant-wise connection strings in a store will be helpful in case you plan to scale out a specific tenant from the Shared DB due to considerable volume of data growth so as to offer higher response times and also better performance for the rest of the Tenants.

More on Azure Shard Map

EDIT

Actually, when you have a user mapped to a schema in SQL Server, you don't need to use the [Schema].[Table] in the Query, instead you can directly use the [Table], the access happens automatically. You can actually map the user to the schema using likeALTER USER erpadmin WITH DEFAULT_SCHEMA = erpadmin; from then on, the queries does not require the schema prefix for the table. More details here

HTH