Customizing EntityManager injection for multi-tenancy

[Jayesh A Lalwani]

So, we are building an application that requires multi-tenancy support. There is one application that has to support multiple clients. Basically, we have the data hosted in 2 huge Oracle databases. For security reasons (and also to improve scalability), each client's data is stored in it's own schema within the Oracle database. When the user logs in to the application the LDAP entry for the user gives us the client code that the user belongs to. The JNDI is configured to contain the data source for each client, and we look up the data source when we create the EntittyManagerFactory

Also, we have 2 parts of the application, one is the front end and business logic layer that is hosted in JBoss. In this case, the JBoss acts as the containerThe second is the grid computation framework that is spring based and Spring acts as the container.

Traditionally, what we have been doing is we have our own Service Locator that acts as a factory for the EntityManager, and is also responsible for setting up transactions. Our Services, access the Service Locator to get the Entity Manager; the service locator manages a map of Entity Manager Factories, and when a new client is encountered, it looks up the data source from the JNDI and create the Entity Manager Factory


As you can imagine, it's a hell a lot of code, and also we have all these Services that are dependent on this Service Locator. I wanted to change this design to use EJBs for our services, and have the container manage the Entity Manager and the Transactions. This will significantly reduce our code base and make the design much cleaner

But, the problem I keep running into is this multi-tenancy problem. If I have my service like this

class MyServiceImpl implements MyService {
  @PersistenceContext(unitName = "persistenceunit")
    private EntityManager entityManager;
   .
   .
}

The container is going to inject the Entity Manager into my service. THis is great if I had only one database to access, but I don't. The Entity Manager injected into this service has to be based on the client code. I want to customize that injection code to create my own Entity Manager instead of using the container's default implementation. Is there a way to do that? Is there a way I can do this in Spring as well as JBoss.

[Martin Vashko]

Jayesh A Lalwani wrote:For security reasons (and also to improve scalability), each client's data is stored in it's own schema within the Oracle database.

That design might actually hurt scalability (issuing otherwise identical SQL statements against different schema unnecessarily floods the shared pool and causes more parsing and therefore more serialization). Alternative design would be to have a single schema, provide the security via VPD or FGAC and improve the performance by partitioning tables by clients, if needed at all (if most of your queries use indexes, you wouldn't even need to partition). This is actually not from my own head, I've recently accidentally bumped into this exact topic on asktom. If you could still make this change, definitely consider this (yes, the original question is dated, but as you can see, the premises still hold after all these years).

If I understood your explanation correctly, having one schema would solve the problem, wouldn't it?

[Jayesh A Lalwani]

The requirement for multiple schemas is coming from our DBAs and implementation people. Adding a little more info, we will add support of multiple organizations within a schema, and the tables will be partitioned by the organization ID. Your suggestion is already in the works.

However, the people who manage the implementations for each client report that there are significant differences in how each client uses the product. They would prefer to be able to tune the database indexes to match the usage of the client rather that try to build the indexes that satisfy all clients. Trying to maintain the same set of indexes for all our big clients is almost close to impossible. That's why they would prefer to have all the data for each of the bigger clients stored in it's own schema, whereas data for all the small clients will be stored in a single schema that is partitioned by the organization id. If in case, we do land the biggest client of them all. we might have to host their data in it's own database. The software has to be designed to not make the assumption that all the data will be in a single database/schema.

Or, as someone else had put it, the little fish will all be in the same tank. The big fish will have their own tanks, and if we do catch the whale, we will build a aquarium.

[Martin Vashko]

So it seems you're on your path already. I'm still not very convinced about that design, but it is not my business, of course

I hoped that the suggestion would resolve your problems, but if it is this way, I cannot really help.

[Jayesh A Lalwani]

I found a way to this in Spring, and found a possible way to do it in JBoss. The Spring way is cleaner, and really after figuring it out, I can;t believe why I didn't see it all along

Spring

I simply created my own DataSource that wraps a map of Data SOurces(key = tenant name, value = Data Source). It is provided with the seed data source that is configured during installation time. During run time, I have a thread-local variable that contains the tenantName. The application has to set the thread local variable at start of each request before doing anything else. My DataSource looks at this thread local variable, and looks for the data source in the map. If it's not there, it takes the seed data source, clones it and modifies it according to our business rules to point to the tenant's database, and puts it back in the map.

Everything works perfectly. My services that are injected with the entitymanager correctly work against the correct data source as long as the thread local variable is set correctly

JBoss

This is an idea we are kicking around. Probably we won't go with this, and just use the above Spring solution inside JBoss. Instead of binding the DataSOurce in JNDI, we can implement a DataSourceFactory and bind it. The DataSourceFactory can look at the aforementioned thread local variable and create the Data Source corresponding to the tenant. It should work. But haven't tried it. Not sure if the container does some sort of pooling of EntityManager. If it does, we will be FUBARed. Also, don;t know if the code that calls the ObjectFactory executes in the same thread as the thread that calls the service.

[Piotr Nowicki]

I'm just shooting here, but...

...do you think you could have a class which produces the Entity Managers using CDI's @Produces setter method basing on each client's information?

I.e. something like

public class Configurator {

    @PersistenceContext("pu1")
    private EntityManager em1;

    @PersistenceContext("pu2")
    private EntityManager em2;

    @Produces
    public EntityManager produceEm() {
        // Assuming that this condition is taken from 'some' accessible place
        if (user eligible for em1) {
            return em1;
        } else {
            return em2;
        }
    }
}

then in your application code you can just @Inject EntityManager em.

I'm just at the beginning of my CDI adventure so this might not be an accurate answer, but I would consider it -- the CDI -- worth revising :-)

Cheers!

[Jayesh A Lalwani]

That's very interesting, Pedro. That's for pointing me to CDI. I'm not very familiar with it. It might be interesting to play around with it