Discriminator Multitenancy with Spring and Hibernate (No @Filters)

I’ll try to keep this short so we can get right to the code. Hibernate 4 onward has support for schema and database multitenancy. If this sounds like Klingon, read this article followed by the Hibernates docs.

So what about discriminator based multitenancy, with all the data in a single schema? This is pretty convenient for application maintenance. It’s also a great for startups, provided you have the flexibility to change strategies as your business scales. Time and time again – from StackOverflow questions to the open Hibernate feature request – I see folks say “use Hibernate filters”.

No, don’t use Hibernate filters. Here’s why:

• Filters are activated and parameterized at runtime. That means code changes, potential bugs, security holes, etc. Sure, there are one or two ways to implement global filters. However, these approaches are unwieldy and, as we discuss below, filters fall short in other areas.
• Hibernate doesn’t apply filters for CRUD operations. I’ve heard folks say that this isn’t important, since CRUD operations work on identities. Perhaps the semantics have changed with the addition of multitenancy. For instance, what’s to stop a piece of application code from providing a cross-tenant id to Session.get(…)? If I spam your RESTful webservice with random id’s, could I get another tenants data?
• JPA doesn’t support filters. To activate a filter in JPA you have to break encapsulation (i.e. EntityManager.unwrap(Session.class)). I know … this is a Hibernate article. But just saying.
• Hibernate can’t help you with native SQL. Separate schema / database multitenancy works fine, because data is segmented by connection. However, native SQL based packages – reporting tools, ETL frameworks, even Spring JDBC – will bypass Hibernate and leak data across tenants.
• Legacy code. Again, this is a Hibernate article. However, let’s suppose your legacy app (non-Hibernate) already implements schema / database multitenancy. Why not support discriminators as well? The approach presented in this article makes it trivial.

Enter the Tenant View Filter

The tenant view filter is a horizontal partitioning pattern, implemented at the database level using views. It’s described with examples in the aforementioned article. This approach addresses all the issues with Hibernate filters. The tenant view filter is always active and our existing application code (JDBC, Hibernate / JPA, external frameworks, etc) just works.

Let’s Get Started

Enough theory, let’s work by example. Start by checking out the complete code. The finished project is included (rjb-blog-multitenancy-complete), so feel free to skip ahead.

In this article, we’ll start with a simple single tenant application (rjb-blog-multitenancy-starter) and retrofit it for database, schema, and discriminator based multitenancy. The app defines a single entity – a Product – but this approach works for any number of entities regardless of their relationships. You should be familiar with the structure of the starter app: it’s a Spring / Hibernate project with JUnit tests over a mock HSQLDB database. Just run ProductDaoTest and work your way into the code. The only other classes of note are AbstractTest, and AbstractHsqlDbTestEnvironment, which allow us to initialize the database(s) before the Spring context initialization.

Tenant View Filter In Action

The first step is to implement the tenant view filter. Append the following code to the database initialization script.

/src/test/resources/scripts/hsqlbd/setup-1.sql:

/*
Implement the Tenant View Filter:
*/

-- 1) Rename the table 
-- use a convention - in this example, we prefix with 'T_'
ALTER TABLE RJB_BLOG_MULTITENANCY_1.PRODUCT RENAME TO T_PRODUCT;

-- 2) Add the tenant id column /w index
-- we need a default value for existing records
-- we use tenant 1
ALTER TABLE RJB_BLOG_MULTITENANCY_1.T_PRODUCT ADD COLUMN tenant_id VARCHAR(255) DEFAULT 'rjb-blog-mutlitenancy-1';
CREATE INDEX RJB_BLOG_MULTITENANCY_1.IDX_T_PRODUCT_TENANT_ID ON RJB_BLOG_MULTITENANCY_1.T_PRODUCT (tenant_id);

-- 3) Verify existing indexes. Unique indexes on non-generated fields must be adjusted to include the tenant id.
-- In this case, we adjust the sku index.
DROP INDEX RJB_BLOG_MULTITENANCY_1.IDX_PRODUCT_SKU;
CREATE UNIQUE INDEX RJB_BLOG_MULTITENANCY_1.IDX_PRODUCT_SKU ON RJB_BLOG_MULTITENANCY_1.T_PRODUCT (tenant_id, sku);

-- 4) Create the filtered view with the original table name
CREATE VIEW RJB_BLOG_MULTITENANCY_1.PRODUCT AS 
SELECT * FROM RJB_BLOG_MULTITENANCY_1.T_PRODUCT t
WHERE t.tenant_id IS NULL
OR t.tenant_id = CURRENT_USER;

-- 5) grant CRUD access to our 'Product' VIEW for the original test user
-- NOTE: the user cannot access the table.
GRANT SELECT, INSERT, UPDATE, DELETE ON RJB_BLOG_MULTITENANCY_1.PRODUCT TO "rjb-blog-mutlitenancy-1";

/*
Our Tenant View Filter is complete.
Existing queries - if performed on a correctly configured connection - will work as expected.
Execute ProductDaoTest now, to verify this.
*/

The inline comments sum it up:

• We’ve replaced the PRODUCT table with a filtered view. The view filters records by matching the CURRENT_USER id on the tenant_id. Records with a NULL tenant id are shared, a convention for global data.
• We’ve adjusted the SKU index to account for tenants i.e. SKU’s must be unique within a tenant, but not across tenants.
• We’ve adjusted the user grants to protect T_PRODUCT from native SQL.

Note

1. These database changes are trivial. In fact, you can implement the tenant view filter pattern for an entire schema in one shot, using a generic stored procedure. Perhaps I’ll post one in the future.
2. The CURRENT_USER function may differ across database vendors (e.g. SUSER_SID() for SQL Server). If you can think of a vendor that doesn’t support it, please comment below.

That’s it. Execute ProductDaoTest and everything passes. As promised, the existing code can’t tell the difference! We’re now ready to set up for multiple tenants.

DataSource Routing

This isn’t anything earth shattering – Spring JDBC has supported DataSource routing since 2.x via their AbstractRoutingDataSource class. The examples / documentation on this is pretty extensive, so we won’t rehash it here. You can find tons of posts with implementations similar to this.

Create com.rjb.blog.multitenancy.context.TenantResolver.java:

public interface TenantResolver {

	String getTenantId();

}

Create com.rjb.blog.multitenancy.spring.MultiTenantDataSource.java:

public class MultiTenantDataSource extends AbstractRoutingDataSource {

	private TenantResolver tenantResolver;
	
	public MultiTenantDataSource(TenantResolver tenantResolver) {
		this.tenantResolver = tenantResolver;
	}

	@Override
	protected Object determineCurrentLookupKey() {
		return tenantResolver.getTenantId();
	}
}

DataSource Spring Configuration.

Now let’s hook the new DataSource into our Spring configuration. Add the following to the properties configuration.

com.rjb.blog.multitenancy.config.PropertiesConfig.java:

/**
 *
 * A map of tenant id -> data source JNDI name.
 *
 * @return
 */
@Bean
public Map getTenantJndiDataSourceMappings() {
	Map returnVal = new HashMap();
	returnVal.put(“rjb-blog-mutlitenancy-1”, “java:/jdbc/rjb-blog-mutlitenancy-1”);
	return returnVal;
}

Create com.rjb.blog.multitenancy.config.ContextConfig.java:

public interface ContextConfig {
	TenantResolver tenantResolver();
}

Create com.rjb.blog.multitenancy.config.MultiTenantDataSourceConfig.java:

@Configuration
public class MultiTenantDataSourceConfig implements DataSourceConfig {
	@Inject
	private PropertiesConfig propertiesConfig;
	@Inject
	private ContextConfig contextConfig;
	@Bean
	public DataSource dataSource() {
		MultiTenantDataSource returnVal = new MultiTenantDataSource(contextConfig.tenantResolver());
		Map targetDataSources = new HashMap();
		targetDataSources.putAll(propertiesConfig.getTenantJndiDataSourceMappings());
		returnVal.setTargetDataSources(targetDataSources);
		returnVal.setLenientFallback(false);
		return returnVal;
	}
}

Configure JUnit

Next, we hook the new Spring configuration into our JUnit test.

In src/test/java, create com.rjb.blog.multitenancy.test.config.TestContextConfig.java:

@Configuration
public class TestContextConfig implements ContextConfig {
	@Bean
	public TenantResolver tenantResolver() {
		return new TestTenantResolver();
	}
	public class TestTenantResolver implements TenantResolver {
		private String tenantId;
		@Override
		public String getTenantId() {
			return this.tenantId;
		}
		/**
		 * We support manual set for tests.
		 * @param tenantId
		 */
		public void setTenantId(String tenantId) {
			this.tenantId = tenantId;
		}
	}
}

Replace the test config, to include the new TestContextConfig and MultiTenantDataSourceConfig.

com.rjb.blog.multitenancy.test.config.TestConfig.java:

@Configuration
@Import(value = { PropertiesConfig.class, Log4jConfig.class, TestContextConfig.class, MultiTenantDataSourceConfig.class,
		HibernateConfig.class, DaoConfig.class, TransactionConfig.class })
public class TestConfig {
}

And lastly, add the following to com.rjb.blog.multitenancy.test.AbstractTest.java:

@Inject
protected TestTenantResolver tenantResolver;
…
@BeforeTransaction
public void beforeTransaction() {
	this.tenantResolver.setTenantId(“rjb-blog-mutlitenancy-1”);
}

This method sets the tenant id for a test before a transaction is started. In a secure production app, a TenantResolver implementation would check a request or session scoped holder for the current user. The Spring Security principle for example. However, this is just a test, so we set it manually.

At this point, you can run ProductDaoTest again. Everything will pass. That’s because, although we’re configured for DataSource routing, we’re defaulting to the original DataSource in our beforeTransaction method.

Let’s make the necessary adjustments to test two tenants at once!

A Second DataSource

If we want to support a second tenant, we need to connect them to the database and create some mock data to test them against. First, let’s replace the original DataSource definition in our test environment.

com.rjb.blog.multitenancy.test.config.TestEnvironmentImpl.java

protected Collection> getDataSources() {
	Map dataSource1 = new HashMap();
	dataSource1.put(AbstractHsqlDbTestEnvironment.DATABASE_NAME, “rjb-blog-mutlitenancy-1”);
	dataSource1.put(AbstractHsqlDbTestEnvironment.SCHEMA_NAME, “RJB_BLOG_MULTITENANCY_1”);
	dataSource1.put(AbstractHsqlDbTestEnvironment.USERNAME, “rjb-blog-mutlitenancy-1”);
	dataSource1.put(AbstractHsqlDbTestEnvironment.PASSWORD, “rjb-blog-mutlitenancy-1”);
	dataSource1.put(AbstractHsqlDbTestEnvironment.JNDI_NAME, “java:/jdbc/rjb-blog-mutlitenancy-1”);
	Map dataSource2 = new HashMap();
	dataSource2.put(AbstractHsqlDbTestEnvironment.DATABASE_NAME, “rjb-blog-mutlitenancy-1”);
	dataSource2.put(AbstractHsqlDbTestEnvironment.SCHEMA_NAME, “RJB_BLOG_MULTITENANCY_1”);
	dataSource2.put(AbstractHsqlDbTestEnvironment.USERNAME, “rjb-blog-mutlitenancy-2”);
	dataSource2.put(AbstractHsqlDbTestEnvironment.PASSWORD, “rjb-blog-mutlitenancy-2”);
	dataSource2.put(AbstractHsqlDbTestEnvironment.JNDI_NAME, “java:/jdbc/rjb-blog-mutlitenancy-2”);
	return Arrays.asList(dataSource1, dataSource2);
}

Notice that the second DataSource is pointing to the same database and schema, but we’re using different credentials and JNDI name. Next, modify the properties config to reference this new DataSource.

com.rjb.blog.multitenancy.config.PropertiesConfig.java:

/**
 *
 * A map of tenant id -> data source JNDI name.
 *
 * @return
 */
@Bean
public Map getTenantJndiDataSourceMappings() {
	Map returnVal = new HashMap();
	returnVal.put(“rjb-blog-mutlitenancy-1”, “java:/jdbc/rjb-blog-mutlitenancy-1”);
	returnVal.put(“rjb-blog-mutlitenancy-2”, “java:/jdbc/rjb-blog-mutlitenancy-2”);
	return returnVal;
}

Our MultiTenantDataSource now has two available DataSources. We determine the DataSource for a transaction by manipulating the tenant id in our beforeTransaction method.

com.rjb.blog.multitenancy.test.AbstractTest.java:

@Rule
public TestName testName = new TestName();
…
@BeforeTransaction
public void beforeTransaction() {
	if (testName.getMethodName().contains(“tenant_2”)) {
		this.tenantResolver.setTenantId(“rjb-blog-mutlitenancy-2”);
	} else {
		// default to tenant 1
		// so all tests in the ProductDaoTest base class will still pass
		this.tenantResolver.setTenantId(“rjb-blog-mutlitenancy-1”);
	}
}

Note

This is a useful JUnit hack. Before a transaction is started, we inspect the name of the current test. If it contains the text “tenant_2”, we set the corresponding tenant id. We default to tenant 1 so our original tests pass.

At this point, we could run ProductDaoTest again, and everything would pass. But we’re focused on multitenancy here! So let’s add some test data and unit tests for tenant 2. Append the following to the end of the database setup script.

/src/test/resources/scripts/hsqlbd/setup-1.sql:

— mock Product(s) for tenant 2
INSERT INTO RJB_BLOG_MULTITENANCY_1.PRODUCT (id, tenant_id, created_user, created_timestamp, updated_user, updated_timestamp, category, sku)
VALUES (4, ‘rjb-blog-mutlitenancy-2’, ‘rjb-multitenancy-blog’, CURRENT_TIMESTAMP, ‘rjb-multitenancy-blog’, CURRENT_TIMESTAMP, ‘Test Category’, ‘SKU-2’);
— create a second user for tenant 2
CREATE USER “rjb-blog-mutlitenancy-2” PASSWORD ‘rjb-blog-mutlitenancy-2’;
— grant CRUD access to our ‘Product’ VIEW for tenant 2
GRANT SELECT, INSERT, UPDATE, DELETE ON RJB_BLOG_MULTITENANCY_1.PRODUCT TO “rjb-blog-mutlitenancy-2”;

The comments are pretty clear and the code is trivial. Let’s keep going and add the test cases.

com.rjb.blog.multitenancy.test.ProductDaoTest.java:

@Test
public void get_tenant_2() {
	Long expectedId = Long.valueOf(4);
	Product product = this.productDao.get(expectedId);
	assertTrue(product != null && expectedId.equals(product.getId()));
}
@Test
public void get_tenant_2_accessTenant1() {
	Long expectedId = Long.valueOf(1);
	Product product = this.productDao.get(expectedId);
	assertTrue(product == null);
}

Run the tests and watch your DataSource routing in action! These tests prove 3 things: (1) the data source routing works, provided we have the correct tenant id set, (2) tenant 2 can access their mock product (id = 4) from the database, and (3) tenant 2 cannot access tenant 1 data.

We can write similar “tenant_2” tests for getBySku, getByCategory and the remaining CRUD operations. I’ll leave that up to you – you can start by copying the existing tests.

Finished? Wait We Forgot Hibernate!

You may be wondering why we haven’t modified the Hibernate config. Not to mention, why does Hibernate handle multitenancy without any config changes? On the one hand, this is a testament to the tenant view filter – it’s so seamless, that Hibernate just works. But the truth is, Hibernate is only working partially. Our test cases pass because Hibernate executes statements against the MultiTenantDataSource. The DataSource routing is hidden, but it’s still occurring. However, since Hibernate doesn’t know about the multitenancy, we won’t get the seamless second-level cache support. Not good. To sort this out, we have to let Hibernate in on the deal.

Updated Entity Mapping

Pretty trivial, but our entities need a new tenant id field, with a corresponding entry in the Product Hibernate mapping.

com.rjb.blog.multitenancy.entity.AbstractEntity.java:

protected String tenantId;
…
public String getTenantId() {
	return tenantId;
}
public void setTenantId(String tenantId) {
	this.tenantId = tenantId;
}

/src/main/resources/mappings/Product.hbm.xml:

<property name="tenantId" type="string" column="tenant_id"/>

Hibernate Multitenancy Config

This is also well documented. To activate Hibernate’s multitenancy, we need a CurrentTenantIdentifierResolver and a MultiTenantConnectionProvider.

Create com.rjb.blog.multitenancy.hibernate.CurrentTenantIdentifierResolverImpl.java:

public class CurrentTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver {
	private TenantResolver tenantResolver;
	public CurrentTenantIdentifierResolverImpl(TenantResolver tenantResolver) {
		this.tenantResolver = tenantResolver;
	}
   	public String resolveCurrentTenantIdentifier() {
	return this.tenantResolver.getTenantId();
   	}
   	public boolean validateExistingCurrentSessions() {
       	return true;
   	}
}

Create com.rjb.blog.multitenancy.hibernate.MultiTenantConnectionProviderImpl.java:

public class MultiTenantConnectionProviderImpl extends AbstractMultiTenantConnectionProvider {
	private DatasourceConnectionProviderImpl dataSourceConnectionProvider;
	/**
	 *
	 */
	private static final long serialVersionUID = -1796618421575501644L;
	public MultiTenantConnectionProviderImpl(MultiTenantDataSource dataSource) {
		this.dataSourceConnectionProvider = new DatasourceConnectionProviderImpl();
		this.dataSourceConnectionProvider.setDataSource(dataSource);
		// this triggers the ‘available’ flag
		this.dataSourceConnectionProvider.configure(new HashMap());
	}
	@Override
	protected ConnectionProvider getAnyConnectionProvider() {
		return this.getDatasourceConnectionProvider();
	}
	@Override
	protected ConnectionProvider selectConnectionProvider(String tenantIdentifier) {
		return this.getDatasourceConnectionProvider();
	}
	protected DatasourceConnectionProviderImpl getDatasourceConnectionProvider() {
		return this.dataSourceConnectionProvider;
	}
}

Pretty simple right? That’s because everything is already handled under the hood of the DataSource. These Hibernate plugins are only required to switch Hibernate into multitenancy mode. Let’s add them to the Hibernate config.

com.rjb.blog.multitenancy.config.HibernateConfig.java:

@Inject
private ContextConfig contextConfig;
…
protected LocalSessionFactoryBean createSessionFactoryBean() {
	…
	// configure for multitenancy
	if (!(this.dataSourceConfig.dataSource() instanceof MultiTenantDataSource)) {
		throw new IllegalStateException(“a MultiTenantDataSource is required”);
	}
	factoryBean.getHibernateProperties().setProperty(AvailableSettings.MULTI_TENANT,
			MultiTenancyStrategy.DATABASE.toString());
	factoryBean.setMultiTenantConnectionProvider(
			new MultiTenantConnectionProviderImpl((MultiTenantDataSource) this.dataSourceConfig.dataSource()));
	factoryBean.setCurrentTenantIdentifierResolver(
			new CurrentTenantIdentifierResolverImpl(contextConfig.tenantResolver()));
	return factoryBean;
}

And add a test case to com.rjb.blog.multitenancy.test.ProductDaoTest.java:

@Test(expected = DataSegmentationException.class)
public void save_tenant_2_blockCrossTenant() {
	Product product = new Product();
	product.setCreatedUser(“test”);
	product.setCreatedDate(new Timestamp(System.currentTimeMillis()));
	product.setUpdatedUser(“test”);
	product.setUpdatedDate(new Timestamp(System.currentTimeMillis()));
	product.setCategory(“Test Category”);
	product.setSku(“SKU-Test”);
	// try to save into tenant 1
	product.setTenantId(“rjb-blog-mutlitenancy-1”);
	this.productDao.save(product);
}

Running the test fails as expected – without an update guard, tenant 2 can save records into tenant 1.

Implementing an Update Guard

To implement our guard, we can use database triggers or a Hibernate Interceptor. Triggers have the advantage of protecting against native SQL updates. So most legacy apps will require BEFORE INSERT / UPDATE / DELETE triggers to check the CURRENT_USER against the inbound tenant_id, and raise an error when they don’t match. Triggers, however, have a couple downsides: (A) triggers have to be added to each table (though you can use a procedure to add them all at once), and (B) they slow down low level batch operations.

If you’re not concerned about native SQL updates, opt for the Interceptor. A simple Interceptor can be configured as follows.

Create com.rjb.blog.multitenancy.hibernate.MultiTenantEntityInterceptor.java:

public class MultiTenantEntityInterceptor extends EmptyInterceptor {

	/**
	 * 
	 */
	private static final long serialVersionUID = -2372143420877673397L;

	private TenantResolver tenantResolver;

	public MultiTenantEntityInterceptor(TenantResolver tenantResolver) {
		this.tenantResolver = tenantResolver;
	}

	@Override
	public boolean onFlushDirty(Object entity, Serializable id, Object[] currentState, Object[] previousState,
			String[] propertyNames, org.hibernate.type.Type[] types) {
		return this.handleTenant(entity, id, currentState, propertyNames, types);
	}

	@Override
	public boolean onSave(Object entity, Serializable id, Object[] state, String[] propertyNames,
			org.hibernate.type.Type[] types) {
		return this.handleTenant(entity, id, state, propertyNames, types);
	}

	private boolean handleTenant(Object entity, Serializable id, Object[] currentState, String[] propertyNames,
			org.hibernate.type.Type[] types) {

		int index = ArrayUtil.indexOf(propertyNames, "tenantId");
		if (index < 0) {
			return false;
		}

		String activeTenantId = this.tenantResolver.getTenantId();
		Object tenantId = currentState[index];

		// on a new entity, set tenant id to current tenant
		if (tenantId == null || StringUtil.isEmpty(tenantId.toString())) {
			currentState[index] = activeTenantId;
			return true;
		}

		// on update, block cross tenant attempt
		else if (!tenantId.equals(activeTenantId)) {
			throw new DataSegmentationException(
					"cross tenant update, tenantId=" + tenantId + ", activeTenantId=" + activeTenantId);
		}

		return true;
	}
}

And add it to com.rjb.blog.multitenancy.config.HibernateConfig.java:

protected LocalSessionFactoryBean createSessionFactoryBean() {

	...

	// update guard
	factoryBean.setEntityInterceptor(new MultiTenantEntityInterceptor(contextConfig.tenantResolver()));

	return factoryBean;

}

Now re-run our failing test case in ProductDaoTest. We’ve patched the security hole, cross tenant updates are disabled!

Finished? Yep.

At this point, I could wrap up the post with our seamless discriminator multitenancy. The careful reader, however, may have noted that I said we would “retrofit it for database, schema, and discriminator based multitenancy” …

The truth is, it’s already done. Notice that we configured Hibernate for DATABASE multitenancy. Every tenant has their own DataSource, and the routing layer (Spring) ensures that Hibernate uses the correct one. We’ve essentially just made discriminator multitenancy work like database multitenancy (i.e. at the connection level).

In fact, we can use this single implementation for all 3 multitenancy methods at the same time. You could scale this app from a single schema supporting multiple tenants, to a schema per tenant, to a physical database instance per tenant. Perfect for maintaining your small customers in a single schema, with larger tenants in a separate schema, or in a separate database instance. All 3 approaches can be supported simultaneously from the same code base.

You can test this yourself, but here’s the approach:

• Add another database definition (e.g. “rjb-blog-mutlitenancy-2”) in the getDataBaseNames method of com.rjb.blog.multitenancy.test.config.TestEnvironmentImpl.java.
• Create another database initialization script (e.g. “setup-2.sql”) and add it to the getDatabasePopulatorScripts method of com.rjb.blog.multitenancy.test.config.TestEnvironmentImpl.java. This script doesn’t need to implement the tenant view filter – it’s a separate database.
• Add another DataSource definition (referencing your new database) in the getDataSources method of com.rjb.blog.multitenancy.test.config.TestEnvironmentImpl.java. Use a unique JNDI name.
• Add a new tenant id ⇒ JNDI name mapping in the getTenantJndiDataSourceMappings method of com.rjb.blog.multitenancy.config.PropertiesConfig.java.
• Edit the the beforeTransaction method of com.rjb.blog.multitenancy.test.AbstractTest.java, adding logic to “tenant_3” in the test method name and set the tenant id.
• Add your unit test (e.g. my_tenant_3_test()) to com.rjb.blog.multitenancy.test.ProductDaoTest.java

Good luck! Happy to help, leave your questions / comments below! Oh and P.S. see rjb-blog-multitenancy-extras for an example of side by side disriminator + schema + database multitenancy.

Mike Reid is the Director of Engineering at RJB Technology Inc., a Canadian firm with Branch Offices in Makati, Philippines. Contact us today for more information about our company, or to discuss your custom software development needs.