Fusion Middleware Documentation
Advanced Search


Solutions Guide for Oracle TopLink
Close Window

Table of Contents

Show All | Collapse

14 Tenant Isolation Using TopLink

With Oracle TopLink, you can develop a single application and then deploy it for different clients, or "tenants," with varying degrees of application and data isolation and of tenant-specific functionality. For example, a large company may develop a single payroll application to be used by multiple divisions. Each division has access to its own data and to shared data, but they cannot see any other division's data.

This chapter contains the following sections:

Use Case

Multiple application clients must share data sources, with private access to their data, for example in a Software as a Service (SaaS) environment.

Solution

Decide on a strategy for tenant isolation; then use TopLink's tenant isolation features to implement the strategy.

Components

  • TopLink 12c Release 1 (12.1.2) or later.

    Note:

    TopLink's core functionality is provided by EclipseLink, the open source persistence framework from the Eclipse Foundation. EclipseLink implements Java Persistence API (JPA), Java Architecture for XML Binding (JAXB), and other standards-based persistence technologies, plus extensions to those standards. TopLink includes all of EclipseLink, plus additional functionality from Oracle.

  • A compliant Java Database Connectivity (JDBC) database, such as Oracle Database, Oracle Express, or MySQL.

14.1 Introduction to the Solution

EclipseLink offers considerable flexibility in how you can design and implement features for isolating tenants. Possibilities include the following:

Application Isolation options

  • Separate container/server

  • Separate application within the same container/server

  • Separate entity manager factory and shared cache within the same application

  • Shared entity manager factory with tenant isolation per entity manager

Data isolation options

  • Separate database

  • Separate schema/tablespace

  • Separate tables

  • Shared table with row isolation

  • Query filtering

  • Oracle Virtual Private Database (VPD)

EclipseLink includes the following options for providing multi-tenancy in the data source:

  • Single-table multi-tenancy allows tenants to share tables. Each tenant has its own rows, identified by discriminator columns, and those rows are invisible to other tenants. See Using Single-Table Multi-Tenancy.

  • With table-per-tenant multi-tenancy, each tenant has its own table or tables, identified by table tenant discriminators, and those tables are invisible to other users. See Using Table-Per-Tenant Multi-Tenancy.

  • With (VDP) multi-tenancy, tenants use a VDP database, which provides the functionality to support multiple tenants sharing the same table. See Using VPD Multi-Tenancy.

EclipseLink further provides tenant-specific extensions through extensible entities using extensible entities and MetadataSource. For information about those features, see Chapter 12, "Making JPA Entities and JAXB Beans Extensible," and Chapter 13, "Using an External MetaData Source."

14.2 Using Single-Table Multi-Tenancy

With single-table multi-tenancy, any table (Table or SecondaryTable) to which an entity or mapped superclass maps can include rows for multiple tenants. Access to tenant-specific rows is restricted to the specified tenant.

Tenant-specific rows are associated with the tenant by using one or more tenant discriminator columns. Discriminator columns are used with application context values to limit what a persistence context can access.

The results of queries on the mapped tables are limited to the tenant discriminator value(s) provided as property values. This applies to all insert, update, and delete operations on the table. When multi-tenant metadata is applied at the mapped superclass level, it is applied to all subentities unless they specify their own multi-tenant metadata.

Note:

In the context of single-table multi-tenancy, “single-table” means multiple tenants can share a single table, and each tenant's data is distinguished from other tenants' data via the discriminator column(s). It is possible to use multiple tables with single-table multi-tenancy; but in that case, an entity's persisted data is stored in multiple tables, and multiple tenants can share all the tables.

14.2.1 Main Tasks for Using Single-Table Multi-Tenancy

The following tasks provide instructions for using single-table multi-tenancy:

14.2.1.1 Task 1: Prerequisites

To implement and use single-table multi-tenancy, you need:

14.2.1.2 Task 2: Enable Single-Table Multi-Tenancy

Single-table multi-tenancy can be enabled declaratively using the @Multitenant annotation, in an Object Relational Mapping (ORM) XML file using the <multitenant> element, or by using annotations and XML together.

14.2.1.2.1 Using the @Multitenant Annotation

To use the @Multitenant annotation, include it with an @Entity or @MappedSuperclass annotation. For example:

@Entity
@Table(name=“EMP”)
@Multitenant(SINGLE_TABLE)
public class Employee {
}

Note:

Single-table is the default multi-tenancy type, so SINGLE_TABLE does not have to be included in @Multitenant.

Note:

The @Table annotation is not required, because the discriminator column is assumed to be on the primary table. However, if the discriminator column is defined on a secondary table, you must identify that table using @SecondaryTable.

14.2.1.2.2 Using the <multitenant> Element

To use the <multitenant> element, include the element within an <entity> element. For example:

<entity class="model.Employee">
   <multitenant type="SINGLE_TABLE">
   ...
   </multitenant>
   ...
</entity>

14.2.1.3 Task 3: Specify Tenant Discriminator Columns

Discriminator columns are used together with an associated application context to indicate which rows in a table an application tenant can access.

Tenant discriminator columns can be specified declaratively using the @TenantDiscriminatorColumn annotation or in an object-relational (ORM) XML file using the <tenant-discriminator-column> element.

The following characteristics apply to discriminator columns:

  • Tenant discriminator column(s) must always be used with @Multitenant (or <multitenant> in the ORM XML file). You cannot specify the tenant discriminator column(s) only.

  • The tenant discriminator column is assumed to be on the primary table unless another table is explicitly specified.

  • On persist, the values of tenant discriminator columns are populated from their associated context properties.

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

    • Name = TENANT_ID (the database column name)

    • Context property = eclipselink.tenant.id (the context property used to populate the database column)

  • Tenant discriminator columns are application definable. That is, the discriminator column is not tied to a specific column for each shared entity table. You can use TENANT_ID, T_ID, etc.

  • There is no limit on the number of tenant discriminator columns an application can define.

  • Any name can be used for a discriminator column.

  • Generated schemas include specified tenant discriminator columns.

  • Tenant discriminator columns can be mapped or unmapped:

    • When a tenant discriminator column is mapped, its associated mapping attribute must be marked as read only.

    • Both mapped and unmapped properties are used to form the additional criteria when issuing a SELECT query.

14.2.1.3.1 Use the @TenantDiscriminatorColumn Annotation

To use the @TenantDiscriminatorColumn annotation, include it with @Multitenant annotation on an entity or mapped superclass, and optionally include the name and contextProperty attributes. If you do not specify these attributes, the defaults name = "TENANT-ID" and contextProperty = "eclipselink.tenant-id" are used.

For example:

@Entity
@Multitenant(SINGLE_TABLE)
@TenantDiscriminatorColumn(name = "TENANT", contextProperty = "multitenant.id")
public class Employee {
}

To specify multiple tenant discriminator columns, include multiple @TenantDiscriminatorColumn annotations within the @TenantDiscriminatorColumns annotation, and include the table where the column is located if it is not located on the primary table. For example:

@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() {
   ...
}
14.2.1.3.2 Use the <tenant-discriminator-column> Element

To use the <tenant-discriminator-column> element, include the element within a <multitenant> element and optionally include the name and context-property attributes. If you do not specify these attributes, the defaults name = "TENANT-ID" and contextProperty = "eclipselink.tenant-id" are used.

For example:

<entity class="model.Employee">
   <multitenant>
      <tenant-discriminator-column name="TENANT"
         context-property="multitenant.id"/>
   </multitenant>
   ...
</entity>

To specify multiple columns, include additional <tenant-discriminator-column> elements, and include the table where the column is located if it is not located on the primary table. For example:

<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>
14.2.1.3.3 Map Tenant Discriminator Columns

Tenant discriminator columns can be mapped to a primary key or to another column. The following example maps the tenant discriminator column to the primary key on the table during DDL generation:

@Entity
@Table(name = "ADDRESS")
@Multitenant
@TenantDiscriminatorColumn(name = "TENANT", contextProperty = "tenant.id",
   primaryKey = true)
public Address() {
  ...
}

The following example uses the ORM XML file to map the tenant discriminator column to a primary key:

<entity class="model.Address">
   <multitenant>
      <tenant-discriminator-column name="TENANT"
         context-property="multitenant.id" primary-key="true"/>
   </multitenant>
   <table name="ADDRESS"/>
   ...
</entity>

The following example maps the tenant discriminator column to another column named AGE:

@Entity
@Table(name = "Player")
@Multitenant
@TenantDiscriminatorColumn(name = "AGE", contextProperty = "tenant.age")
public Player() {
  ...
 
  @Basic
  @Column(name="AGE", insertable="false", updatable="false")
  public int age;
}

The following example uses the ORM XML file to map the tenant discriminator column to another column named AGE:

<entity class="model.Player">
  <multitenant>
    <tenant-discriminator-column name="AGE" context-property="tenant.age"/>
  </multitenant>
  <table name="PLAYER"/>
  ...
  <attributes>
    <basic name="age" insertable="false" updatable="false">
      <column name="AGE"/>
    </basic>
    ...
  </attributes>
  ...
</entity>
14.2.1.3.4 Define Persistence Unit and Entity Mappings Defaults

In addition to configuring discriminator columns at the entity and mapped superclass levels, you can also configure them at the persistence-unit-defaults and entity-mappings levels to provide defaults. Defining the metadata at the these levels follows similar JPA metadata defaulting and overriding rules.

Specify default tenant discriminator column metadata at the persistence-unit-defaults level in the ORM XML file. When defined at this level, the defaults apply to all entities of the persistence unit that have specified a multi-tenant type of SINGLE_TABLE minus those that specify their own tenant discriminator metadata. For example:

<persistence-unit-metadata>
  <persistence-unit-defaults>
    <tenant-discriminator-column name="TENANT_ID" context-property="tenant.id"/>
  </persistence-unit-defaults>
</persistence-unit-metadata>

You can also specify tenant discriminator column metadata at the entity-mappings level in the ORM XML file. A setting at this level overrides a persistence unit default and applies to all entities with a multi-tenant type of SINGLE_TABLE of the mapping file, minus those that specify their own tenant discriminator metadata. For example:

<entity-mappings>
  ...
      ...
      <tenant-discriminator-column name="TENANT_ID" context-property="tenant.id"/>
      ...
</entity-mappings>

14.2.1.4 Configure Context Properties and Caching Scope

Runtime context properties are used in conjunction with the multi-tenancy configuration on an entity (or mapped superclass) to implement the multi-tenancy strategy. For example, the tenant ID assigned to a tenant discriminator column for an entity is used at runtime (via an entity manager) to restrict access to data, based on that tenant's ownership of (or access to) the rows and tables of the database.

At runtime, multi-tenancy properties can be specified in a persistence unit definition or passed to a create entity manager factory call.

The order of precedence for tenant discriminator column properties is as follows:

  1. EntityManager

  2. EntityManagerFactory

  3. Application context (when in a Java EE container)

For example, to set the configuration on a persistence unit in persistence.xml:

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

Alternatively, to set the properties programmatically:

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

Note:

Swapping tenant IDs during a live EntityManager is not allowed.

14.2.1.4.1 Configure a Shared Entity Manager

By default, tenants share the entity manager factory. A single application instance with a shared EntityManagerFactory for a persistence unit can be responsible for handling requests from multiple tenants.

The following example shows a shared entity manager factory configuration:

EntityManager em = createEntityManager(MULTI_TENANT_PU);
em.getTransaction().begin();
em.setProperty(EntityManagerProperties.MULTITENANT_PROPERTY_DEFAULT, "my_id");

When using a shared entity manager factory, the L2 cache is by default not shared, and therefore multi-tenant entities have an ISOLATED cache setting.

To share the cache, set the eclipselink.multitenant.tenants-share-cache property to true. This results in multi-tenant entities having a PROTECTED cache setting.

Caution:

Queries that use the cache may return data from other tenants when using the PROTECTED setting.

14.2.1.4.2 Configure a Non-Shared Entity Manager

To create an entity manager factory that is not shared, set the eclipselink.multitenant.tenants-share-emf property to false.

When the entity manager factory is not shared, you must use the eclipselink.session-name property to provide a unique session name, as shown in the following example. This ensures that a unique server session and cache are provided for each tenant. This provides full caching capabilities. For example,

HashMap properties = new HashMap();
properties.put("tenant.id", "707");
properties.put("eclipselink.session-name", "multi-tenant-707");
...     
EntityManager em = Persistence.createEntityManagerFactory("multitenant", 
                      properties).createEntityManager();

Another example:

HashMap properties = new HashMap();
properties.put(PersistenceUnitProperties.MULTITENANT_SHARED_EMF, "false");
properties.put(PersistenceUnitProperties.SESSION_NAME, "non-shared-emf-for-this-emp");
properties.put(PersistenceUnitProperties.MULTITENANT_PROPERTY_DEFAULT, "this-emp");
...     
EntityManager em = Persistence.createEntityManagerFactory("multi-tenant-pu", properties).createEntityManager();

An example in the persistence unit definition:

<persistence-unit name="multi-tenant-pu">
  ...
  <properties>
    <property name="eclipselink.multitenant.tenants-share-emf" value="false"/>
    <property name="eclipselink.session-name" 
         value="non-shared-emf-for-this-emp"/>
    <property name="eclipselink.tenant-id" value="this-emp"/>
    ...
  </properties>
</persistence-unit>
14.2.1.4.3 Configure an Entity Manager

When configuring properties at the level of the entity manager, you must specify the caching strategies, because the same server session can be used for each tenant. For example, you can set up an isolation level (L1 cache) to ensure no shared tenant information exists in the L2 cache. These settings are set when creating the entity manager factory.

HashMap tenantProperties = new HashMap();
properties.put("tenant.id", "707");
 
HashMap cacheProperties = new HashMap();
properties.put("eclipselink.cache.shared.Employee", "false");
properties.put("eclipselink.cache.size.Address", "10");
properties.put("eclipselink.cache.type.Contract", "NONE");
...     
EntityManager em = Persistence.createEntityManagerFactory("multitenant", 
                      cacheProperties).createEntityManager(tenantProperties);
...

14.2.1.5 Task 4: Perform Operations and Queries

The tenant discriminator column is used at runtime through entity manager operations and querying. The tenant discriminator column and value are supported through the following entity manager operations:

  • persist()

  • find()

  • refresh()

The tenant discriminator column and value are supported through the following queries:

  • Named queries

  • Update all

  • Delete all

Note:

Multi-tenancy is not supported through named native queries. To use named native queries in a multi-tenant environment, manually handle any multi-tenancy issues directly in the query. In general, it is best to avoid named native queries in a multi-tenant environment.

14.2.1.6 Task 5: Use Single-Table Multi-Tenancy in an Inheritance Hierarchy

Inheritance strategies are configured by specifying the inheritance type (@javax.persistence.Inheritance). Single-table multi-tenancy can be used in an inheritance hierarchy, as follows:

  • Multi-tenant metadata can be applied only at the root level of the inheritance hierarchy when using a SINGLE_TABLE or JOINED inheritance strategy.

  • You can also specify multi-tenant metadata within a TABLE_PER_CLASS inheritance hierarchy. In this case, every entity has its own table, with all its mapping data (which is not the case with SINGLE_TABLE or JOINED strategies). Consequently, in the TABLE_PER_CLASS strategy, some entities of the hierarchy may be multi-tenant, while others may not be. The other inheritance strategies can only specify multi-tenancy at the root level, because you cannot isolate an entity to a single table to build only its type.

14.3 Using Table-Per-Tenant Multi-Tenancy

Table-per-tenant multi-tenancy allows multiple tenants of an application to isolate their data in one or more tenant-specific tables. Multiple tenants' tables can be in a shared schema, identified using a prefix or suffix naming pattern; or they can be in separate, tenant-specific schemas. Table-per-tenant entities can be mixed with other multi-tenant type entities within the same persistence unit.

The table-per-tenant multi-tenant type is used in conjunction with:

  • A tenant table discriminator that specifies the type of discriminator (schema or name with prefix or suffix)

  • A tenant ID to identify the user (configured per entity manager or at the entity manager factory, if isolating the table-per-tenant per persistence unit.)

A single application instance with a shared EntityManagerFactory for a persistence unit can be responsible for handling requests from multiple tenants.

Alternatively, separate EntityManagerFactory instances can be used for each tenant. (This is required when using extensions per tenant.) In this case, tenant-specific schema and table names are defined in an eclipselink-orm.xml configuration file. A MetadataSource must be registered with a persistence unit. The MetadataSource is used to support additional persistence unit metadata provided from outside the application.

For information about MetadataSource, see Chapter 13, "Using an External MetaData Source." See also metadata-source in Java Persistence API (JPA) Extensions Reference for Oracle TopLink.

The table-per-tenant multi-tenant type enables individual tenant table(s) to be used at the entity level. A tenant context property must be provided on each entity manager after a transaction has started.

  • The table(s) (Table and SecondaryTable) for the entity are individual tenant tables based on the tenant context. Relationships within an entity that uses a join or a collection table are also assumed to exist within the table-per-tenant context.

  • Multi-tenant metadata can only be applied at the root level of the inheritance hierarchy when using a SINGLE_TABLE or JOINED inheritance strategy. Multi-tenant metadata can be specified in a TABLE_PER_CLASS inheritance hierarchy

14.3.1 Main Tasks for Using Table-Per-Tenant Multi-Tenancy

The following tasks provide instructions for using table-per-tenant multi-tenancy:

14.3.1.1 Task 1: Prerequisites

To implement and use table-per-tenant multi-tenancy, you need:

14.3.1.2 Task 2: Enable Table-Per-Tenant Multi-Tenancy

Table-per-tenant multi-tenancy can be enabled declaratively using the @Multitenant annotation; or in an Object Relational Mapping (ORM) XML file using the <multitenant> element, or using annotations and XML together.

14.3.1.2.1 Using the @Multitenant and @TenantTableDiscriminator Annotations

To use the @Multitenant annotation, include the annotation with an @Entity or @MappedSuperclass annotation and include the TABLE_PER_TENANT attribute.

For example:

@Entity
@Multitenant(TABLE_PER_TENANT
...)
public class Employee {
}

The TABLE_PER_TENANT attribute states that clients have a dedicated table or tables (Table and SecondaryTable) associated with the entity.

14.3.1.2.2 Using the <multitenant> Element

To use the <multitenant> element, include the element within an <entity> element. For example:

<entity class="model.Employee">
   <multitenant type="TABLE_PER_TENANT">
   ...
   </multitenant>
   ...
</entity>

14.3.1.3 Task 3: Specify Tenant Table Discriminator

The tenant table discriminator describes the type of table discriminator to use in a table-per-tenant multi-tenancy strategy. The tenant table discriminator is identified by a property. You can define your own identifier or use the default property: org.eclipse.persistence.config.PersistenceUnitProperties.MULTITENANT_PROPERTY_DEFAULT = "eclipselink.tenant-id"

The tenant table discriminator can be specified at the entity or mapped superclass level, and it must always be accompanied with a Multitenant(TABLE_PER_TENANT) specification. It is not sufficient to specify only a tenant table discriminator.

The tenant table discriminator is used together with an associated application context to indicate which table or tables an application tenant can access.

14.3.1.3.1 Using the @TenantTableDiscriminator Annotation

Use the @TenantTableDiscriminator annotation to specify which tables are associated with which tenants. The tenant table discriminator must include a type and a context property:

  • Use the type attribute to identify what type of discriminator to use:

    • Use PREFIX to apply the tenant table discriminator as a prefix to all multi-tenant tables.

    • Use SUFFIX to apply the tenant table discriminator as a suffix to all multi-tenant tables.

    • Use SCHEMA to apply the tenant table discriminator as a schema to all multi-tenant tables. This strategy requires appropriate database provisioning.

  • Use the contextProperty attributes to identify the user. The value of the context property is a tenant ID that identifies the user. This can be configured for an entity manager or, if you want to isolate the table-per-tenant per persistence unit, an entity manager factory.

For example:

@Entity
@Table(name=“EMP”)
@Multitenant(TABLE_PER_TENANT)
@TenantTableDiscriminator(type=SCHEMA, contextProperty="eclipselink-tenant.id")
public class Employee {
    ...
}
14.3.1.3.2 Using the <tenant-table-discriminator> Element

To use the <tenant-table-discriminator> element, include the element within a <multitenant> element and include the name and context-property attributes. For example:

<entity class="Employee">
  <multitenant type="TABLE_PER_TENANT">
    <tenant-table-discriminator type="SCHEMA"  
        context-property="eclipselink-tenant.id"/>
  </multitenant>
  <table name="EMP">
  ...
</entity>

14.3.1.4 Task 4: Specify a Context Property at Runtime

At runtime, specify the context property using a persistence unit definition passed to an entity manager factory or set on an individual entity manager. For example:

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

To specify a context property at runtime programmatically:

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

An entity manager property definition follows:

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

14.3.1.5 Task 5: Perform Operations and Queries

The tenant discriminator column is used at runtime through entity manager operations and querying. The tenant discriminator column and value are supported through the following entity manager operations:

  • persist()

  • find()

  • refresh()

The tenant discriminator column and value are supported through the following queries:

  • Named queries

  • Update all

  • Delete all

Note:

Multi-tenancy is not supported through named native queries. To use named native queries in a multi-tenant environment, manually handle any multi-tenancy issues directly in the query. In general, it is best to avoid named native queries in a multi-tenant environment.

14.4 Using VPD Multi-Tenancy

A Virtual Private Database (VPD) uses security controls to restrict access to database objects based on various parameters.

For example, the Oracle Virtual Private Database supports security policies that control database access at the row and column level. Oracle VPD adds a dynamic WHERE clause to SQL statements issued against the table, view, or synonym to which the security policy was applied.

Oracle Virtual Private Database enforces security directly on the database tables, views, or synonyms. Because security policies are attached directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.

When a user directly or indirectly accesses a table, view, or synonym that is protected with an Oracle Virtual Private Database policy, Oracle Database dynamically modifies the SQL statement of the user. This modification creates a WHERE condition (called a predicate) returned by a function implementing the security policy. Oracle Virtual Private Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. Oracle Virtual Private Database policies can be applied to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

When using EclipseLink VPD multitanancy, the database handles the tenant filtering on all SELECT, INSERT, UPDATE, INDEX and DELETE queries.

To use EclipseLink VPD multi-tenancy, you must first configure VPD in the database and then specify multi-tenancy on the entity or mapped superclass, as shown in the following example, using @Multitenant and @TenantDiscriminatorColumn:

14.4.1 Main Tasks for Using VPD Multi-Tenancy

The following tasks provide instructions for using VPD multi-tenancy with Oracle Virtual Private Database:

14.4.1.1 Task 1: Prerequisites

To implement and use VPD multi-tenancy, you need:

14.4.1.2 Task 2: Configure the Virtual Private Database

In this example, an Oracle Virtual Private Database is configured with a policy and a stored procedure. The policy is a call to the database that tells the database 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 the SCOTT.TASK table. The policy is created as follows:

CALL DBMS_RLS.ADD_POLICY ('SCOTT', 'TASK', 'todo_list_policy', 'SCOTT', 'ident_func', 'select, update, delete'));

The function defined below is used by VPD to limit the data based on the identifier that is passed in to the connection. The function uses the USER_ID column in the table to limit the rows. The rows are limited, based on what is set in the client_identifier variable in the userenv context.

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;

14.4.1.3 Task 3: Configure the Entity or Mapped Superclass

As described above, VPD is configured to use the USER_ID column to limit access to rows. Therefore, you must tell EclipseLink to auto-populate the USER_ID column on inserts. The following code uses EclipseLink multi-tenancy and specifies that the client identifier is passed in to the entity managers using a property called tenant.id. Because the filtering is done by VPD on the database, you must turn off caching on this entity to avoid leakage across users.

@Entity
@Multitenant(VPD)
@TenantDiscriminatorColumn(name = "USER_ID", contextProperty = "tenant.id")
@Cacheable(false)
 
public class Task implements Serializable {
...
...

14.4.1.4 Task 4: Disable Criteria Generation

When single-table and table-per-tenant multi-tenancy are enabled, a client identifier is auto appended to any generated SQL. However, when VPD is used to limit the access to data, the auto-appending of the identifier should be turned off.

Beginning with TopLink 12c Release 1 (12.1.2), disable criteria generation as follows:

14.4.1.5 Task 5: Configure persistence.xml

Add the following properties to persistence.xml.

Include the following to set and clear the VPD identifier:

<property name="eclipselink.session-event-listener" value="example.VPDSessionEventAdapter" />

Include the following to provide one connection per entity manager:

<property name="eclipselink.jdbc.exclusive-connection.mode" value="Always" /> 

Include the following to allow native queries to be runnable from EclipseLink. This is required for creating VPD artifacts:

<property name="eclipselink.jdbc.allow-native-sql-queries" value="true" />
</properties>

For example:

<properties>
   <property name="eclipselink.session-event-listener" value="example.VPDSessionEventAdapter" />
   <property name="eclipselink.jdbc.exclusive-connection.mode" value="Always" /> 
   <property name="eclipselink.jdbc.allow-native-sql-queries" value="true" />
  ...
</properties>

14.5 Additional Resources

See the following resources for more information about the technologies and tools used to implement the solutions in this chapter: