Multi tenancy app Java Spring Hibernate Mysql OAuth2 Spring Securit

Question

I'm working on POC java application that will support Multi Tenancy. I start my POC with JHipster generator and OAUTH2 authentication start on spring boot. Each tenant have own SCHEMA but tenants and OAUTH2 tables are public. JHipster use hibernate and Spring Data to connect with DB. In my example I use Mysql as database.

I want to achieve solution with single DataSource and single connection pool. As connection pool JHipster use HikariCP. In MultiTenantConnectionProvider I want to change SCHEMA in similar way like Hibernate doc describe (see Example 16.3.)

http://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html#d5e4866

When hibernate call getConnection(String tenantIdentifier) I want to set correct SCHEMA in MYSQL database. My implementation use Mysql command to change scheme "USE sample_tenant_identifier". I have to users with name "user" and "admin". Each of this user have own SCHEMA. Problem that I have is very strange. Example all SELECT operation use "user" schema but INSERT or UPDATE use "admin" SCHEMA. In result "admin" see data in "user" SCHEMA but INSERT data to "admin" SCHEMA.

package com.mycompany.myapp.tenancy.hibernate;

import org.hibernate.HibernateException;
import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;

/**
 * Created by AdamS on 2015-04-02.
 */
public class SchemaMultiTenantConnectionProviderImpl  implements MultiTenantConnectionProvider, ServiceRegistryAwareService {

    private final Logger log = LoggerFactory.getLogger(SchemaMultiTenantConnectionProviderImpl.class);
    DataSource dataSource;

    @Override
    public Connection getAnyConnection() throws SQLException {
        return this.dataSource.getConnection();
    }

    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        try {
            connection.createStatement().execute("USE jhipster;");
        }
        catch (SQLException e) {
            throw new HibernateException("Could not alter JDBC connection to specified schema [public]", e);
        }
        connection.close();
    }

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        log.debug("Tenatd is:"+tenantIdentifier);
        final Connection connection = getAnyConnection();
        try {
            connection.createStatement().execute("USE " + tenantIdentifier + ";");
//            connection.setCatalog(tenantIdentifier);
//            connection.setSchema(tenantIdentifier);
        }
        catch (SQLException e) {
            throw new HibernateException("Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]", e);
        }
        return connection;
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
//        try {
//            connection.createStatement().execute("USE "+tenantIdentifier+";");
//        }
//        catch (SQLException e) {
//            throw new HibernateException("Could not alter JDBC connection to specified schema [public]", e);
//        }
        connection.close();
    }

    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }

    @Override
    public boolean isUnwrappableAs(Class unwrapType) {
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> unwrapType) {
        return null;
    }

    @Override
    public void injectServices(ServiceRegistryImplementor serviceRegistry) {
        Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();
        DataSource localDs =  (DataSource) lSettings.get("hibernate.connection.datasource");
        dataSource = localDs;
    }
}

I create second working example where I create new DataSource for each tenant and store it in Map. This example work OK but concurrent map this is not what I want to exactly.

package com.mycompany.myapp.tenancy.hibernate;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * Created by AdamS on 2015-03-12.
 */
public class MyMultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider, ServiceRegistryAwareService {

    private final Logger log = LoggerFactory.getLogger(MyMultiTenantConnectionProviderImpl.class);

    DataSource dataSource;
    private Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>();

    public MyMultiTenantConnectionProviderImpl() {
        getSource("main");
    }

    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {

        connection.close();
    }

    @Override
    public Connection getAnyConnection() throws SQLException {
        //return this.dataSource.getConnection();
        log.info("get eny connection return main");
        return getSource("jhipster").getConnection();
    }

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        log.info("Tenatd is:" + tenantIdentifier);

        return getSource(tenantIdentifier).getConnection();
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {

        log.info("releaseConnection " + tenantIdentifier);
        connection.close();
    }

    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }

    @Override
    public boolean isUnwrappableAs(Class unwrapType) {
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> unwrapType) {
        return null;
    }

    @Override
    public void injectServices(ServiceRegistryImplementor serviceRegistry) {
        Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();
        DataSource localDs =  (DataSource) lSettings.get("hibernate.connection.datasource");
        dataSource = localDs;
    }

    public DataSource getSource(String tentant) {
        if(dataSourceMap.containsKey(tentant)){
            return dataSourceMap.get(tentant);
        } else {
            DataSource ds = dataSource(tentant);
            dataSourceMap.put(tentant,ds);
            return ds;
        }
    }

    public DataSource dataSource(String tentant) {
        log.info("Create Datasource "+tentant);

        HikariConfig config = new HikariConfig();
        config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
        config.addDataSourceProperty("url", "jdbc:mysql://localhost:3306/"+tentant);
        config.addDataSourceProperty("user", "root");
        config.addDataSourceProperty("password", "");

        //MySQL optimizations, see https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
        config.addDataSourceProperty("cachePrepStmts", true);
        config.addDataSourceProperty("prepStmtCacheSize",  "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("useServerPrepStmts", "true");

        return new HikariDataSource(config);
    }
}

My configuration class:

package com.mycompany.myapp.config;
...


@Configuration
@EnableJpaRepositories(basePackages  = {"com.mycompany.myapp.repository"},entityManagerFactoryRef = "entityManagerFactory",transactionManagerRef = "transactionManager")
@EnableJpaAuditing(auditorAwareRef = "springSecurityAuditorAware")
//@EnableTransactionManagement()
//@EnableAutoConfiguration(exclude = HibernateJpaAutoConfiguration.class)
public class DatabaseConfiguration implements EnvironmentAware {

    private final Logger log = LoggerFactory.getLogger(DatabaseConfiguration.class);

    private RelaxedPropertyResolver propertyResolver;

    private Environment env;

    private DataSource dataSource;

    @Autowired(required = false)
    private MetricRegistry metricRegistry;

    @Override
    public void setEnvironment(Environment env) {
        this.env = env;
        this.propertyResolver = new RelaxedPropertyResolver(env, "spring.datasource.");
    }

    @Bean(destroyMethod = "shutdown")
    @ConditionalOnMissingClass(name = "com.mycompany.myapp.config.HerokuDatabaseConfiguration")
    @Profile("!" + Constants.SPRING_PROFILE_CLOUD)
    public DataSource dataSource() {
        log.debug("Configuring Datasource");
        if (propertyResolver.getProperty("url") == null && propertyResolver.getProperty("databaseName") == null) {
            log.error("Your database connection pool configuration is incorrect! The application" +
                    "cannot start. Please check your Spring profile, current profiles are: {}",
                    Arrays.toString(env.getActiveProfiles()));

            throw new ApplicationContextException("Database connection pool is not configured correctly");
        }
        HikariConfig config = new HikariConfig();
        config.setDataSourceClassName(propertyResolver.getProperty("dataSourceClassName"));
        if (propertyResolver.getProperty("url") == null || "".equals(propertyResolver.getProperty("url"))) {
            config.addDataSourceProperty("databaseName", propertyResolver.getProperty("databaseName"));
            config.addDataSourceProperty("serverName", propertyResolver.getProperty("serverName"));
        } else {
            config.addDataSourceProperty("url", propertyResolver.getProperty("url"));
        }
        config.addDataSourceProperty("user", propertyResolver.getProperty("username"));
        config.addDataSourceProperty("password", propertyResolver.getProperty("password"));

        //MySQL optimizations, see https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
        if ("com.mysql.jdbc.jdbc2.optional.MysqlDataSource".equals(propertyResolver.getProperty("dataSourceClassName"))) {
            config.addDataSourceProperty("cachePrepStmts", propertyResolver.getProperty("cachePrepStmts", "true"));
            config.addDataSourceProperty("prepStmtCacheSize", propertyResolver.getProperty("prepStmtCacheSize", "250"));
            config.addDataSourceProperty("prepStmtCacheSqlLimit", propertyResolver.getProperty("prepStmtCacheSqlLimit", "2048"));
            config.addDataSourceProperty("useServerPrepStmts", propertyResolver.getProperty("useServerPrepStmts", "true"));
        }
        if (metricRegistry != null) {
            config.setMetricRegistry(metricRegistry);
        }
        dataSource = new HikariDataSource(config);
        return dataSource;
    }

    @Bean
    public SpringLiquibase liquibase(DataSource dataSource) {
        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setDataSource(dataSource);
        liquibase.setChangeLog("classpath:config/liquibase/master.xml");
        liquibase.setContexts("development, production");
        if (env.acceptsProfiles(Constants.SPRING_PROFILE_FAST)) {
            if ("org.h2.jdbcx.JdbcDataSource".equals(propertyResolver.getProperty("dataSourceClassName"))) {
                liquibase.setShouldRun(true);
                log.warn("Using '{}' profile with H2 database in memory is not optimal, you should consider switching to" +
                    " MySQL or Postgresql to avoid rebuilding your database upon each start.", Constants.SPRING_PROFILE_FAST);
            } else {
                liquibase.setShouldRun(false);
            }
        } else {
            log.debug("Configuring Liquibase");
        }
        return liquibase;
    }

    @Bean
    public MultiTenantSpringLiquibase liquibaseMt(DataSource dataSource) throws SQLException {
        MultiTenantSpringLiquibase multiTenantSpringLiquibase = new MultiTenantSpringLiquibase();
        multiTenantSpringLiquibase.setDataSource(dataSource);

        Statement stmt = null;
        stmt = dataSource.getConnection().createStatement();

        ResultSet rs = stmt.executeQuery("SELECT tu.tentantId FROM t_user tu WHERE tu.tentantId IS NOT NULL");
        ArrayList<String> schemas = new ArrayList<>();
        while(rs.next()) {
            String schemaName = rs.getString("tentantId");
            dataSource.getConnection().createStatement().executeUpdate("CREATE DATABASE IF NOT EXISTS "+schemaName);
            schemas.add(schemaName);
        }

        multiTenantSpringLiquibase.setSchemas(schemas);
        multiTenantSpringLiquibase.setChangeLog("classpath:config/liquibase/mt_master.xml");
        multiTenantSpringLiquibase.setContexts("development, production");
        if (env.acceptsProfiles(Constants.SPRING_PROFILE_FAST)) {
            if ("org.h2.jdbcx.JdbcDataSource".equals(propertyResolver.getProperty("dataSourceClassName"))) {
                multiTenantSpringLiquibase.setShouldRun(true);
                log.warn("Using '{}' profile with H2 database in memory is not optimal, you should consider switching to" +
                    " MySQL or Postgresql to avoid rebuilding your database upon each start.", Constants.SPRING_PROFILE_FAST);
            } else {
                multiTenantSpringLiquibase.setShouldRun(false);
            }
        } else {
            log.debug("Configuring MultiTenantSpringLiquibase");
        }

        return multiTenantSpringLiquibase;
    }

    @Bean
    public Hibernate4Module hibernate4Module() {
        return new Hibernate4Module();
    }
}

And EntityManagerConfiguration:

package com.mycompany.myapp.config;

....

/**
 * Created by AdamS on 2015-03-31.
 */
@Configuration
@EnableTransactionManagement
public class EntityManagerConfiguration  {
    @Autowired
    private DataSource dataSource;

    @Autowired
    private JpaVendorAdapter jpaVendorAdapter;

    @Bean(name = "entityManagerFactory")
    //@DependsOn("transactionManager")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws Throwable {

        HashMap<String, Object> properties = new HashMap<String, Object>();
        //properties.put("hibernate.transaction.jta.platform", AtomikosJtaPlatform.class.getName());
        //properties.put("javax.persistence.transactionType", "JTA");
        properties.put("hibernate.cache.region.factory_class", "org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory");
        properties.put("hibernate.cache.use_second_level_cache", "false");
        properties.put("hibernate.cache.use_query_cache", "false");
        properties.put("hibernate.generate_statistics", "true");

        properties.put("hibernate.tenant_identifier_resolver", "com.mycompany.myapp.tenancy.hibernate.MyCurrentTenantIdentifierResolver");
        /* MANY DATASOURCES. WORKING SOLUTION */
        //properties.put("hibernate.multi_tenant_connection_provider", "com.mycompany.myapp.tenancy.hibernate.MyMultiTenantConnectionProviderImpl");
        /*SCHEMA CONFIG THAT IS NOT WORKING*/
        properties.put("hibernate.multi_tenant_connection_provider", "com.mycompany.myapp.tenancy.hibernate.SchemaMultiTenantConnectionProviderImpl");
        properties.put("hibernate.multiTenancy", "SCHEMA");

        LocalContainerEntityManagerFactoryBean entityManager = new LocalContainerEntityManagerFactoryBean();
        entityManager.setDataSource(dataSource);
        entityManager.setJpaVendorAdapter(jpaVendorAdapter);
        //entityManager.setPackagesToScan("com.mycompany.myapp.domain");
        entityManager.setPackagesToScan(new String[] { "com.mycompany.myapp.domain","com.mycompany.myapp.tenancy.domain" });

        entityManager.setPersistenceUnitName("persistenceUnit");
        entityManager.setJpaPropertyMap(properties);
        return entityManager;
    }

    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory){
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);

        return transactionManager;
    }
}

Anybody have some idea why my Hibernate implementation can work in that way ? Whole project you can find on github:

https://github.com/upway/jhipster-multi-tenancy-poc

Answer 1

Replace

connection.createStatement().execute("USE " + tenantIdentifier + ";");

with

connection.setCatalog(tenantIdentifier);

The main issue in your code is (I think) using the sql USE ...; instead of using the setCatalog() function as explained in the mysql connector documentation

I ran into the same issue. As first I was thinking of an issue with the hibernate and built a new jhipster project based on your code. Even with the newer hibernate I reproduced the problem.

Once I used the setCatalog on the connection, it started to work.