EclipseLink/Examples/JPA/Auditing

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>