Different database connection for each user

[timetunnel]

Hello

I have a kind of "standard" stack in order to implement a multi-user web application: a database, Hibernate 3.3.2, Spring 2.5 and JSF. The users have to login into the web application. However, user rights and roles are managed inside the database because data access has to be restricted on this level (it's a prerequisite I can't change). This means that each user has a real database account which must be used by the application for the user's session when the user logs in (and for all subsequent database access for this user as long as he his logged in). The list of users is not static but will grow in the future (likely up to several thousand users).

Now the problem is that the datasource, which is configured as a property of the session factory (AnnotationSessionFactoryBean) in Spring's application context, allows only one database user for the whole application.

Is there any way to achieve my special needs using Spring? I've already found AbstractRoutingDataSource but I'm not sure yet if that's the way to go.

Thanks in advance for your help
Jens

[Marten Deinum]

Now the problem is that the datasource, which is configured as a property of the session factory (AnnotationSessionFactoryBean) in Spring's application context, allows only one database user for the whole application.

No it doesn't. Wrap the DataSource in a UserCredentialsDataSourceAdapter, then create (for instance) a servlet filter which at the beginning calls setCredentialsForCurrentThread and when finished calls removeCredentialsFromCurrentThread. And presto success...

[timetunnel]

No it doesn't. Wrap the DataSource in a UserCredentialsDataSourceAdapter, then create (for instance) a servlet filter which at the beginning calls setCredentialsForCurrentThread and when finished calls removeCredentialsFromCurrentThread. And presto success...

Wow, thanks so much! I hadn't stumbled upon that class before. Spring is so huge, sometimes you just overlook it's all already there...

Jens