Java Persistence/Auditing and Security

Auditing and Security (database security, proxy authentication, and row level security (VPD))

JPA is 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 database 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 database 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.

There is no standard on how to do this with JPA, but some JPA providers allow the JPA persistence unit properties to be passed as EntityManager properties to configure the EntityManager connection. If your JPA provider does not support this, then you can try creating an EntityManagerFactory per user to allow passing the connection properties. This can be expensive though, so proceed with caution, ensure you disable a shared cache and set the connection pool size to 1.

EclipseLink/TopLink : Provide 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.

Example EntityManager using a user connection

Map properties = new HashMap();
properties.put("javax.persistence.jdbc.user", user);
properties.put("javax.persistence.jdbc.password", password);
EntityManager em = factory.createEntityManager(properties);

Java EE and JTA

If using Java EE and a DataSource the database user name and password may be able to be passed in the same way, depending on your JPA provider.

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 may still be able to 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

In JPA 2.0, the setProperty API can be used:

em.setProperty("javax.persistence.jdbc.user", user);
em.setProperty("javax.persistence.jdbc.password", password);

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.

Some JPA providers may allow for a single persistence unit to define multiple connection pools.

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 events could also be used to record the audit information, or to write to a separate audit 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.

See also,

• History.

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());
  }

}

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

Some databases such as the Oracle database provide 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.

Some JPA providers have support for proxy authentication.

EclipseLink/TopLink : Provide support for Oracle proxy authentication.

Row level security (VPD)

Some databases such as the Oracle database have support for row level security (Virtual Private 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.

Some JPA providers have support for row level security and VPD.

EclipseLink/TopLink : Provide support for Oracle VPD.