Jump to: navigation, search

Difference between revisions of "EclipseLink/Examples/JPA/Auditing"

(Java EE and JTA)
(Provide each application user with a database user id)
Line 74: Line 74:
 
====Example of setting user/password on JEE EntityManager====
 
====Example of setting user/password on JEE EntityManager====
 
If using JPA 2.0, the setProperty API can be used:
 
If using JPA 2.0, the setProperty API can be used:
 
+
<source lang="java">
 +
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");
 +
</java>
  
 
Otherwise, the getDelegate API can be used:
 
Otherwise, the getDelegate API can be used:
 
+
<source lang="java">
If you created your EntityManager using injection, set the properties as follows:
+
Map properties = new HashMap();
 
+
properties.put("javax.persistence.jdbc.user", user);
((org.eclipse.persistence.internal.jpa.EntityManagerImpl)em.getDelegate()).setProperties(emProperties);
+
properties.put("javax.persistence.jdbc.password", password);
 
+
properties.put("eclipselink.jdbc.exclusive-connection.mode", "Always");
JPA 2.0 defines a new setProperty method on EntityManager that could be used instead:
+
properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "false");
 
+
((org.eclipse.persistence.internal.jpa.EntityManagerImpl)em.getDelegate()).setProperties(properties);
em.setProperty("eclipselink.oracle.proxy-type", oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME);
+
</java>
em.SetProperty(oracle.jdbc.OracleConnection.PROXY_USER_NAME, "john");
+
  
 
===Caching and security===
 
===Caching and security===
  
 
===Roles===
 
===Roles===
 
  
 
==Use a common database user id, and manage auditing and security in the application==
 
==Use a common database user id, and manage auditing and security in the application==

Revision as of 13:23, 16 June 2010


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");
</java>
 
Otherwise, the getDelegate API can be used:
<source lang="java">
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);
</java>
 
===Caching and security===
 
===Roles===
 
==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===
<source lang="java">
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 updateChgUser() {
    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

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

Caching and security

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.