EclipseLink/Examples/JPA/Multitenant/VPD

EclipseLink MultiTenancy with Oracle VPD

!!!UNDER CONSTRUCTION!!!

Since 1.0, EclipseLink has supported using Oracle VPD to partition data within a table. Using VPD, each user can only see their own data. In 2.3.0, EclipseLink added the ability to user partition any database tables using the @MultiTenancy feature.

This example (available here: [link to svn example]) shows how to use VPD and EclipseLink together to support MultiTenancy.

Multi-Tenant ToDo List

The example at [link to svn example] is very simple in architecture. It has one domain class Task that has a one to many list of subTasks. The TASK table has a USER_ID field that is populated automatically on INSERT by EclipseLink using the @MultiTenancy feature. That same field is used filtered in Oracle VPD by the database.

The JavaSEExample class creates two EntityManagers each for a different user. Each user have their own personal tasks stored in the same Table. Depending on which user the EntityManager is created for, a different list of tasks is visible.

Oracle VPD

Oracle VPD is supported on most versions of the Oracle database. In simple terms, VPD allows users to identify themselves as a specific user, and will be able to 'see' data specific to that user.

For more information on VPD please see [link to VPD info].

Configuring VPD

Configuring VPD for this example requires two things, a policy and a stored function. The policy for this example is a native query that tells the DB to use a stored function to limit the results of a query. In this example, the function is called ident_func, and it is run whenever a select, update or delete is performed on SCOTT.TASK. The policy is created like this:

session.executeNonSelectingCall(new SQLCall(
  "CALL DBMS_RLS.ADD_POLICY ('SCOTT', 'TASK', 'todo_list_policy', 'SCOTT', 'ident_func', 'select, update, delete')"));

The next thing to configure is the function used by VPD to limit the data based on the identifier that is passed in to the connection (more on that later). The following snippet of code, will create a simple function that will use the USER_ID column in the database to filter the rows based on what is set in the client_identifier variable in the userenv context.

session.executeNonSelectingCall(new SQLCall(
"CREATE OR REPLACE FUNCTION ident_func (p_schema in VARCHAR2 default NULL, p_object in VARCHAR2 default NULL) 
    RETURN VARCHAR2 
    AS 
    BEGIN 
       return 'USER_ID = sys_context(''userenv'', ''client_identifier'')';
    END;"  ));

To see this code in action, please see the method JavaSEExample.vpdInitDB(EntityManagerFactory emf) in the example.

Using VPD

Now that the VPD has been configured, you need to tell the database which user you are. This is done using the postAcquireExclusiveConnection event. It looks like this:

public void postAcquireExclusiveConnection(SessionEvent event) {
    DatabaseAccessor accessor = (DatabaseAccessor) event.getResult();
    SQLCall call = new SQLCall("CALL DBMS_SESSION.SET_IDENTIFIER('" + event.getSession().getProperty("tenant.id") + "')");
    call.returnNothing();
    accessor.executeCall(call, new DatabaseRecord(), (AbstractSession) event.getSession());
}

Also, the preReleaseExclusiveConnection will need to clear the IDENTIFIER, like this:

public void preReleaseExclusiveConnection(SessionEvent event) {
    DatabaseAccessor accessor = (DatabaseAccessor) event.getResult();
    SQLCall call = new SQLCall("CALL DBMS_SESSION.CLEAR_IDENTIFIER()");
    call.returnNothing();
    accessor.executeCall(call, new DatabaseRecord(), (AbstractSession) event.getSession());
}

See the code in VPDSessionEventAdaptor.

Support for multitenant entities is done though the usage of the @Multitenant annotation or <multitenant> xml element configured in your eclipselink-orm.xml mapping file. The @Multitenant annotation can be used on an @Entity or @MappedSuperclass and is used in conjunction with the @TenantDiscriminatorColumn or <tenant-discriminator-column> xml element.

The tenant discriminator column defines the tenant identifying database column and there may be 1 or more such columns. These columns can be unmapped or mapped. When mapped, they must be marked as read-only. See the annotation and xml examples to follow.

When a multitenant entity is specified, the tenant discriminator column can default. Its default values are:

• name = TENANT_ID (the database column name)
• context property = tenant.id (the context property used to populate the database column)

The context property is a value that is required at runtime in order to acces sthe specific rows for a tenant. This value is configured at the persistenec unit or persistence context (see usages) and if not specified a runtime exception will be thrown when attempting to query or modify a multitenant entity type.

Persistence Usage for Multiple Tenants

There are multiple usage options available for how an EclipseLink JPA persistence unit can be used in an application with @Multitenant entity types. Since different tenants will have access to only its rows the persistence layer must be configured so that entities from different tenants do not end up in the same cache.

These architetcures with usage notes include:

• Dedicated Persistence Unit: In this usage there is a persistence unit defined per tenant and the application must request the correct PersistenceContext or PersistenceUnit for its tenant. This can be used through container managed or application bootstrapped JPA.

<source lang="xml">
<persistence-unit name="mysports-OSL">
  ...
  <properties>
    <property name="eclipselink.tenant-id" value="OSL"/>
    ...
  </properties>
</persistence-unit>

</source>

• Persistence Context per Tenant: Using a single persistence unit definition in the persistence.xml and a shared persistence unit (EntityManagerFactory and cache) the tenant context is specified per persistence Context (EntityManager) at runtime using the createEntityManager(Map) API. This approach can be used with @PersistenceUnit injection but not with container managed @PersistenceContext injection.
  • When using this architecture there is a shared cache available for regular entity types but the Multitenant types must be PROTECTED in the cache so the MULTITENANT_SHARED_EMF property must be set to true.

<property name="eclipselink.multitenant.tenants-share-cache" value="true" />

• Persistence Unit per Tenant: In this architecture there is a single persistence unit defined in the persistence.xml and through use of the application bootstrap API (no container injection supported) new persistence contexts with their own caches are created per tenant.
  • The eclipselink.session-name (SESSION_NAME) property must be provided to ensure a unique server session (and cache) is provided for each tenant.

Usage Summary

Usage	@PersistenceContext EntityManager Injection	@PersistenceUnit EntityManagerFactory Injection	Persistence.createEntityManagerFactory Application Bootstrap API
Dedicated	Yes	Yes	Yes
Persistence Context per Tenant	No	Yes	Yes
Persistence Unit per Tenant	No	No	Yes

Simple Example

Note, through annotations, specifying only a tenant discriminator column itself does not enable a multitenant entity. At a minimum, the @Multitenant must also be specified. Meaning the minimal configuration is:

@Entity
@Multitenant
public class Player  {
}

This configuration means that the Player entity type has rows for multiple tenants stored in its default PLAYER table and that the default TENANT_ID column is used as a discriminator along with the default context property eclipselink.tenant-id.

Assuming this application wants to use a shared EntityManagerFactory and have the EntityManager be tenant specific then it would be used like:

Map<String, Object> emProperties = new HashMap<String, Object>();
 
emProperties.set("eclipselink.tenant-id", "HTHL");
 
EntityManager em = emf.createEntityManager(emProperties);

Additional Examples

The following examples outline other possible configurations using annotations and XML.

/** Single discriminator tenant column **/
 
@Entity
@Table(name = "CUSTOMER")
@Multitenant
@TenantDiscriminatorColumn(name = "TENANT", contextProperty = "multi-tenant.id")
public Customer() {
  ...
}
 
/** Multiple tenant discriminator columns using multiple tables **/
 
@Entity
@Table(name = "EMPLOYEE")
@SecondaryTable(name = "RESPONSIBILITIES")
@Multitenant(SINGLE_TABLE)
@TenantDiscriminatorColumns({
    @TenantDiscriminatorColumn(name = "TENANT_ID", contextProperty = "employee-tenant.id", length = 20)
    @TenantDiscriminatorColumn(name = "TENANT_CODE", contextProperty = "employee-tenant.code", discriminatorType = STRING, table = "RESPONSIBILITIES")
  }
)
public Employee() {
  ...
}
 
/** Tenant discriminator column mapped as part of the primary key on the database **/
 
@Entity
@Table(name = "ADDRESS")
@Multitenant
@TenantDiscriminatorColumn(name = "TENANT", contextProperty = "tenant.id", primaryKey = true)
public Address() {
  ...
}
 
/** Mapped tenant discriminator column **/
 
@Entity
@Table(name = "Player")
@Multitenant
@TenantDiscriminatorColumn(name = "AGE", contextProperty = "tenant.age")
public Player() {
  ...
 
  @Basic
  @Column(name="AGE", insertable="false", updatable="false")
  public int age;
}

<!-- Single tenant discriminator column -->
 
<entity class="model.Customer">
  <multitenant>
    <tenant-discriminator-column name="TENANT context-property="multi-tenant.id""/>
  </multitenant>
  <table name="CUSTOMER"/>
  ...
</entity>
 
<!-- Multiple tenant discriminator columns using multiple tables -->
 
<entity class="model.Employee">
  <multitenant type="SINGLE_TABLE">
    <tenant-discriminator-column name="TENANT_ID" context-property="employee-tenant.id" length="20"/>
    <tenant-discriminator-column name="TENANT_CODE" context-property="employee-tenant.id" discriminator-type="STRING" table="RESPONSIBILITIES"/>
  </multitenant>
  <table name="EMPLOYEE"/>
  <secondary-table name="RESPONSIBILITIES"/>
  ...
</entity>
 
<!-- Tenant discriminator column mapped as part of the primary key on the database -->
 
<entity class="model.Address">
  <multitenant>
    <tenant-discriminator-column name="TENANT" context-property="multi-tenant.id" primary-key="true"/>
  </multitenant>
  <table name="ADDRESS"/>
  ...
</entity>
 
<!-- Mapped tenant discriminator column -->
 
<entity class="model.Player">
  <multi-tenant>
    <tenant-discriminator-column name="AGE" context-property="tenant.age"/>
  </multi-tenant>
  <table name="PLAYER"/>
  ...
  <attributes>
    <basic name="age" insertable="false" updatable="false">
      <column name="AGE"/>
    </basic>
    ...
  </attributes>
  ...
</entity>

At runtime, the context property configuration can be specified via a persistence unit definition, passed to a create entity manager factory call or set on individual entity managers.

<persistence-unit name="multi-tenant">
  ...
  <properties>
    <property name="tenant.id" value="707"/>
    ...
  </properties>
</persistence-unit>

Or alternatively in code as follows:

HashMap properties = new HashMap();
properties.put(PersistenceUnitProperties.MULTITENANT_PROPERTY_DEFAULT, "707");   
EntityManager em = Persistence.createEntityManagerFactory("multi-tenant-pu", properties).createEntityManager();

An entity Manager property definition would be as follows:

EntityManager em = Persistence.createEntityManagerFactory("multi-tenant-pu").createEntityManager();
em.beginTransaction();
em.setProperty("other.tenant.id.property", "707");
em.setProperty(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, "707");
...