Adding tenants without application restart in SaaS style multi-tenant web app with Spring Boot 2 and Spring Security 5

In my previous posts (part 1, part 2) about Saas style multi-tenant web applications, the focus was on how multi-tenancy is achieved.  In this post I will share how tenants can be added without restarting the application.

Update 4th Sep 2018

Many readers of this blog post have emailed me saying that they run the code but cannot login. This is because they have not populated the user table with username and password for a user nor have they created the necessary databases and schema. In many cases, the readers are not familiar with Spring Security. I request that you please read up on Spring Security first. Here is an excellent example from the official Spring documentation site.

Purpose of this blog post

In my previous posts, I showed how to read the details of the tenant databases from the application.yml file. The focus was on how to set up SaaS style database per tenant multitenancy.

In this post, I will show you how you can add the tenant information in a separate 'master' database and the multi-tenant application will pick up the existing tenants and any tenant added while your application is running. If you were to add a new tenant in the application.yml file, then you would have to restart the application. With the technique I am about to describe, you do not need to restart your application.

Structure of the application

In the previous posts, the application learnt about the tenants from the application.yml file. In this post, the tenant information is not learnt from the application.yml file but from a separate table in the master database.

The application.yml file is still required for application related properties and values, but the tenant database information need not be stored in it.

Datasources - One for the master database and one each for every tenant

The tenant information will be stord in the master database. For example, if the master database is called 'masterdb' and the table for tenant information is called 'master_tenant', then the information might look like this:

It is a simple table with the datasource related information about each tenant, like url, username, password, etc.

Order of setting up datasources and entity managers

It is important how the data sources are set up. Earlier, the tenant information was from the application.yml file. So the only datasources being setup were for each tenant.

The datasource for the 'master_tenant' table needs to be setup first as this table contains the tenant information. Once this datasource is set up, it can be used to read the tenant information and set up a datasource per tenant.

Please pay attention to the package structures. The master and tenant related datasource configurations are kept in separate packages and the master datasource is set up first.

The following source code listings show the way this is done.

File: MasterDatabaseConfig.java

This sets up the master datasource to connect to the masterdb database.

package com.sunitkatkar.blogspot.master.config;

import java.util.Properties;

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

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.dao.annotation.
        PersistenceExceptionTranslationPostProcessor;
import org.springframework.data.jpa.repository.config.
        EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.
        LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.
        HibernateJpaVendorAdapter;
import org.springframework.transaction.annotation.
        EnableTransactionManagement;

import com.sunitkatkar.blogspot.master.model.MasterTenant;
import com.sunitkatkar.blogspot.master.repository.
        MasterTenantRepository;
import com.zaxxer.hikari.HikariDataSource;

/**
 * Configuration of the master database which holds information about tenants in
 * the application.
 * 
 * @author Sunit Katkar, sunitkatkar@gmail.com
 *         (https://sunitkatkar.blogspot.com/)
 * @since ver 1.0 (May 2018)
 * @version 1.0
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = { "com.sunitkatkar.blogspot.master.model",
                                 "com.sunitkatkar.blogspot.master.repository" }, 
                       entityManagerFactoryRef = "masterEntityManagerFactory", 
                       transactionManagerRef = "masterTransactionManager")
public class MasterDatabaseConfig {

    private static final Logger LOG = LoggerFactory
            .getLogger(MasterDatabaseConfig.class);

    /**
     * Master database configuration properties like username, password, etc.
     */
    @Autowired
    private MasterDatabaseConfigProperties masterDbProperties;

    /**
     * Creates the master datasource bean which is required for creating the
     * entity manager factory bean <br/>
     * <br/>
     * Note that using names for beans is not mandatory but it is a good
     * practice to ensure that the intended beans are being used where required.
     * 
     * @return
     */
    @Bean(name = "masterDataSource")
    public DataSource masterDataSource() {

        LOG.info("Setting up masterDataSource with: "
                + masterDbProperties.toString());

        HikariDataSource ds = new HikariDataSource();

        ds.setUsername(masterDbProperties.getUsername());
        ds.setPassword(masterDbProperties.getPassword());
        ds.setJdbcUrl(masterDbProperties.getUrl());
        ds.setDriverClassName(masterDbProperties.getDriverClassName());
        ds.setPoolName(masterDbProperties.getPoolName());

        // HikariCP settings
        // Maximum number of actual connection in the pool
        ds.setMaximumPoolSize(masterDbProperties.getMaxPoolSize());

        // Minimum number of idle connections in the pool
        ds.setMinimumIdle(masterDbProperties.getMinIdle());

        // Maximum waiting time for a connection from the pool
        ds.setConnectionTimeout(masterDbProperties.getConnectionTimeout());

        // Maximum time that a connection is allowed to sit idle in the pool
        ds.setIdleTimeout(masterDbProperties.getIdleTimeout());
        LOG.info("Setup of masterDataSource succeeded.");
        return ds;
    }

    /**
     * Creates the entity manager factory bean which is required to access the
     * JPA functionalities provided by the JPA persistence provider, i.e.
     * Hibernate in this case. <br/>
     * <br/>
     * Note the <b>{@literal @}Primary</b> annotation which tells Spring boot to
     * create this entity manager as the first thing when starting the
     * application.
     * 
     * @return
     */
    @Primary
    @Bean(name = "masterEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean masterEntityManagerFactory(){
        LocalContainerEntityManagerFactoryBean em = 
                new LocalContainerEntityManagerFactoryBean();

        // Set the master data source
        em.setDataSource(masterDataSource());

        // The master tenant entity and repository need to be scanned
        em.setPackagesToScan(
                new String[]{MasterTenant.class.getPackage().getName(),
                        MasterTenantRepository.class.getPackage().getName()});
        // Setting a name for the persistence unit as Spring sets it as
        // 'default' if not defined
        em.setPersistenceUnitName("masterdb-persistence-unit");

        // Setting Hibernate as the JPA provider
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        // Set the hibernate properties
        em.setJpaProperties(hibernateProperties());
        LOG.info("Setup of masterEntityManagerFactory succeeded.");
        return em;
    }

    /**
     * This transaction manager is appropriate for applications that use a
     * single JPA EntityManagerFactory for transactional data access. <br/>
     * <br/>
     * Note the <b>{@literal @}Qualifier</b> annotation to ensure that the
     * <tt>masterEntityManagerFactory</tt> is used for setting up the
     * transaction manager.
     * 
     * @param emf
     * @return
     */
    @Bean(name = "masterTransactionManager")
    public JpaTransactionManager masterTransactionManager(
            @Qualifier("masterEntityManagerFactory") EntityManagerFactory emf) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
    }

    /**
     * Bean post-processor that automatically applies persistence exception
     * translation to any bean marked with Spring's @Repository annotation,
     * adding a corresponding PersistenceExceptionTranslationAdvisor to the
     * exposed proxy (either an existing AOP proxy or a newly generated proxy
     * that implements all of the target's interfaces).
     * 
     * @return
     */
    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }

    /**
     * The properties for configuring the JPA provider Hibernate.
     * 
     * @return
     */
    private Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.put(org.hibernate.cfg.Environment.DIALECT,
                "org.hibernate.dialect.MySQL5Dialect");
        properties.put(org.hibernate.cfg.Environment.SHOW_SQL, true);
        properties.put(org.hibernate.cfg.Environment.FORMAT_SQL, true);
        properties.put(org.hibernate.cfg.Environment.HBM2DDL_AUTO, "update");
        return properties;
    }
}

File: TenantDatabaseConfig.java

This class sets up the datasources for the tenant databases. Note that this class requires the 'MultiTenantConnectionProvider' for the tenant datasources. These datasources are configured in the 'DataSourceBasedMultiTenantConnectionProviderImpl' class, shown later on in this post.

package com.sunitkatkar.blogspot.tenant.config;

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

import javax.persistence.EntityManagerFactory;

import org.hibernate.MultiTenancyStrategy;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.sunitkatkar.blogspot.tenant.model.User;
import com.sunitkatkar.blogspot.tenant.repository.UserRepository;
import com.sunitkatkar.blogspot.tenant.service.UserService;

/**
 * This is the tenant data sources configuration which sets up the multitenancy.
 * 
 * @author Sunit Katkar, sunitkatkar@gmail.com
 *         (https://sunitkatkar.blogspot.com/)
 * @since ver 1.0 (May 2018)
 * @version 1.0
 */
@Configuration
@EnableTransactionManagement
@ComponentScan(basePackages = { "com.sunitkatkar.blogspot.tenant.repository",
        "com.sunitkatkar.blogspot.tenant.model" })
@EnableJpaRepositories(basePackages = {
        "com.sunitkatkar.blogspot.tenant.repository",
        "com.sunitkatkar.blogspot.tenant.service" }, 
        entityManagerFactoryRef = "tenantEntityManagerFactory", 
        transactionManagerRef = "tenantTransactionManager")
public class TenantDatabaseConfig {

    private static final Logger LOG = LoggerFactory
            .getLogger(TenantDatabaseConfig.class);


    @Bean(name = "tenantJpaVendorAdapter")
    public JpaVendorAdapter jpaVendorAdapter() {
        return new HibernateJpaVendorAdapter();
    }

    @Bean(name = "tenantTransactionManager")
    public JpaTransactionManager transactionManager(
            EntityManagerFactory tenantEntityManager) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(tenantEntityManager);
        return transactionManager;
    }

    /**
     * The multi tenant connection provider
     * 
     * @return
     */
    @Bean(name = "datasourceBasedMultitenantConnectionProvider")
    @ConditionalOnBean(name = "masterEntityManagerFactory")
    public MultiTenantConnectionProvider multiTenantConnectionProvider() {
        // Autowires the multi connection provider
        return new DataSourceBasedMultiTenantConnectionProviderImpl();
    }

    /**
     * The current tenant identifier resolver
     * 
     * @return
     */
    @Bean(name = "currentTenantIdentifierResolver")
    public CurrentTenantIdentifierResolver currentTenantIdentifierResolver() {
        return new CurrentTenantIdentifierResolverImpl();
    }

    /**
     * Creates the entity manager factory bean which is required to access the
     * JPA functionalities provided by the JPA persistence provider, i.e.
     * Hibernate in this case.
     * 
     * @param connectionProvider
     * @param tenantResolver
     * @return
     */
    @Bean(name = "tenantEntityManagerFactory")
    @ConditionalOnBean(name = "datasourceBasedMultitenantConnectionProvider")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            @Qualifier("datasourceBasedMultitenantConnectionProvider") 
            MultiTenantConnectionProvider connectionProvider,
            @Qualifier("currentTenantIdentifierResolver") 
            CurrentTenantIdentifierResolver tenantResolver) {

        LocalContainerEntityManagerFactoryBean emfBean = new LocalContainerEntityManagerFactoryBean();
        //All tenant related entities, repositories and service classes must be scanned
        emfBean.setPackagesToScan(
                new String[] { User.class.getPackage().getName(),
                        UserRepository.class.getPackage().getName(),
                        UserService.class.getPackage().getName() });
        emfBean.setJpaVendorAdapter(jpaVendorAdapter());
        emfBean.setPersistenceUnitName("tenantdb-persistence-unit");
        Map<String, Object> properties = new HashMap<>();
        properties.put(org.hibernate.cfg.Environment.MULTI_TENANT,
                MultiTenancyStrategy.SCHEMA);
        properties.put(
                org.hibernate.cfg.Environment.MULTI_TENANT_CONNECTION_PROVIDER,
                connectionProvider);
        properties.put(
                org.hibernate.cfg.Environment.MULTI_TENANT_IDENTIFIER_RESOLVER,
                tenantResolver);
        // ImprovedNamingStrategy is deprecated and unsupported in Hibernate 5
        // properties.put("hibernate.ejb.naming_strategy",
        // "org.hibernate.cfg.ImprovedNamingStrategy");
        properties.put(org.hibernate.cfg.Environment.DIALECT,
                "org.hibernate.dialect.MySQL5Dialect");
        properties.put(org.hibernate.cfg.Environment.SHOW_SQL, true);
        properties.put(org.hibernate.cfg.Environment.FORMAT_SQL, true);
        properties.put(org.hibernate.cfg.Environment.HBM2DDL_AUTO, "update");

        emfBean.setJpaPropertyMap(properties);
        LOG.info("tenantEntityManagerFactory set up successfully!");
        return emfBean;
    }
}

File: DataSourceBasedMultiTenantConnectionProviderImpl.java

This sets up the multi-tenant connection properties required by Hibernate. This is different than the code you have seen in the past 2 blog posts. Here, the datasources are read from the 'master_tenant' table and then stored in a map. This map is used to look up the datasource based on the tenant id. If a tenant is added to the 'master_tenant' table while the application is running, then a request for that tenant will prompt this class to read the 'master_tenant' table once again and recreate the datasource look up map. This is how the application becomes 'dynamic' about using a tenant added at runtime.

package com.sunitkatkar.blogspot.tenant.config;

import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.sql.DataSource;

import org.hibernate.engine.jdbc.connections.
    spi.AbstractDataSourceBasedMultiTenantConnectionProviderImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;

import com.sunitkatkar.blogspot.master.model.MasterTenant;
import com.sunitkatkar.blogspot.master.repository.MasterTenantRepository;
import com.sunitkatkar.blogspot.util.DataSourceUtil;

/**
 * This class does the job of selecting the correct database based on the tenant
 * id found by the {@link CurrentTenantIdentifierResolverImpl}
 * 
 * @author Sunit Katkar, sunitkatkar@gmail.com
 *         (https://sunitkatkar.blogspot.com/)
 * @since ver 1.0 (May 2018)
 * @version 1.0
 *
 */
@Configuration
public class DataSourceBasedMultiTenantConnectionProviderImpl
        extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {

    private static final Logger LOG = LoggerFactory
            .getLogger(DataSourceBasedMultiTenantConnectionProviderImpl.class);

    private static final long serialVersionUID = 1L;

    /**
     * Injected MasterTenantRepository to access the tenant information from the
     * master_tenant table
     */
    @Autowired
    private MasterTenantRepository masterTenantRepo;

    /**
     * Map to store the tenant ids as key and the data source as the value
     */
    private Map<String, DataSource> dataSourcesMtApp = new TreeMap<>();

    @Override
    protected DataSource selectAnyDataSource() {
        // This method is called more than once. So check if the data source map
        // is empty. If it is then rescan master_tenant table for all tenant
        // entries.
        if (dataSourcesMtApp.isEmpty()) {
            List<MasterTenant> masterTenants = masterTenantRepo.findAll();
            LOG.info(">>>> selectAnyDataSource() -- Total tenants:"
                    + masterTenants.size());
            for (MasterTenant masterTenant : masterTenants) {
                dataSourcesMtApp.put(masterTenant.getTenantId(), DataSourceUtil
                        .createAndConfigureDataSource(masterTenant));
            }
        }
        return this.dataSourcesMtApp.values().iterator().next();
    }


    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        // If the requested tenant id is not present check for it in the master
        // database 'master_tenant' table
        if (!this.dataSourcesMtApp.containsKey(tenantIdentifier)) {
            List<MasterTenant> masterTenants = masterTenantRepo.findAll();
            LOG.info(">>>> selectDataSource() -- tenant:" + tenantIdentifier
                    + " Total tenants:" + masterTenants.size());
            for (MasterTenant masterTenant : masterTenants) {
                dataSourcesMtApp.put(masterTenant.getTenantId(), DataSourceUtil
                        .createAndConfigureDataSource(masterTenant));
            }
        }
        return this.dataSourcesMtApp.get(tenantIdentifier);
    }
}

Resources

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

In conclusion

That's all there is to reading tenant information at run time. For a production grade application, I recommend that you create a separate Spring Boot app or a microservice to manage the tenant information in the master_tenant table. So this keeps the management of the application separate from the actual application. The master database can be used to store other kinds of app related information and the separate app can be used by the administrators for other app related activities.

Happy coding :)

Update - 10 July 2018

A reader of my blog - Jordan Mackie - found an error which was a mistake on my part in publishing a small but important part of the code. In the file TenantDatabaseConfig.java you need to explicitly name the tenant entity manager factory and use that when passing it in to the transaction manager. So the code should look like

@Bean(name = "tenantTransactionManager")
    public JpaTransactionManager transactionManager(
@Qualifier("tenantEntityManagerFactory")
EntityManagerFactory tenantEntityManager) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(tenantEntityManager);
        return transactionManager;
    }

Building SaaS style multi-tenant web app with Spring Boot 2 and Spring Security 5 - Part 1

Software as a Service or SaaS has been around for quite some time now.  But most of the time, developers are building single tenant applications as per requirements.  These applications have just one database and one web server at their core.

Purpose of this blog post

I wanted a solution where multi-tenancy is achieved by having a database per tenant and all user information (username, password, etc) for authentication and authorization stored in a user table in the respective tenant databases. It meant that not only did I need a multi-tenant application, but also a secure application like any other web application secured by Spring Security.

I know how to use Spring Security to secure a web application and how to use Hibernate to connect to a database. The requirement further dictated that all users belonging to a tenant be stored in the tenant database and not a separate or central database. This would allow for complete data isolation for each tenant.

So I did what most people do - I googled :)  I found many blog posts and articles published on multi-tenancy and web application security, but could not found a single solution or example implementation which addresses database per tenant approach along with security.  Maybe it is too trivial of a topic and hence no blogs or articles ;-)

So I just built my own!


The solution I describe below might sound a little complicated but if you are familiar with the basics of Spring Security and Hibernate, then its quite simple, albeit a bit tedious.

I have broken up this blog post into two parts as the code to set up multi-tenancy and spring security is quite a lot. I don't want you to keep scrolling down to finish reading the blog post. :) 

What is used to build the example in this blog post?

Here, I am going to share how I built a SaaS style web app using the latest Spring Boot 2, Spring JPA with Hibernate as the JPA provider to connect to MySQL and Spring Security 5 to secure the web aplication.

What is Multi-Tenancy?

A multi-tenant application is where a tenant (i.e. users in a company) feels that the application has been created and deployed for them. In reality, there are many such tenants and they too are using the same application but get a feel that its built just for them. Typical examples are online applications for time-sheet management, project management, Scrum/Agile Sprint management, etc etc.

A more technical explanation of multi-tenancy

SaaS applications are multi-tenant applications which require tenant data isolation in the database layer. There are different approaches for achieving this data isolation.
The most widely used approaches are

• Schema per tenant - all tenants have their own schema but these schema live in the same database 
• Discriminator per tenant - data for all tenants is in the same schema in the database but is distinguished by a tenant discriminator column
• Database per tenant - each tenant has a separate database

Which multi-tenancy approach is better?

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

In many applications, especially in financial and medical domains, there is a strict need for absolute data isolation, security and privacy in multi-tenant applications. As far as my experience,  knowledge and research on this topic goes, multi-tenancy using database per tenant is the best for data isolation and security. I may be wrong and your feedback is welcome.

How does the SaaS application work?

We will build a database per tenant multi-tenant application secured by Spring Security.
In most SaaS applications, there is an entry point where a user belonging to a tenant enters the

• tenant name (company name in most cases or some other identifier)
• username
• password

Step 1 - Process the login form with the extra 'tenant' field

• The login form will present the tenant name, username and password to Spring Security for authentication.  
• The Spring Security UsernamePasswordAuthenticationFilter filter intercepts the login form's request to the server. This filter needs to be extended so that the extra field 'tenant' can be extracted. 
• This tenant field is the tenant database identifier and is required by Hibernate to identify the tenant database to connect to.
• Once the tenant field is extracted by the security filter, it needs to be set in memory so that after the security filter is finished doing its job, this tenant identifier is available to the Hibernate code to connect to the correct tenant database. 
• To make this happen, the tenant field is stored in a ThreadLocal

Step 2 - Compare the login form values with existing user in the database

• Now that the username, password and tenant values are available to Spring Security, the AbstractUserDetailsAuthenticationProvider will use the retrieveUser() method to call upon the UserDetailsService to get the user details from the database. 
• If the credentials from the login form match those of the result returned by the tenant database, then the authentication is successful and Spring Security will now log the user into the application and create a secure session for the user for further access and interaction with the protected resources in the application. 

Step 3 - Select the correct tenant database

• Step 3 actually happens along side Step 2. 
• It means that Spring Security does its job of preparing user credentials for comparison with existing users in the database. 
• And Hibernate does its job of selecting the correct tenant database to provide such a user record to Spring Security for comparison. 

How does Step 3 work?

• In Step 2 above, Spring Security queries the database for a user based on the login form username and tenant field. So how does Hibernate know to get the user from the correct tenant database. In Step 1 note that the tenant field is stored in a ThreadLocal for later access. 

• Hibernate provides CurrentTenantIdentifierResolver for resolving or identifying the correct tenant id. The tenant id is extracted from the ThreadLocal storage.

• Hibernate also provides AbstractDataSourceBasedMultiTenantConnectionProviderImpl (also see DataSourceBasedMultiTenantConnectionProviderImpl) whose job is to provide the datasource corresponding to the tenant identifier.

How Spring Security and Hibernate process the login form

How Spring Security and Hibernate process the login form

Technology Stack

I am assuming that you are comfortable with Spring MVC, Spring Boot, JPA, Hibernate and Spring Security. The example application was built using -

• Spring Boot 2 (2.0.1.RELEASE)
• Spring Security 5 
• MySQL 
• Spring JPA (Spring Boot 2 provides latest Hibernate 5 as the JPA provider)
• Java 8
• Spring STS IDE

Understanding the code for the application

I will describe the significant code in this section. Rest of the code is available in the GitHub repository for this example application.  The link is in the Resources section at the end. If you want to just use the code and run it, then skip to the resources section at the end of part 2 for the GitHub link.  

Project Layout

This is how my project layout is in Spring STS

File: pom.xml

Using the Spring Initialzr create a project with Spring Web, Security, Thymeleaf, MySQL and JPA. It should generate a pom.xml as follows:

Note that I have added a few extra dependencies - 

• Cyber NekoHTML for setting ThymeLeaf in legacy mode. Why? Thymeleaf complains if there are no proper closing tags but HTML5 allows some attributes to not have closing tags. So the fix is to use this dependency. A detailed problem statement and its solution is on this stackoverflow thread. 
• Apache commons-lang-3 for StringUtils
• spring-boot-configuration-processor for helping with reading config properties. When you have annotations like @ConfigurationProperties, the STS IDE itself suggests that this dependency should be added.

This is the final pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>com.example</groupId>
 <artifactId>multitenancy-mysql</artifactId>
 <version>1.0.1</version>
 <packaging>jar</packaging>
 <name>multitenancy-mysql</name>
 <description>Spring Boot JPA Hibernate with Per Database Multi-Tenancy with Spring Security</description>
 <contributors>
  <contributor>
   <name>Sunit Katkar</name>
   <email>sunitkatkar@gmail.com</email>
   <url>https://sunitkatkar.blogspot.com/</url>
  </contributor>
 </contributors>

 <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.0.1.RELEASE</version>
  <relativePath /> <!-- lookup parent from repository -->
 </parent>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  <java.version>1.8</java.version>
 </properties>

 <dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-security</artifactId>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-thymeleaf</artifactId>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-configuration-processor</artifactId>
   <optional>true</optional>
  </dependency>
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-test</artifactId>
   <scope>test</scope>
  </dependency>
  <dependency>
   <groupId>org.springframework.security</groupId>
   <artifactId>spring-security-test</artifactId>
   <scope>test</scope>
  </dependency>
  <dependency>
   <groupId>net.sourceforge.nekohtml</groupId>
   <artifactId>nekohtml</artifactId>
   <version>1.9.21</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
  <dependency>
   <groupId>org.apache.commons</groupId>
   <artifactId>commons-lang3</artifactId>
   <version>3.7</version>
  </dependency>
 </dependencies>

 <build>
  <plugins>
   <plugin>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-maven-plugin</artifactId>
   </plugin>
  </plugins>
 </build>
</project>

File: application.yml

I have worked mostly with application.properties files, but found that the YAML format is easier to use when defining multiple data sources. In this example application, I have defined two data sources multitenancy.mtapp.dataSources which are pointing to 2 MySQL databases running on a MySQL instance.

spring:
  thymeleaf:
    cache: false
    mode: LEGACYHTML5
  jpa:
    database: mysql
    show-sql: true 
    generate-ddl: false
    hibernate: 
      ddl-auto: none  

multitenancy: 
  mtapp: 
    dataSources: 
      -
        tenantId: tenant_1 
        url: jdbc:mysql://localhost:3306/dbtenant1?useSSL=false 
        username: tenant1 
        password: admin123 
        driverClassName: com.mysql.jdbc.Driver 
      -
        tenantId: tenant_2
        url: jdbc:mysql://localhost:3306/dbtenant2?useSSL=false
        username: tenant1
        password: admin123
        driverClassName: com.mysql.jdbc.Driver        

Note that spring.thymeleaf.mode is set to LEGACYHTML5. The Cyber NekoHTML dependency will then take care of not being so strict about parsing HTML5 and ThymeLeaf will not complain.

File: CustomUserDetails.java

Note that Spring Security provides the interface UserDetails. An implementation of this interface requires details about the user to be authenticated. For this, Spring Security provides a User class which can be used as is or extended. The example application extends this org.springframework.security.core.userdetails.User class to add the tenant attribute to the User.

package com.example.model;

import java.util.Collection;

import org.springframework.security.core.GrantedAuthority;

/**
 * CustomUserDetails class extends the Spring Security provided
 * {@link org.springframework.security.core.userdetails.User} class for
 * authentication purpose. Do not confuse this with the {@link User} class which
 * is an entity for storing application specific user details like username,
 * password, tenant, etc in the database using the JPA {@literal @}Entity
 * annotation.
 * 
 * @author Sunit Katkar
 * @version 1.0
 * @since 1.0 (April 2018)
 *
 */
public class CustomUserDetails 
    extends org.springframework.security.core.userdetails.User {

    private static final long serialVersionUID = 1L;

    /**
     * The extra field in the login form is for the tenant name
     */
    private String tenant;

    /**
     * Constructor based on the spring security User class but with an extra
     * argument <code>tenant</code> to store the tenant name submitted by the end
     * user.
     * 
     * @param username
     * @param password
     * @param authorities
     * @param tenant
     */
    public CustomUserDetails(String username, String password, 
                Collection<? extends GrantedAuthority> authorities,
                String tenant) {
        super(username, password, authorities);
        this.tenant = tenant;
    }

    // Getters and Setters
    public String getTenant() {
        return tenant;
    }

    public void setTenant(String tenant) {
        this.tenant = tenant;
    }

}

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;
    }
}

File: CustomUserDetailsAuthenticationProvider.java

Spring Security provides class AbstractUserDetailsAuthenticationProvider which allows subclasses to override and work with UserDetails objects. The class is designed to respond to UsernamePasswordAuthenticationToken authentication requests.

The CustomUserDetailsAuthenticationProvider implemented for this application delegates to the CustomUserDetailsService (implemented by CustomUserDetailsServiceImpl) for retrieving the UserDetails for authentication

package com.example.security;

import org.springframework.security.authentication.BadCredentialsException;
import org.springframework.security.authentication.InternalAuthenticationServiceException;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider;
import org.springframework.security.core.AuthenticationException;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.util.Assert;

/**
 * {@link CustomUserDetailsAuthenticationProvider} extends
 * {@link AbstractUserDetailsAuthenticationProvider} and delegates to the
 * {@link CustomUserDetailService} to retrieve the User. The most important
 * feature of this class is the implementation of the <code>retrieveUser</code>
 * method.
 * 
 * Note that the authentication token must be cast to CustomAuthenticationToken
 * to access the custom field - tenant
 * 
 * 
 * @author Sunit Katkar
 * @version 1.0
 * @since 1.0 (April 2018)
 */
public class CustomUserDetailsAuthenticationProvider 
            extends AbstractUserDetailsAuthenticationProvider {

    /**
     * The plaintext password used to perform PasswordEncoder#matches(CharSequence,
     * String)} on when the user is not found to avoid SEC-2056
     * (https://github.com/spring-projects/spring-security/issues/2280).
     */
    private static final String USER_NOT_FOUND_PASSWORD = "userNotFoundPassword";

    /**
     * For encoding and/or matching the encrypted password stored in the database
     * with the user submitted password
     */
    private PasswordEncoder passwordEncoder;

    private CustomUserDetailsService userDetailsService;

    /**
     * The password used to perform
     * {@link PasswordEncoder#matches(CharSequence, String)} on when the user is not
     * found to avoid SEC-2056. This is necessary, because some
     * {@link PasswordEncoder} implementations will short circuit if the password is
     * not in a valid format.
     */
    private String userNotFoundEncodedPassword;

    public CustomUserDetailsAuthenticationProvider(PasswordEncoder passwordEncoder,
            CustomUserDetailsService userDetailsService) {
        this.passwordEncoder = passwordEncoder;
        this.userDetailsService = userDetailsService;
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.springframework.security.authentication.dao.
     * AbstractUserDetailsAuthenticationProvider#additionalAuthenticationChecks(org.
     * springframework.security.core.userdetails.UserDetails,
     * org.springframework.security.authentication.
     * UsernamePasswordAuthenticationToken)
     */
    @Override
    protected void additionalAuthenticationChecks(UserDetails userDetails,
            UsernamePasswordAuthenticationToken authentication) throws AuthenticationException {

        if (authentication.getCredentials() == null) {
            logger.debug("Authentication failed: no credentials provided");
            throw new BadCredentialsException(
                    messages.getMessage("AbstractUserDetailsAuthenticationProvider.badCredentials", 
                            "Bad credentials"));
        }
        // Get the password submitted by the end user
        String presentedPassword = authentication.getCredentials().toString();

        // If the password stored in the database and the user submitted password do not
        // match, then signal a login error
        if (!passwordEncoder.matches(presentedPassword, userDetails.getPassword())) {
            logger.debug("Authentication failed: password does not match stored value");
            throw new BadCredentialsException(
                    messages.getMessage("AbstractUserDetailsAuthenticationProvider.badCredentials", 
                            "Bad credentials"));
        }
    }

    @Override
    protected void doAfterPropertiesSet() throws Exception {
        Assert.notNull(this.userDetailsService, "A UserDetailsService must be set");
        this.userNotFoundEncodedPassword = this.passwordEncoder.encode(USER_NOT_FOUND_PASSWORD);
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.springframework.security.authentication.dao.
     * AbstractUserDetailsAuthenticationProvider#retrieveUser(java.lang.String,
     * org.springframework.security.authentication.
     * UsernamePasswordAuthenticationToken)
     */
    @Override
    protected UserDetails retrieveUser(String username, UsernamePasswordAuthenticationToken authentication)
            throws AuthenticationException {
        CustomAuthenticationToken auth = (CustomAuthenticationToken) authentication;
        UserDetails loadedUser;

        try {
            loadedUser = this.userDetailsService
                    .loadUserByUsernameAndTenantname(auth.getPrincipal().toString(),
                            auth.getTenant());
        } catch (UsernameNotFoundException notFound) {
            if (authentication.getCredentials() != null) {
                String presentedPassword = authentication.getCredentials().toString();
                passwordEncoder.matches(presentedPassword, userNotFoundEncodedPassword);
            }
            throw notFound;
        } catch (Exception repositoryProblem) {
            throw new InternalAuthenticationServiceException(repositoryProblem.getMessage(), 
                    repositoryProblem);
        }

        if (loadedUser == null) {
            throw new InternalAuthenticationServiceException(
                    "UserDetailsService returned null, "
                    + "which is an interface contract violation");
        }
        return loadedUser;
    }
}

Conclusion of Part 1

In this part of the blog post, I have explained how this application works, how to set up your project, how Spring Security is set up.

In the next part, I will show how the JPA and Hibernate part of the code is set up. Also I will show how Spring Security is tied in with the multi-tenancy Hibernate code.

Part 2