Using partitioning for multi-confidential multi-confidential work with dynamic tenants

To be honest, I haven't tried Chris's suggestion, but opted for a more subtle solution. This is my decision.

• in my case, tenant = client; each client information is in its own database schema, potentially located in a separate instance of the DBMS (any vendor); in other words, I have one data source for each client.
• since I am using partitioning, this means each client has their own partition; each section is identified by a corresponding unique client ID
• each user entering the application belongs to a different client; I am using Spring Security for authentication and authorization, so I can get information about a user (including its owner) by askingSecurityContextHolder
  
  
• I have defined my own EclipseLink PartitioningPolicy
  
  that defines the client of the current user as described in the previous point and then returns a list containing only Accessor
  
  that identifies that client section

• all my tables need to be partitioned and I don't want to specify which on EVERY entity with annotations, so I registered this partitioning policy in EclipseLink at startup and set it as default; Briefly:

  JpaEntityManagerFactory jpaEmf = entityManagerFactory.unwrap(JpaEntityManagerFactory.class);
  ServerSession serverSession = jpaEmf.getServerSession();
  serverSession.getProject().addPartitioningPolicy(myCustomerPolicy);
  serverSession.setPartitioningPolicy(myCustomerPolicy);
  
        
  
          
          
          
        
  
      

Then, to dynamically add data sources to EclipseLink (called connection pools in EclipseLink terminology) so that the client ID specified by the above policy is mapped to a known "connection pool" in EclipseLink, I do the following:

• the listener intercepts any successful user login

• this EclipseLink listener request to see what it already knows about the connection pool identified by the user's user ID; if so, we're done, EclipseLink can handle the section correctly; otherwise, a new connection pool is created and added to EclipseLink; proof of concept:

  String customerId = principal.getCustomerId();
  JpaEntityManagerFactory jpaEmf = entityManagerFactory.unwrap(JpaEntityManagerFactory.class);
  ServerSession serverSession = jpaEmf.getServerSession();
  if (!serverSession.getConnectionPools().containsKey(customerId)) {
    DataSource customerDataSource = createDataSourceForCustomer(customerId);
    DatabaseLogin login = new DatabaseLogin();
    login.useDataSource(customerId);
    login.setConnector(new JNDIConnector(customerDataSource));
    Class<? extends DatabasePlatform> databasePlatformClass = determineDbVendorPlatform(customerId);
    login.usePlatform(databasePlatformClass.newInstance());
    ConnectionPool connectionPool = new ExternalConnectionPool(customerId, login, serverSession);
    connectionPool.startUp();
    serverSession.addConnectionPool(connectionPool);
  }
  
        
  
          
          
          
        
  
      

The login operation is of course done against the central database (or any other authentication source), so the above code occurs before any JPA request for a specific client is made (and hence the client connection pool is added to EclipseLink before the separation policy always refers to it).

• in this scenario, the section used is determined by the current user; this means that it will be the same for every request made within the HTTP session; Since I use entity managers with transactions that have the most lifespan of the request (which itself propagates well within an HTTP session), simply disabling the shared EclipseLink cache avoids mixing data from different clients; however, this is still undesirable.
• the best option I could find is to make sure that all ids (= primary keys) are generated and that the generation is handled by EclipseLink in a central cross-user mode, so that id = x for object E is certainly only assigned one instance of only one client object; it actually means "decoupling" the client ID assignment sequences and prevents the use of MySQL auto-increment columns (eg the type of database ID generation); so I decided to use a table generation type for entity ids and put this table in a central database where user and customer data is stored.

The last little problem to properly implement Option 2 is that even though the EclipseLink documentation states that it is possible to specify the connection pool (= data source) to be sequenced on the table using a config parameter eclipselink.connection-pool.sequence

, this seems to be ignored when the default split policy installed as described above. In fact, my client partitioining policy is called for EVERY request, even those used to allocate IDs. For this reason, the policy must intercept these requests and route them to a central data source. I couldn't find a definitive solution to this problem, but the best options I could think of are the following:

• if the SQL line of the query begins with "UPDATE SEQUENCE", it means that this is a query to allocate an identifier, provided that the table dedicated to the distribution of sequences is called SEQUENCE (this is the default)
• if you accept the convention to add SEQUENCE suffix to your generators, if the completed request name ends with "SEQUENCE" it means it is a request to allocate an id

I chose option 2, correctly defining my generation mappings as such:

@Entity
public class MyEntity {
  @Id
  @TableGenerator(name = "MyEntity_SEQUENCE", allocationSize = 10)
  @GeneratedValue(generator = "MyEntity_SEQUENCE")
  private Long id;
}

      

        
        
        
      

    

This forces EclipseLink to use a named table SEQUENCE

containing one row, the column value SEQ_NAME

is MyEntity_SEQUENCE

. The request used to update this sequence to allocate an id will be called MyEntity_SEQUENCE

and we're done. However, I have made my partitioining policy customizable so that I can switch from one request-request identification strategy to another at any time in case something changes in the EclipseLink implementation that violates this "heuristic".

This is essentially the whole picture. It works well for now. Feedback, improvements, suggestions are welcome.