EclipseLink/Examples/MySports/VPD

MySports using Oracle VPD

This page describes how the MySports demo can be configured to work with Oracle VPD to perform the SQL modifications necessary to limit what each tenant can see of the shared tables. The MySports example by default makes use of EclipseLink's @Multitenant support that will add in the necessary SQL predecates to all SELECT, UPDATE, & DELETE statements as well as adding the necessary tenant discriminator value to the INSERT statements. When using Oracle VPD to limit tenant access on shared tables EclipseLink must still INSERT the tenant discriminator values but instead of augmenting the queries it will set the tenant identifier with the JDBC connection's session and rely on the VPD configuration to perform and necessary augmentation.

Configuration

The demo has simplified the use of Oracle VPD to only require a simple configuration

MySports Configuration

Within the mysports application at the root of the source tree if a mysports.properties file. This file controls how the application operates. To enable VPD usage the flag must be set to true:

mysports.vpd=true

This flag will cause all of the necessary EclipseLink options and customizations to be invoked. For more details of how this functions please refer to the How It Works section below.

Database Configuration

The database must be configured to support VPD. The MySports test cases will do the configuration setting up a stored procedure to generate the SQL predicates as well as enabling the access policy on the multi-tenant tables (MYS_PLAYER, MYS_TEAM, & MYS_DIV)

CREATE OR REPLACE FUNCTION mysports_league_func (p_schema IN VARCHAR2 DEFAULT NULL, p_object IN VARCHAR2 DEFAULT NULL) \n RETURN VARCHAR2 \n AS \n BEGIN \n RETURN 'LEAGUE_ID = sys_context(''userenv'', ''client_identifier'')'; \n END;
 
CALL DBMS_RLS.ADD_POLICY (root@localhost, MYS_PLAYER, MYS_PLAYER_multitenant_policy, scott, mysports_league_func, SELECT, UPDATE, DELETE)

When a connection is acquired for use from the connection pool it is configured using a session event listener to execute:

CALL DBMS_SESSION.SET_IDENTIFIER('HTHL')

Prior to returning the connection to the pool the client identifier must be cleared:

CALL DBMS_SESSION.CLEAR_IDENTIFIER()

How It Works

Within the MySports application there are some EclipseLink features leveraged to make this all work:

1. SessionEventListener used to set and unset tenant (client) identifier on database connections
2. Disable @Multitenant's additional criteria