Latest revision as of 16:45, 29 February 2012

EclipseLink MultiTenancy with Oracle VPD

Since 1.0, EclipseLink has supported using Oracle VPD to share data within a table. Using VPD, clients can manage data in shared tables, and each client only has access to their own rows. In 2.3.0, EclipseLink added the ability to stripe database tables using the @Multitenant feature. The @Multitenant feature has two main components, writing a user id field on insert, and appending a comparison to that field in any generated SQL.

This example (source available here) shows how to use VPD and the EclipseLink multitenancy feature together. Instead of EclipseLink auto appending SQL, Oracle VPD will be used to ensure that only requested user data is returned.

NOTE: This example requires an Oracle Database of version 8i or higher, and you may need to configure your DB permissions to allow for creation of the policy and the stored function required for this example.

Multitenant Todo List

The example architecture is fairly simple. It has a single persistence unit definition, with a single EntityManagerFactory. Each client uniquely identifies itself within the persistence unit context (EntityManager). The model consists of one domain class Task that has a list of sub-tasks. The TASK table has a USER_ID field that is populated automatically on insert by EclipseLink using the @Multitenant feature. That same field is used by Oracle VPD to filter the rows in the database.

The main method in JavaSEExample class creates two EntityManagers, each for a different user. Each user stores their own personal tasks in the same Table (TASK). Depending on which user the EntityManager is created for, a different list of tasks is visible.

Oracle VPD

Oracle VPD has been supported on the Oracle database since 8i. In simple terms, VPD allows users to identify themselves as a specific user, and will be able to 'see' data specific to that user. All result limiting is done at the database level, removing the need to send special SQL containing an additional comparison.

For more information on Oracle VPD please click here.

Configuring VPD

Configuring VPD for this example requires two things, a policy and a stored function. The policy for this example is a call to the database 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 the SCOTT.TASK table. The policy is created like this:

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 table to limit the rows. The rows are being 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;

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

Using VPD

Now that Oracle VPD has been configured, you need to tell the database which user you are. This is done using the postAcquireExclusiveConnection event. The following code sets the client_identifier variable in the userenv context to the value in the "tenant.id" property.

   public void postAcquireExclusiveConnection(SessionEvent event) {
        SQLCall call = new SQLCall("CALL DBMS_SESSION.SET_IDENTIFIER('" + event.getSession().getProperty("tenant.id") + "')");
        event.getSession().executeNonSelectingCall(call);
    }

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

    public void preReleaseExclusiveConnection(SessionEvent event) {
        SQLCall call = new SQLCall("CALL DBMS_SESSION.CLEAR_IDENTIFIER()");
        event.getSession().executeNonSelectingCall(call);
     }
}

See the code in VPDSessionEventAdapter.

Multitenancy

Now that VPD is configured to use the USER_ID column for selects, deletes and updates, the next step is to tell EclipseLink to auto populate this column on inserts. The following code snippet turns on the Multitenancy feature for EclipseLink and specifies that the client identifier is passed in to the EMs using a property called tenant.id. Also note, as the filtering is done by VPD on the database, it is important to turn off caching on this entity to avoid leakage across users.

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

Disable the append of criteria

When the Multitenancy feature is enabled in EclipseLink, the specified client identifier is auto appended to any generated SQL. Now that we are using VPD to limit the data, this should be turned off, and how to do it differs slightly from release to release.

As of EclipseLink 2.4.0, the @Multitenant annotation allows for the criteria generation to be disabled:

@Multitenant(includeCriteria=false)
@TenantDiscriminatorColumn(name = "USER_ID", contextProperty = "tenant.id")

In 2.3.1, the following code needs to be run from a SessionCustomizer:

session.getDescriptor(Task.class).getQueryManager().setIncludeTenantCriteria(false);

NOTE: This can also be configured in 2.3.0. To see how, please see the comments in VPDSessionCustomizer.

Persistence XML

The persistence xml for this example contains a few settings that are required for this example to function properly:

...
    <properties>
        <!-- required in 2.3.1 for disabling the criteria auto appending to SQL queries-->
        <property name="eclipselink.session.customizer" value="example.VPDSessionCustomizer" /> 
        <!-- used to set and clear the VPD identifier -->
        <property name="eclipselink.session-event-listener" value="example.VPDSessionEventAdapter" />
        <!-- required to give one connection per EntityManager.  -->
        <property name="eclipselink.jdbc.exclusive-connection.mode" value="Always" /> 
        <!-- allow for native queries to be runnable from EclipseLink.  Required for the creation of the VPD artifacts -->
        <property name="eclipselink.jdbc.allow-native-sql-queries" value="true" />
    </properties>
...

Multitenant todo list in use

Use the following code to create an EntityManager for a specific user:

    Map<String, Object> emProps1 = new HashMap<String, Object>();
    emProps1.put("tenant.id", "USER1");
    EntityManager em1 = emf.createEntityManager(emProps1);

Use this EM normally to perform CRUD operations on it. All inserts will auto set the USER_ID field to 'USER1'. All other operations will use VPD to limit the results coming back from the database. Note, how the SQL itself is unmodified in the log, but the results that come back are limited to those with 'USER1' in the USER_ID field.

    Map<String, Object> emProps = new HashMap<String, Object>();
    emProps.put("tenant.id", "bsmith@here.com");
    EntityManager em = emf.createEntityManager(emProps);
 
    em.createQuery("Select t from Task t").getResultList());
 
....
....
RESULTS:
   SELECT ID, USER_ID, COMPLETED, DESCRIPTION, PARENT_ID FROM TASK
 
--> Incomplete Task(id: 1 -- Order Pizza), 
    Incomplete Task(id: 2 -- Tip Pizza delivery driver), 
    Incomplete Task(id: 3 -- Put house up for sale)

    Map<String, Object> emProps = new HashMap<String, Object>();
    emProps.put("tenant.id", "gdune@there.ca");
    EntityManager em = emf.createEntityManager(emProps);
 
    em.createQuery("Select t from Task t").getResultList());
 
....
....
RESULTS:
   SELECT ID, USER_ID, COMPLETED, DESCRIPTION, PARENT_ID FROM TASK
 
--> Incomplete Task(id: 6 -- Pay Bills), 
    Incomplete Task(id: 5 -- Feed fish), 
    Incomplete Task(id: 4 -- Drive kids to school)

More Information

• Session Event Listeners
• Session Customizers
• @Multitenant
• EclipseLink with VPD