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>

Java EE and JTA

If using Java EE and a DataSource the database user name and password can be passed in the same way. EclipseLink will just pass the user/password to the DataSource when creating a connection instead of directly to JDBC.

If a JEE and JTA managed EntityManager is used, specifying a user/password can be more difficult, as the EntityManager and JDBC connection is not under the applications control. The persistence unit properties can still be specified on the EntityManager. As long as this is done before the EntityManager has established a database connection, this will still work.

Example of setting user/password on JEE EntityManager

If using JPA 2.0, the setProperty API can be used:

em.setProperty("javax.persistence.jdbc.user", user);
em.setProperty("javax.persistence.jdbc.password", password);
em.setProperty("eclipselink.jdbc.exclusive-connection.mode", "Always");
em.setProperty("eclipselink.jdbc.exclusive-connection.is-lazy", "false");

Otherwise, the getDelegate API can be used:

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");
((org.eclipse.persistence.internal.jpa.EntityManagerImpl)em.getDelegate()).setProperties(properties);

Caching and security

By default EclipseLink maintains a shared (L2) object cache. This is fine for auditing, but if user based security is used to prevent the reading of certain tables/classes, then the cache may need to be disabled for these secure classes. To disable the shared cache for a class see, Caching.

If the database user is used to check security for reads, then the "eclipselink.jdbc.exclusive-connection.mode" can be set to "Isolated" to only use the user connection for reads for the classes whose shared cache has been disabled (isolated).

Roles

Normally each user is not allocated different security privileges, but instead a set of roles are defined that are allocated security privileges and the users are assigned to a role. If you create a single application database user for each role, then you can have multiple security levels, but still allow for connection pooling.

One way to enable this in JPA is to define a different connection pool and persistence unit for each role. This will allow connection pooling and handle the role based database security, but has issues with sharing a cache between the roles.

EclipseLink allows for a single persistence unit to define multiple connection pools. A ConnectionPolicy can then be passed when creating an EntityManager to choose the connection pool to be used using the entity manager property "eclipselink.jdbc.connection-policy".

Multiple connection pools can be defined in EclipseLink using a SessionCustomizer and the ConnectionPool class.

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.

Example AuditedObject class

import javax.persistence.*;
 
@MappedSuperclass
public Class AuditedObject {
  public static ThreadLocal currentUser = new ThreadLocal();
 
  @Column("AUDIT_USER");
  protected String auditUser;
  @Column("AUDIT_TIMESTAMP");
  protected Calendar auditTimestamp;
 
  public String getAuditUser() {
    return auditUser;
  }
 
  public void setAuditUser(String auditUser) {
    this.auditUser = auditUser;
  }
 
  public Calendar getAuditTimestamp() {
    return auditTimestamp;
  }
 
  public void setAuditTimestamp(Calendar auditTimestamp) {
    this.auditTimestamp= auditTimestamp;
  }
 
  @PrePersist
  @PreUpdate
  public void updateAuditInfo() {
    setAuditUser((String)AuditedObject.currentUser.get());
    setAuditTimestamp(Calendar.getInstance());
  }
 
}

Using EclipseLink events, you can also enable application auditing without requiring adding a common superclass or extra fields to your objects. The DescriptorEvent aboutToInsert and aboutToUpdate can be used to add columns to the database write. Descriptor event can be registered for using the DescriptorEventListener interface or DescriptorEventAdapter class, and the JPA @EntityListeners annotation or xml element. They can also be registered using a DescriptorCustomizer or SessionCustomizer.

import java.util.*;
import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.descriptors.DescriptorEventAdapter;
import org.eclipse.persistence.descriptors.DescriptorEvent;
import org.eclipse.persistence.config.DescriptorCustomizer;
import org.eclipse.persistence.config.SessionCustomizer;
 
public Class AuditListener extends DescriptorEventAdapter implements SessionCustomizer, DescriptorCustomizer {
  public static ThreadLocal currentUser = new ThreadLocal();
 
  /** This will audit a specific class. */
  public void customize(ClassDescriptor descriptor) {
    descriptor.getEventManager().addListener(this);
  }
 
  /** This will audit all classes. */
  public void customize(Session session) {
    for (ClassDescriptor descriptor : session.getDescriptors().values()) {
      customize(descriptor);
    }
  }
 
  public void aboutToInsert(DescriptorEvent event) {
    for (String table : (List<String>)event.getDescriptor().getTableNames()) {
      event.getRecord().put(table + ".AUDIT_USER", (String)AuditListener.currentUser.get());
      event.getRecord().put(table + ".AUDIT_TIMESTAMP", Calendar.getInstance());
    }
  }
 
  public void aboutToUpdate(DescriptorEvent event) {
    for (String table : (List<String>)event.getDescriptor().getTableNames()) {
      event.getRecord().put(table + ".AUDIT_USER", (String)AuditListener.currentUser.get());
      event.getRecord().put(table + ".AUDIT_TIMESTAMP", Calendar.getInstance());
    }
  }
 
}

Use Oracle proxy authentication to allow a shared connection pool and a user context

The Oracle database provides a mechanism to set a proxy user on an existing database connection. This allows for a shared connection pool to be used, but to also gives the database a user context.

EclipseLink provides support for Oracle proxy authentication through allowing the following persistence unit properties to be specified on an EntityManager:

• "eclipselink.oracle.proxy-type" : oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME, PROXYTYPE_CERTIFICATE, PROXYTYPE_DISTINGUISHED_NAME
• oracle.jdbc.OracleConnection.PROXY_USER_NAME : <user_name>
• oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD : <password>
• oracle.jdbc.OracleConnection.PROXY_DISTINGUISHED_NAME
• oracle.jdbc.OracleConnection.PROXY_CERTIFICATE
• oracle.jdbc.OracleConnection.PROXY_ROLES

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.

Example EntityManager using proxy authentication

To audit only writes:

Map properties = new HashMap();
properties.put("eclipselink.oracle.proxy-type", oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_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("eclipselink.oracle.proxy-type", oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password);
properties.put("eclipselink.jdbc.exclusive-connection.mode", "Always");
properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "false");
EntityManager em = factory.createEntityManager(properties);

Java EE and JTA

If a JEE and JTA managed EntityManager is used, specifying a proxy user/password can be more difficult, as the EntityManager and JDBC connection is not under the applications control. The persistence unit properties can still be specified on the EntityManager. As long as this is done before the EntityManager has established a database connection, this will still work.

Example of setting user/password on JEE EntityManager

If using JPA 2.0, the setProperty API can be used:

em.setProperty("eclipselink.oracle.proxy-type", oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME);
em.setProperty(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user);
em.setProperty(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password);
em.setProperty("eclipselink.jdbc.exclusive-connection.mode", "Always");
em.setProperty("eclipselink.jdbc.exclusive-connection.is-lazy", "false");

Otherwise, the getDelegate API can be used:

Map properties = new HashMap();
properties.put("eclipselink.oracle.proxy-type", oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user);
properties.put(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password);
properties.put("eclipselink.jdbc.exclusive-connection.mode", "Always");
properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "false");
((org.eclipse.persistence.internal.jpa.EntityManagerImpl)em.getDelegate()).setProperties(properties);

Caching and security

By default EclipseLink maintains a shared (L2) object cache. This is fine for auditing, but if VPD or user based security is used to prevent the reading of certain tables/classes, then the cache may need to be disabled for these secure classes. To disable the shared cache for a class see, Caching.

If the database user is used to check security for reads, then the "eclipselink.jdbc.exclusive-connection.mode" can be set to "Isolated" to only use the user connection for reads for the classes whose shared cache has been disabled (isolated).

VPD

The Oracle Virtual Private Database feature allows for row level security within the Oracle database. Typical database security only allows access privileges to be assign per table. Row level security allows different users to have access to different rows within each table.

EclipseLink's Oracle proxy authentication can be used to support Oracle VPD. The proxy user allows for the row level security to be checked. When using Oracle VPD, it is also important to disable shared caching for the secured objects, as these objects should not be shared.