How to use database auditing, database security, proxy authentication and VPD
JPA and EclipseLink are typically used in a mid tier/server environment with a shared connection pool. A connection pool allows database connections to be shared to avoid the cost of reconnecting to the database. Typically the user logs into the application, but does not have their own database login, as a shared login is used for the connection pool. This is a different model than traditional two tier applications where each user had their own database login. Most databases provide auditing support to log changes and provide user based security. However in a three tier environment, with a shared connection pool, and web users, this does not typically work.
There are several solutions to auditing and security:
- Provide each application user with a database user id, and provide each user with their own database connection.
- Use a common database user id, and manage auditing and security in the application.
- Use Oracle proxy authentication to allow a shared connection pool and a user context.
Provide each application user with a database user id
This allows for database user based auditing and security. If each application user has their own database user id, then connections cannot be shared. Each user will need to create a new database connection when they create their EntityManager.
EclipseLink provides support for this model, by allowing the JPA persistence unit properties to be passed to the EntityManagerFactory.createEntityManager(Map) API. The application can pass the "javax.persistence.jdbc.user" and "javax.persistence.jdbc.password" properties to trigger a new connection to be created for this EntityManager. Note that this connection will only be used for writing by default, reads will still use the shared connection pool. To force reads to also use the connection the "eclipselink.jdbc.exclusive-connection.mode" property should be set to "Always", but this depends on if the application wishes to audit writes, or reads as well. EclipseLink also defines a "eclipselink.jdbc.exclusive-connection.is-lazy" property that configure if the connection should be connected up front, or only when first required. If only writes are audited, then lazy connections allow for the cost of creating a new database connection to be avoided unless a write occurs. By default EclipseLink also has a shared connection pool, if both reads and writes are using the user connection, it should be disabled by setting the size to 0. EclipseLink will still require a single connection and a valid user to validate the database connection, but this will only be used once at start-up and then disconnected.
Example EntityManager using a user connection
To audit only writes:
Map properties = new HashMap(); properties.put("javax.persistence.jdbc.user", user); properties.put("javax.persistence.jdbc.password", password); properties.put("eclipselink.jdbc.exclusive-connection.mode", "Transactional"); properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "true"); EntityManager em = factory.createEntityManager(properties);
To audit reads and writes:
Map properties = new HashMap(); properties.put("javax.persistence.jdbc.user", user); properties.put("javax.persistence.jdbc.password", password); properties.put("eclipselink.jdbc.exclusive-connection.mode", "Always"); properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "false"); EntityManager em = factory.createEntityManager(properties);
Example persistence.xml with no connection pool
To disable connection pooling when auditing reads and writes:
<persistence-unit name="default" transaction-type="RESOURCE_LOCAL"> <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> <properties> <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/> <property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@oracle:1521:orcl"/> <property name="javax.persistence.jdbc.user" value="guest"/> <property name="javax.persistence.jdbc.password" value="welcome"/> <property name="eclipselink.jdbc.connections.initial" value="0"/> <property name="eclipselink.jdbc.connections.min" value="0"/> <property name="eclipselink.jdbc.connections.max" value="1"/> </properties> </persistence-unit>
Java EE and JTA
Caching and security
Use a common database user id, and manage auditing and security in the application
Auditing is normally managed in the application by having an application user, and a single shared database user. This is typically implemented by adding a AUDIT_USER and AUDIT_TIMESTAMP column to all of the audited tables and auditUser and auditTimestamp field to all of the audited objects. When the application inserts or updates an object, it will set these fields and they will be stored in the database. JPA, or EclipseLink events could also be used to record the audit information, or to write to a separate audit table.
EclipseLink also support full history support, which allows a complete history of all changes made to the database to be tracked in a mirror history table.
Security is also controlled by the application, allow the user access to different part of the application based on their application user id or role. The database can be used to store the user login information, roles and access privileges, but these will just be in normal tables, and not have any relation to the database users, and the database will not enforce its own security.
This model allows for full connection pooling, and gives the application control over auditing and security.