qinfengge

Introduction#

Multi-tenancy is primarily used for data isolation and is commonly employed in SAAS systems, providing software as a service to enterprises. Each enterprise acts as a tenant, and each tenant can only see data within its own tenant. Therefore, data isolation is required for each tenant.

Data Isolation#

Multi-tenant data isolation solutions can be divided into three types:

DATASOURCE Mode: Independent databases
SCHEMA Mode: Shared database, independent schema
COLUMN Mode: Shared database, shared schema, shared tables

DATASOURCE Mode#

One tenant per database; this solution offers the highest level of user data isolation and security, but it is also the most costly.

DATASOURCE Mode

Advantages: Provides independent databases for different tenants, helping to simplify the design of data model extensions and meet the unique needs of different tenants; data recovery is relatively simple in case of failure.
Disadvantages: Increases the number of database installations, leading to higher maintenance and acquisition costs.

SCHEMA Mode#

Multiple or all tenants share a database, but each tenant has its own table.

SCHEMA Mode

Advantages: Provides a certain degree of logical data isolation for tenants with high security requirements, though not completely isolated; each database can support a larger number of tenants.
Disadvantages: Data recovery can be difficult in case of failure, as recovering the database will involve data from other tenants; there are certain difficulties in cross-tenant data statistics.

COLUMN Mode#

Shared database, shared data structure. Tenants share the same database and the same table, but tenant data is distinguished by the tenant_id field in the table. This is the mode with the highest level of sharing and the lowest level of isolation.

COLUMN Mode

Advantages: The lowest maintenance and acquisition costs, allowing for the maximum number of tenants supported by each database.
Disadvantages: The lowest level of isolation and security, requiring increased attention to security during design and development; data backup and recovery are the most challenging, requiring backup and restoration on a per-table and per-row basis.

Summary#

In most cases, DATASOURCE and COLUMN modes are the most commonly used.
Choose DATASOURCE mode when the budget is sufficient or when tenants have high security and isolation requirements.
In ordinary cases, COLUMN mode is sufficient, as it simplifies development and operations, providing services to the maximum number of tenants with the least number of servers.

Principle#

The following explains the implementation principle of multi-tenancy using the most commonly used COLUMN shared database mode.
You may have noticed that the only difference between this mode and a typical single-tenant application is the addition of a tenant_id field in the database table.
Yes, this field serves as a unique tenant identifier, and the program needs to use this field to determine which data belongs to each tenant.
You might wonder, why not just concatenate the tenant field when writing SQL?
Of course, you can do that, but it is clearly not elegant enough.

MyBatis Plus Multi-Tenant Plugin#

So how can we write elegant code to implement multi-tenancy?
MyBatis, as the most commonly used and familiar ORM framework for developers in China, provides us with a native multi-tenant plugin.
Official Multi-Tenant Plugin

Example

public interface TenantLineHandler {

    /**
     * Get the tenant ID value expression, only supports a single ID value
     * <p>
     *
     * @return Tenant ID value expression
     */
    Expression getTenantId();

    /**
     * Get the tenant field name
     * <p>
     * The default field name is: tenant_id
     *
     * @return Tenant field name
     */
    default String getTenantIdColumn() {
        // If this field is not fixed, please use SqlInjectionUtils.check to check security
        return "tenant_id";
    }

    /**
     * Determine whether to ignore concatenating multi-tenant conditions based on the table name
     * <p>
     * By default, all need to be parsed and concatenated with multi-tenant conditions
     *
     * @param tableName Table name
     * @return Whether to ignore, true: indicates ignore, false: needs to parse and concatenate multi-tenant conditions
     */
    default boolean ignoreTable(String tableName) {
        return false;
    }
}

Note:
Multi-tenancy != permission filtering, do not misuse it; tenants are completely isolated!!!
After enabling multi-tenancy, all SQL executed by methods will be processed.
Custom SQL should be written according to specifications (each table involved in SQL with multiple tables must have aliases, especially for inner joins, which must be written in standard inner join format).

When we use this plugin, the SQL concatenation operation is handled by the MyBatis-Plus framework, and its implementation is based on the pagination plugin (interceptor).

Advanced#

The above is just a simple example; next, I will expand the multi-tenant functionality using the ruoyi-vue-plus scaffolding.

Project Address

YML Configuration#

Add the following configuration to the YML configuration file

# Multi-tenant configuration
tenant:
    # Whether to enable multi-tenant mode
    enable: true
    # Tenant field name
    column: tenant_id
    # Table names that need to be checked for multi-tenancy
    includes:
      - sr_card
      - sr_room
      - sr_seat
      - sr_room_seat
      - sr_swiper
      - sys_oss

Write the corresponding configuration values in the component

@Data
@Component
@ConfigurationProperties(prefix = "tenant")
public class TenantProperties {

    /**
     * Whether to enable multi-tenant mode
     */
    private Boolean enable;

    /**
     * Tenant field name
     */
    private String column;

    /**
     * Table names that need to be checked for multi-tenancy
     */
    private List<String> includes;
}

MyBatis-Plus Configuration#

Then, you need to register the multi-tenant plugin in the MyBatis-Plus configuration file and write the plugin logic

    @Resource
    private TenantProperties tenantProperties;

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        if (tenantProperties.getEnable()) {
            // Multi-tenant plugin
            interceptor.addInnerInterceptor(tenantLineInnerInterceptor());
        }

        // Data permission processing
        interceptor.addInnerInterceptor(dataPermissionInterceptor());
        // Pagination plugin
        interceptor.addInnerInterceptor(paginationInnerInterceptor());
        // Optimistic locking plugin
        interceptor.addInnerInterceptor(optimisticLockerInnerInterceptor());
        return interceptor;
    }

First, introduce the configuration file, then register the multi-tenant plugin. It is important to note that the multi-tenant plugin should be placed before the pagination plugin.

Then, write the corresponding logic

public TenantLineInnerInterceptor tenantLineInnerInterceptor() {

        return new TenantLineInnerInterceptor(new TenantLineHandler() {
            @Override
            public Expression getTenantId() {
                try {
                    LoginUser loginUser = LoginUserShareMap.getLoginUser();
                    if (loginUser == null) {
                        System.err.println("Current user is not logged in!");
                        // In multi-tenant mode, tenant id is null
                        return new LongValue(0L);
                    } else {
                        // In multi-tenant mode, tenant id is obtained from the current user
                        return new LongValue(loginUser.getUserId());
                    }
                } catch (Exception e) {
                    throw new RuntimeException("Failed to get current logged-in user information!", e);
                }

            }

            @Override
            public String getTenantIdColumn() {
                // Corresponding column name for tenant ID in the database
                System.err.println("Current tenant column name: " + tenantProperties.getColumn());
                return tenantProperties.getColumn();
            }

            // This is a default method, returning false by default indicates that all tables need to concatenate multi-tenant conditions
            @Override
            public boolean ignoreTable(String tableName) {
                LoginUser loginUser = LoginUserShareMap.getLoginUser();
                // Check if logged in; if logged in, filter
                if (loginUser != null) {
                    // Check if the platform super administrator; if so, has all data permissions
                    if (!LoginHelper.isAdmin()) {
                        // Tables that need to filter tenants
                        List<String> includes = tenantProperties.getIncludes();
                        return !includes.contains(tableName);
                    }
                }
                return true;
            }
        });
    }

There are some points to note here:

1. By default, ruoyi-vue-plus reads configuration information from the database tables during project initialization, and since the framework uses sa-token for login verification, if you use sa-token to get the logged-in user during project initialization, it will report unable to obtain web context error.
2. You can use a component to save the login state; typically, we would use ThreadLocal, but note that this framework uses a thread pool, and using ThreadLocal may lead to unexpected errors. For example, in high concurrency or high load situations, if the user login thread is A, but the tenant judgment thread is B, the ThreadLocal of these two threads will be different.

Specific SQL Filtering#

Although the multi-tenant plugin provides filtering based on tables, it does not provide finer-grained filtering based on SQL.
However, it provides an annotation to achieve this.

It is important to note that if your MP version is 3.1.1 or below, using @SqlParser(filter=true) also requires configuration

# Enable SQL parsing cache annotation to take effect; if your MP version is 3.1.1 or above, this configuration is not needed
mybatis-plus:
  global-config:
    sql-parser-cache: true

You can use it in the mapper

public interface TenantMapper extends BaseMapper<Tenant> {
    /**
     * Custom Wrapper, @SqlParser(filter = true) annotation indicates that SQL parsing will not be performed, thus no additional tenant conditions.
     *
     * @return
     */
    @SqlParser(filter = true)
    @Select("SELECT count(5) FROM t_tenant ")
    public Integer myCount();
}

Multi-Tenant Plugin
Multi-Tenant Field Isolation
Multi-Tenant Solutions