Revision as of 09:56, 23 August 2011

EclipseLink MultiTenancy with Oracle VPD

!!!UNDER CONSTRUCTION!!!

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

This example (available here: [link to svn example]) shows how to use VPD and EclipseLink together to support MultiTenancy. Instead of auto appending SQL, we will use Oracle VPD 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.

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 by VPD to filter the rows in 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 the SCOTT.TASK table. 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.

MultiTenancy

Now that VPD is configured to use the USER_ID column, 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 id 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 id is auto appended to any generated SQL. This needs to be turned off, and how to do it differs slightly by release.

In 2.4.0 on later, the @Multitenancy 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: To see how to configure this setup in 2.3.0, please see the comments in VPDSessionCustomizer for more details.

Persistence XML

The persistence xml for this example contains a few settings that are required for this example to function. They are explained here:

...
    <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.VPDSessionEventAdaptor" />
        <!-- 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>
...

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, 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)