Hi everybody!
Probably this question has been discussed earlier, but I could not find anything...

The problem is that I have a number of Oracle users, which have some associated Oracle roles, so I want db server to take care about user login authentication and user's permissions.

All examples contain just fixed user name and password. But how to configure Hibernate to use db user name and passwords in runtime when application is running? And what happens to connection cache in that case?

Thanks in advance.

M. Alexeev

That kind of configuration defeats the whole point of connection pooling, which is to have n connections shared among m users (n < m) to reduce the overhead of the extra m-n database sessions and the network traffic involved with establishing a JDBC connection.

That being said, you probably want to modify HibernateUtil to use the SessionFactory.openSession( Connection ) method instead of SessionFactory.openSession( ). The former will connect the session using a user-supplied connection. So, when a user logs on, you would establish a connection, associate that connection with the user somehow (an HttpSession attribute comes to mind), and then pass that connection to HibernateUtil every time you want to create a session (change the signature of HibernateUtil.currentSession( ) to HibernateUtil.currentSession( Connection )).

Another solution would be to set up a connection pool using a privileged user account. Let Hibernate get its connections automatically from that pool and make no modifications to HibernateUtil or hibernate.cfg.xml. When the user authenticates, open a new JDBC connection using the supplied username and password to perform the authentication and then immediately close that connection once the user has authenticated. In other words, open a standalone JDBC connection with different credentials for the purposes of authentication, and then discard it after that. Then open a Hibernate session and proceed as normal.

The only problem with the latter is that you won't be able to rely on the database for table/column security. Some legacy systems use the database as an authorization point rather than implementing security at the application level. The feasibility of the pool solution depends on the needs of your application.

- Jesse