Managing multiple databases or schemas
Customers commonly prefer to segregate client data into multiple database schemas while still leveraging the benefits of Hibernate (or EJB3). Reasons for this vary, but it typically boils down to a legal or contractual obligation to secure user-specific data with non-shared database credentials.
There is no single right or wrong solution to this problem, so we must consider several factors: Number of schemas, expected concurrency, caching and the amount of [perceived] security desired. The most important thing to remember is that the second level cache is completely unaware of these tactics so you must not use it for data stored across user-specific schemas.
Here are some possible ways to accomplish this:
Deal with filtering the data in the application
This method is really not a viable solution to the problem at hand, but it bears mentioning. Filtering data by client/user in the application is the most common way of handling this concern.
Pros: Extremely simple and probably the natural solution for most developers.
Cons: Does not actually separate user data at the database level.
Manually alter all SQL to use qualified schema names
Hibernate's Interceptor gives you access to every statement preparation (through onPrepareStatement). With a hook to this part of the process, SQL can be modified in almost any way (for better or worse).
Pros: Transparent; easy to understand.
Cons: Rather undesirable to do String manipulation on hundreds or thousands of SQL statements; application still uses a superuser to log in to the database, so mistakes can happen.
Use proprietary database security (Oracle VPD)
Oracle provides for row-level security which we can leverage to filter data.
Pros: Amenable to Oracle DBAs.
Cons: Requires Oracle.
Manage a separate connection (or pool of connections) for each specific database user
By implementing ConnectionProvider, you can manage JDBC connections in any way you see fit. Perhaps a user-keyed Map of DataSources.
Pros: Reasonably secure since the database is only ever accessed within the privilege of one user.
Cons: Does not scale beyond a few users/schemas unless certain provisions can be met.
Hibernate Shards
Hibernate Shards is a subproject developed and donated by Google. It allows the management of several SessionFactory instances across different databases (or users), optionally created from a "prototype" configuration.
Pros: A pre-made solution.
Cons: Not appropriate for large numbers of schemas since there is effectively one SessionFactory per database user/schema.
