Hi,
while playing around with hibernate and some webframeworks a very basic question arised to me:

Suppose you have a webapplication which supports an great number of customers (let's say up to 200).
The application uses 50 "domain" tables (tables which store customer data) in the backend. All the access and the OR mapping is made via hibernate.

The question is, what might be the best way to organize these tables?

1.) Create a database per customer with all tables in it.
Pros: Very easy to manage, all customers are seperated.
Small tablesizes.
Cons: Connection pools need to be build for every database.
200 customers x 10 connections = 2000 pooled connections?!?

2.) Create one database with all tables in it and link their entries against a customer table.
Pros: One connectionpool for all customers
Cons: Every query needs to link with the customer table.
Customer data is only seperated by logic.

In the moment I would strongly prefer 2.) because of the connectionpool size. What is the best for hibernate (concerning 2nd level cache etc).
Do you have any additional thoughts on it?

Greetings,
Markus

A trivial solution would be to use a NamingStrategy and prefix the table names for each customer. You can then assign a NamingStrategy on a per Hibernate SessionFactory basis. Hm, I guess it doesn't solve all of your problems, but its probably worth thinking about.

This is a very common problem. If you are using Oracle, you can use the Virtual Private Database feature to automate option #2 for you. It will look like you are using private tables, but they will actually be implemented as single tables, and the database will silently append conditions to each query to specify the customer.

See http://otn.oracle.com/oramag/oracle/04-mar/o24tech_security.html

-eric

I have worked for some ASP, they always choose your first choice for security reasons (even if they have thousand of customers) : we all make mistakes (programmer, admin), in a ASP context consequences can be dramatic.

Virtual Private Database from Oracle is a nice choise.

Another possibility that may work (if all databases are on the same server) is to change to the right database using connection.setCatalog(). I think this may cause problems with second-level caching though...

hm. looks like there is no easy solution to this problem.
I would realy prefer to keep the data seperated.

Instead of using oracle I would prefer a solution which is as database independent as possible.

Would it be possible to extend the Sessionfactory to accept multiple Datasources and deliver Sessions(with different DataSources) based on a parameter?

E.g. user A gets a session which points to database A and
user B gets a session which points to database B?

The rest would be totally transparent to the application.
Would this affect the second level cache?
I would love to have it in place for the application.


thanks for all your proposals,
Markus

This is not possible. You can however create one SessionFactory per User.

If we are disabling the second level cache will it work????


Thanks & Regards

unnis

Hi,
Let security implementations deal with security considerations. In an ideal world, there would be a security standard for db-access. There isn't one. Therefore, I see 2 options for you:

1. Design your model with tables shared between customers, and rely on specific database security impl to support your needs, like the mentioned Oracle impl. Security-aware customers will understand why this dependency on Oracle is needed. This means more integration work and less development for your project.

2. Design a security model into your application. For example, M:N access control table, telling which user may access which resource. As you said, this means that security is implemented at app-level and not db-level. This means more development and less integration work for your project.

Since I don't know your project's requirement, I would at least invest some time researching and estimating the effort for both approaches, and then decide.