Accessing The Database Via Jpa With The Enduser's Credentials In Java Ee

I am working on a JavaEE project with WildFly, Hibernate (JPA), C3P0 and a MS SQL Server database. The database is a legacy database with more than a thousand Database Objects li

Solution 1:

The 'standard' way in JavaEE is to have an application user for accessing the database and achieve user-based access-rights through roles in your application.

If you need to rely on existing database user, your only chance is to establish a connection per user or per request. The entity manager would then indeed consume some memory as it's designed to be your application's persistence context (and thus some sort of cache).

Maybe you can have some trade-off here by accessing those parts which really have to be user-based by plain JDBC and potential execution of database procedures and others by some application user with extended rights which ensures the necessary user restrictions programmatically.