V2Stech

In this part we will continue to see how to set up multi-tenant database access to the tenant databases using Spring JPA and Hibernate. Also find out how Spring Security is tied into all of this.

Multi-Tenancy data access and tenant database selection

In multi-tenant setup,  Hibernate requires a datasource to be fed so that it can establish a connection/session with the database defined by the datasource.

Data access

For a multi-tenancy setup, Hibernate provides two facilities –

• CurrentTenantIdentifierResolver – an interface which should be implemented by the application code to identify the tenant
• AbstractDataSourceBasedMultiTenantConnectionProviderImpl – an abstract class which provides support for providing the correct datasource based on the tenant id to Hibernate

Tenant identification and data source selection

In previous part of the blog we had considered that the tenant identifier is provided by the end user via the login form. The application code needs to parse this tenant field in the Spring Security layer and store it for tenant id resolution.
Spring provides a UsernamePasswordAuthenticationFilter which is extended by the example application to retrieve the tenant value from the login form. This tenant value is stored in a ThreadLocal variable so that it can be used by the CurrentTenantIdentifierResolver implementation.

Understanding the code used in the application

Let’s go through the most significant pieces of the code so that you can follow how it is all related and how it all works.

File: MultitenancyProperties.java

There are many factors to consider before choosing a data isolation approach as mentioned above. We will not go into depth of each but focus mainly on building a multi-tenant application using the latest Spring Boot 2 framework along with JPA, Hibernate as the JPA provider and Spring Security 5.

Multi-tenancy using database per tenant approach

As seen in previous post, the application.yml defines the data sources for the tenant databases. These should be available to the application code. MultitenancyProperties class has the @ConfigurationProperties annotation and is asked to parse the properties under the multitenancy.app node from application.yml file.

package com.example.multitenancy;

import java.util.List;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

/**
 * This class reads the <code>multitenancy.mtapp</code> node from
 * <code>application.yml</code> file and populates a list of
 * {@link org.springframework.boot.autoconfigure.jdbc.DataSourceProperties}
 * objects, with each instance containing the data source details about the
 * database like url, username, password etc
 * 
 * @author Sunit Katkar
 * @version 1.0 
 * @since 1.0 (April 2018)
 */@Configuration
@ConfigurationProperties("multitenancy.mtapp")
public class MultitenancyProperties {

    private List<DataSourceProperties> dataSourcesProps;

    public List<DataSourceProperties> getDataSources() {
        return this.dataSourcesProps;
    }

    public void setDataSources(List<DataSourceProperties> dataSourcesProps) {
        this.dataSourcesProps = dataSourcesProps;
    }

    public static class DataSourceProperties 
        extends org.springframework.boot.autoconfigure.jdbc.DataSourceProperties {

        private String tenantId;

        public String getTenantId() {
            return tenantId;
        }

        public void setTenantId(String tenantId) {
            this.tenantId = tenantId;
        }
    }
}

File: MultitenancyJpaConfiguration.java

Hibernate needs to be fed with the datasource that it should connect to. MultiTenancyJpaConfiguration reads the data sources from MultitenancyProperties and creates a map of the data sources. This map is then used for selecting the required tenant database based on the tenant identifier and Hibernate establishes a connection with that database.

package com.example.multitenancy;

import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.hibernate.MultiTenancyStrategy;
import org.hibernate.cfg.Environment;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.example.model.Employee;
import com.example.multitenancy.MultitenancyProperties.DataSourceProperties;

/**
 * This class defines the data sources to be used for accessing the different
 * databases (one database per tenant). It generates the Hibernate session and
 * entity bean for database access via Spring JPA as well as the Transaction
 * manager to be used.
 * 
 * @author Sunit Katkar
 * @version 1.0
 * @since 1.0 (April 2018)
 */@Configuration
@EnableConfigurationProperties({ MultitenancyProperties.class, JpaProperties.class })
@EnableTransactionManagement
public class MultiTenancyJpaConfiguration {

    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private MultitenancyProperties multitenancyProperties;

    /**
     * Builds a map of all data sources defined the application.yml file
     * 
     * @return
     */    @Primary
    @Bean(name = "dataSourcesMtApp")
    public Map<String, DataSource> dataSourcesMtApp() {
        Map<String, DataSource> result = new HashMap<>();
        for (DataSourceProperties dsProperties : this.multitenancyProperties.getDataSources()) {
            
            DataSourceBuilder factory = DataSourceBuilder.create()
                    .url(dsProperties.getUrl())
                    .username(dsProperties.getUsername())
                    .password(dsProperties.getPassword())
                    .driverClassName(dsProperties.getDriverClassName());
            
            result.put(dsProperties.getTenantId(), factory.build());
        }
        return result;
    }

    /**
     * Autowires the data sources so that they can be used by the Spring JPA to
     * access the database
     * 
     * @return
     */    @Bean
    public MultiTenantConnectionProvider multiTenantConnectionProvider() {
        // Autowires dataSourcesMtApp
        return new DataSourceBasedMultiTenantConnectionProviderImpl();
    }

    /**
     * Since this is a multi-tenant application, Hibernate requires that the current
     * tenant identifier is resolved for use with
     * {@link org.hibernate.context.spi.CurrentSessionContext} and
     * {@link org.hibernate.SessionFactory#getCurrentSession()}
     * 
     * @return
     */    @Bean
    public CurrentTenantIdentifierResolver currentTenantIdentifierResolver() {
        return new CurrentTenantIdentifierResolverImpl();
    }

    /**
     * org.springframework.beans.factory.FactoryBean that creates a JPA
     * {@link javax.persistence.EntityManagerFactory} according to JPA's standard
     * container bootstrap contract. This is the most powerful way to set up a
     * shared JPA EntityManagerFactory in a Spring application context; the
     * EntityManagerFactory can then be passed to JPA-based DAOs via dependency
     * injection. Note that switching to a JNDI lookup or to a
     * {@link org.springframework.orm.jpa.LocalEntityManagerFactoryBean} definition
     * is just a matter of configuration!
     * 
     * @param multiTenantConnectionProvider
     * @param currentTenantIdentifierResolver
     * @return
     */    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(
            MultiTenantConnectionProvider multiTenantConnectionProvider,
            CurrentTenantIdentifierResolver currentTenantIdentifierResolver) {

        Map<String, Object> hibernateProps = new LinkedHashMap<>();
        hibernateProps.putAll(this.jpaProperties.getProperties());
        hibernateProps.put(Environment.MULTI_TENANT, MultiTenancyStrategy.DATABASE);
        hibernateProps.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProvider);
        hibernateProps.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolver);

        // No dataSource is set to resulting entityManagerFactoryBean
        LocalContainerEntityManagerFactoryBean result = new LocalContainerEntityManagerFactoryBean();
        result.setPackagesToScan(new String[] { Employee.class.getPackage().getName() });
        result.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        result.setJpaPropertyMap(hibernateProps);

        return result;
    }

    /**
     * Interface used to interact with the entity manager factory for the
     * persistence unit.
     * 
     * @param entityManagerFactoryBean
     * @return
     */    @Bean
    public EntityManagerFactory entityManagerFactory(LocalContainerEntityManagerFactoryBean 
            entityManagerFactoryBean) {
        return entityManagerFactoryBean.getObject();
    }

    /**
     * Creates a new
     * {@link org.springframework.orm.jpa.JpaTransactionManager#JpaTransactionManager(EntityManagerFactory emf)}
     * instance.
     * 
     * {@link org.springframework.transaction.PlatformTransactionManager} is the
     * central interface in Spring's transaction infrastructure. Applications can
     * use this directly, but it is not primarily meant as API: Typically,
     * applications will work with either TransactionTemplate or declarative
     * transaction demarcation through AOP.
     * 
     * @param entityManagerFactory
     * @return
     */    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

File: DataSourceBasedMultiTenantConnectionProviderImpl.java

Hibernate provides AbstractDataSourceBasedMultiTenantConnectionProviderImpl which provides support for connections in a multi-tenant setup like the example application.

DataSourceBasedMultiTenantConnectionProviderImpl extends the abstract class and implements the selectDataSource() and selectAnyDataSource() methods.  This class basically does the job of selecting the correct database based on the tenant id found by the tenant id resolver, which I will show next.

package com.example.multitenancy;

import java.util.Map;

import javax.sql.DataSource;

import org.hibernate.engine.jdbc.connections.spi.AbstractDataSourceBasedMultiTenantConnectionProviderImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * This class does the job of selecting the correct database based on the tenant
 * id found by the {@link CurrentTenantIdentifierResolverImpl}
 * 
 * @author Sunit Katkar
 * @version 1.0
 * @since 1.0 (April 2018)
 */@Component
public class DataSourceBasedMultiTenantConnectionProviderImpl
        extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {

    private static final long serialVersionUID = 1L;

    @Autowired
    private Map<String, DataSource> dataSourcesMtApp;

    /*
     * (non-Javadoc)
     * 
     * @see org.hibernate.engine.jdbc.connections.spi.
     * AbstractDataSourceBasedMultiTenantConnectionProviderImpl#selectAnyDataSource(
     * )
     */    @Override
    protected DataSource selectAnyDataSource() {
        return this.dataSourcesMtApp.values().iterator().next();
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.hibernate.engine.jdbc.connections.spi.
     * AbstractDataSourceBasedMultiTenantConnectionProviderImpl#selectDataSource(
     * java.lang.String)
     */    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        return this.dataSourcesMtApp.get(tenantIdentifier);
    }
}

File: CurrentTenantIdentifierResolverImpl.java

This class does the job of identifying the tenant. In the example application the tenant id is stored in a ThreadLocal variable which is accessed in this class and provided to Hibernate

package com.example.multitenancy;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.springframework.stereotype.Component;

import com.example.util.TenantContextHolder;

/**
 * Hibernate needs to know which database to use i.e. which tenant to connect
 * to. This class provides a mechanism to provide the correct datasource at run
 * time.
 * 
 * @see {@link com.example.util.TenantContextHolder}
 * @see {@link com.example.security.CustomAuthenticationFilter}
 * 
 * @author Sunit Katkar
 * @version 1.0
 * @since 1.0 (April 2018)
 */@Component
public class CurrentTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver {

    private static final String DEFAULT_TENANT_ID = "tenant_1";

    /*
     * (non-Javadoc)
     * 
     * @see org.hibernate.context.spi.CurrentTenantIdentifierResolver#
     * resolveCurrentTenantIdentifier()
     */    @Override
    public String resolveCurrentTenantIdentifier() {
        // The tenant is stored in a ThreadLocal before the end user's login information
        // is submitted for spring security authentication mechanism. Refer to
        // CustomAuthenticationFilter
        String tenant = TenantContextHolder.getTenant();
        return StringUtils.isNotBlank(tenant) ? tenant : DEFAULT_TENANT_ID;
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.hibernate.context.spi.CurrentTenantIdentifierResolver#
     * validateExistingCurrentSessions()
     */    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }

}

File: TenantContextHolder.java

When Spring Security parses the login form which has the extra ‘tenant’ field, the field value is important. This value is the tenant identifier which will Hibernate which data source to use to connect to the desired tenant database.

ThreadLocal is a good solution to storing this tenant identifier. Please refer to the javadocs for a detailed explanation and some example code to understand the concept if you are new to it.

The following diagram shows how the tenant id is retrieved from the login form and stored in a ThreadLocal variable and finally retrieved from this variable to identify the tenant database.

The example application uses the following code for a ThreadLocal variable

package com.example.util;


/**
 * When the end user submits the login form, the tenant id is required to
 * determine which database to connect to. This needs to be captured in the
 * spring security authentication mechanism, specifically in the
 * {@link UsernamePasswordAuthenticationFilter} implemented by
 * {@link CustomAuthenticationFilter}. This tenant id is then required by the
 * {@link CurrentTenantIdentifierResolver} implemeted by the
 * {@link CurrentTenantIdentifierResolverImpl}
 * 
 * <br/>
 * <br/>
 * <b>Explanation:</b> Thread Local can be considered as a scope of access, like
 * a request scope or session scope. It’s a thread scope. You can set any object
 * in Thread Local and this object will be global and local to the specific
 * thread which is accessing this object. Global and local at the same time? :
 * 
 * <ul>
 * <li>Values stored in Thread Local are global to the thread, meaning that they
 * can be accessed from anywhere inside that thread. If a thread calls methods
 * from several classes, then all the methods can see the Thread Local variable
 * set by other methods (because they are executing in same thread). The value
 * need not be passed explicitly. It’s like how you use global variables.</li>
 * <li>Values stored in Thread Local are local to the thread, meaning that each
 * thread will have it’s own Thread Local variable. One thread can not
 * access/modify other thread’s Thread Local variables.</li>
 * </ul>
 * 
 * @see https://dzone.com/articles/painless-introduction-javas-threadlocal-storage
 * @author Sunit Katkar
 * @version 1.0
 * @since 1.0 (April 2018)
 */public class TenantContextHolder {

    private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();

    public static void setTenantId(String tenant) {
        CONTEXT.set(tenant);
    }

    public static String getTenant() {
        return CONTEXT.get();
    }

    public static void clear() {
        CONTEXT.remove();
    }
}

These are the significant files for the application.

Modifying the Spring Boot main application class

Typically, the Spring Boot main application class is nothing special as it has a main method which starts the application. But there is a lot of Spring Boot magic going on behind the scenes. A lot of defaults are assumed, a lot of configurations are done automatically by detecting artifacts in the classpath, application properties file and pom.xml analysis.

If you are familiar with Spring Boot and JPA or even JDBC, then you know that any mention of spring jpa properties in the application properties file will cause Spring Boot to automatically configure the datasource. In this web application, we dont want Spring Boot to configure the data sources because we want to define multiple data sources and there is no default data source available when the application starts. Only when a user logs in with tenant information, the correct datasource needs to be used to connect to the tenant database.

So, using a simple exclude attribute to exclude the DataSourceAutoConfiguration class in the main @SpringBootApplication annotation, we can tell Spring Boot to leave data source configuration aside.

Since we have asked Spring Boot to not do some auto configuration related to data sources, it is a good idea to specifically ask Spring Boot to enable JPA Repositories using the @EnableJpaRepositories annotation.

package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

/**
 * @author Sunit Katkar
 * @version 1.0
 * @since 1.0 (April 2018)
 */@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class })
@EnableJpaRepositories("com.example.repository")
public class MultitenancyMySqlApplication {

    public static void main(String[] args) {
        SpringApplication.run(MultitenancyMySqlApplication.class, args);
    }
}

Sample SQL to create a tenant database on MySQL

Had this example been set up as a single database application and the property spring.jpa.hibernate.ddl-auto set to create or update, then the following three tables would be generated automatically by Hibernate based on the JPA entity definitions.

Table: User

CREATE TABLE `user` (
 `user_id` INT(11) NOT NULL,
 `password` VARCHAR(255) NOT NULL,
 `username` VARCHAR(255) NOT NULL,
 `active` BIT(1) NULL DEFAULT NULL,
 `tenant` VARCHAR(255) NULL DEFAULT NULL,
 PRIMARY KEY (`user_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;

Table: Role

CREATE TABLE `role` (
 `role_id` INT(11) NOT NULL,
 `role` VARCHAR(255) NULL DEFAULT NULL,
 PRIMARY KEY (`role_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;

Table: User_Role

This is the mapping table where users and roles are recorded

CREATE TABLE `user_role` (
 `user_id` INT(11) NOT NULL,
 `role_id` INT(11) NOT NULL,
 PRIMARY KEY (`user_id`, `role_id`),
 INDEX `FKa68196081fvovjhkek5m97n3y` (`role_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;

File: CustomUserDetailsServiceImpl.java

Spring Security provides a interface UserDetailsService which has just one method declared in it – loadUserByUsername which returns the UserDetails.  Now this UserDetails is used for authentication. In the example application code, the CustomUserDetails already defines the attributes for the User.

package com.example.security;

import java.util.HashSet;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Service;

import com.example.model.CustomUserDetails;
import com.example.model.Role;
import com.example.model.User;
import com.example.service.UserService;

/**
 * {@link CustomUserDetailsService} contract defines a single method called
 * loadUserByUsernameAndTenantname.
 * 
 * The {@link CustomUserDetailsServiceImpl} class simply implements the contract
 * and delegates to {@link UserService} to get the
 * {@link com.example.model.User} from the database so that it can be compared
 * with the {@link org.springframework.security.core.userdetails.User} for
 * authentication. Authentication occurs via the
 * {@link CustomUserDetailsAuthenticationProvider}.
 * 
 * @author Sunit Katkar
 * @version 1.0
 * @since 1.0 (April 2018)
 *
 */@Service("userDetailsService")
public class CustomUserDetailsServiceImpl implements CustomUserDetailsService {

    @Autowired
    private UserService userService;

    @Override
    public UserDetails loadUserByUsernameAndTenantname(String username, String tenant)
            throws UsernameNotFoundException {
        if (StringUtils.isAnyBlank(username, tenant)) {
            throw new UsernameNotFoundException("Username and domain must be provided");
        }
        // Look for the user based on the username and tenant by accessing the
        // UserRepository via the UserService
        User user = userService.findByUsernameAndTenantname(username, tenant);

        if (user == null) {
            throw new UsernameNotFoundException(
                    String.format("Username not found for domain, "
                            + "username=%s, tenant=%s", username, tenant));
        }

        Set<GrantedAuthority> grantedAuthorities = new HashSet<>();
        for (Role role : user.getRoles()) {
            grantedAuthorities.add(new SimpleGrantedAuthority(role.getRole()));
        }

        CustomUserDetails customUserDetails = 
                new CustomUserDetails(user.getUsername(), 
                        user.getPassword(), grantedAuthorities, tenant);
        
        return customUserDetails;
    }
}

Resources:

The complete source code is checked into GitHub. Its a standard Maven project which you can import into your IDE.

Conclusion

We hope now you have a much better understanding of multi-tenancy and having seen the example code. Now you can build a SaaS style ‘tenant per database’ multi-tenant web application with Spring Boot. You can to secure it with Spring Security.

Source