1. What is multi-tenant (or multitenancy)?
In multi-tenant software architecture—also called software multitenancy—a single instance of a software application (and its underlying database and hardware) serves multiple tenants (or user accounts). A tenant can be an individual user, but more frequently, it’s a group of users—such as a customer organization—that shares common access to and privileges within the application instance. Each tenant’s data is isolated from, and invisible to, the other tenants sharing the application instance, ensuring data security and privacy for all tenants.
2. Database strategies of multitenancy
2.1. Catalog-based multitenancy
In a catalog-based multitenancy architecture, each customer uses its own database catalog. Therefore, the tenant identifier is the database catalog itself.
Since each customer will only be granted access to its own catalog, it’s very easy to achieve customer isolation. Moreover, the data access layer is not even aware of the multitenancy architecture, meaning that the data access code can focus on business requirements only.
This strategy is advantageous when using a relational database system that doesn’t make any distinction between a catalog and a schema, like MySQL, for instance.
The disadvantage of this strategy is that it requires more work on the Ops side: monitoring, replication, and backups. However, with automation in place, this problem could be mitigated.
2.2. Schema-based multitenancy
In a schema-based multitenancy architecture, each custom uses its own database schema. Therefore, the tenant identifier is the database schema itself.
Since each customer will only be granted access to its own schema, it’s very easy to achieve customer isolation. Also, the data access layer is not even aware of the multitenancy architecture, meaning that, just like for catalog-based multitenancy, the data access code can focus on business requirements only.
However, if schemas are colocated on the same hardware, one tenant who runs a resource-intensive job might incur latency spikes in other tenants. Therefore, although data is isolated, sharing resources might make it difficult to honor the Service-Level Agreement.
2.3 Table-based multitenancy (TenantDiscriminatorColumn)
In a table-based multitenancy architecture, multiple customers reside in the same database catalog and/or schema. To provide isolation, a tenant identifier column must be added to all tables that are shared between multiple clients.
While on the Ops side, this strategy requires no additional work, the data access layer needs extra logic to make sure that each customer is allowed to see only its data and to prevent data leaking from one tenant to the other. Also, since multiple customers are stored together, tables and indexes might grow larger, putting pressure on SQL statement performance.
3. Multi-Tenancy Architecture in Cloud Invoice System
In the Send Cloud Invoice(SCI) service, we choose the Table-based multitenancy (TenantDiscriminatorColumn) approach because of its easy and quick setup and acceptable cost.
However, we have improved by adding different levels of protection from application to database
This is a layered architecture based on protection level:
In this architecture, we have 3 levels to control security incidents:
3.1 Level 1: Application protection level
Using Spring Interceptor to get tenant_id from Security Context (after login successfully). So if you are login to the system, then tenant_id will be passed to DelegatingDataSource to choose the right DB source
3.2. Level 2: Framework protection level
Using Hibernate interceptor and StatementInspector to detect missing tenant_id in SQL query. If any issues are detected, the application will throw an Exception and stop the process
The Hibernate StatementInspector is a functional interface that looks as follows:
The inspect method takes an SQL statement that’s about to be executed by Hibernate and allows you to modify the SQL statement and return it to the Hibernate StatementPreparer.
To register an implementation of the StatementInspector interface with Hibernate, you can use the hibernate.session_factory.statement_inspector configuration property which can take a StatementInspector Java object, a Class or a String object defining the class implementing the StatementInspector interface:
Example application.yml:
jpa:
properties:
hibernate:
session_factory:
statement_inspector: xx.moneyforward.xx.xx.MultiTenancyStatementInspectorBelow is the flow chart of level 2 (Hibernate interceptor)
3.3 Level 3: Database protection level
DB Level using Row Level Security: This level is final protection, for any reason makes a mistake from the application (passed wrong tenant_id) or human ( miss configuration) can pass levels 1 and 2, then will be rejected at this level.
In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. This feature is also known as Row-Level Security. By default, tables do not have any policies, so if a user has access privileges to a table according to the SQL privilege system, all rows within it are equally available for querying or updating.
How to apply:
- Storing Tenant Identifier:
Using thread-local to store TENANT_ID
public final class TenantContext {
public static final String KEY_TENANT_ID = "TENANT_ID";
private TenantContext() {
}
private static final InheritableThreadLocal<String> currentTenant = new InheritableThreadLocal<>();
public static void setTenantId(String tenantId) {
log.debug("Setting tenantId to " + tenantId);
currentTenant.set(tenantId);
}
public static String getTenantId() {
return currentTenant.get();
}
public static void clear() {
currentTenant.remove();
}
public static void runInTenantContext(String tenantId, ContextJob function) throws Exception {
var prevTenantId = TenantContext.getTenantId();
try {
setTenantId(tenantId);
function.work();
} finally {
if (prevTenantId != null) {
TenantContext.setTenantId(prevTenantId);
} else {
clear();
}
}
}
public interface ContextJob {
public void work() throws Exception;
}
}- Creating the
TenantAwareDataSource
After opening a connection to the DB exec command SET app.tenant_id TO '" + tenantId + "' to set tenant_id to the current connection
public class TenantAwareDataSource extends DelegatingDataSource {
public TenantAwareDataSource(DataSource targetDataSource) {
super(targetDataSource);
}
@Override
public Connection getConnection() throws SQLException {
Connection connection = null;
DataSource dataSource = getTargetDataSource();
if (dataSource != null) {
connection = dataSource.getConnection();
setTenantId(connection);
}
return getTenantAwareConnectionProxy(connection);
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
Connection connection = null;
DataSource dataSource = getTargetDataSource();
if (dataSource != null) {
connection = dataSource.getConnection(username, password);
setTenantId(connection);
}
return getTenantAwareConnectionProxy(connection);
}
private void setTenantId(Connection connection) throws SQLException {
try (Statement sql = connection.createStatement()) {
String tenantId = TenantContext.getTenantId();
sql.execute("SET app.tenant_id TO '" + tenantId + "'");
}
}
private void clearTenantId(Connection connection) throws SQLException {
try (Statement sql = connection.createStatement()) {
sql.execute("RESET app.tenant_id");
}
}
// Connection Proxy that intercepts close() to reset the tenant_id
protected Connection getTenantAwareConnectionProxy(Connection connection) {
return (Connection) Proxy.newProxyInstance(
ConnectionProxy.class.getClassLoader(),
new Class[]{ConnectionProxy.class},
new TenantAwareDataSource.TenantAwareInvocationHandler(connection));
}
}