Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: Schema based multitenancy with Hibernate and SQLServer
PostPosted: Thu Sep 14, 2017 5:22 am 
Newbie

Joined: Thu Sep 14, 2017 5:10 am
Posts: 3
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
Code:
use $database
, PostgreSQL
Code:
SET search_path TO $schema
and Oracle
Code:
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 :
Code:
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 wih SQLServer.

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


Top
 Profile  
 
 Post subject: Re: Schema based multitenancy with Hibernate and SQLServer
PostPosted: Thu Sep 14, 2017 11:08 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1439
What if you have multiple DataSources and the tenantId is used to pick one or the other. This is similar to these examples I added the Hibernate documentation folder.

So, instead of choosing to switch the schema, the Connection is picked from the right DataSource.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Schema based multitenancy with Hibernate and SQLServer
PostPosted: Thu Sep 14, 2017 11:42 am 
Newbie

Joined: Thu Sep 14, 2017 5:10 am
Posts: 3
Thank you so much for the time you have taken in order to reply to me.
However, this solution does not solve my problem since the situation is the following : one datasource, multiple schemas.
As I mentionned, I'm implementing the Multitenancy schema based approach.
I would be extremely appreciative if you have any suggestions about specifically switching the schema and sticking with the same approach.
Thank you.

Best Regards


Top
 Profile  
 
 Post subject: Re: Schema based multitenancy with Hibernate and SQLServer
PostPosted: Thu Sep 14, 2017 1:35 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1439
Unfortunately, I'm not an expert on SQL Server, so if changing the current schema is not allowed, the only way to make it work is if you define N DataSources for all the N tenants.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Schema based multitenancy with Hibernate and SQLServer
PostPosted: Fri Sep 15, 2017 7:06 am 
Newbie

Joined: Thu Sep 14, 2017 5:10 am
Posts: 3
I agree. Thank you so much.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.