Multitenant architecture patterns

 

Several patterns can help plan and build the data architecture for SaaS applications. A well-designed SaaS application can demonstrate scalability, configurability, zero downtime and multi-tenant efficiency. These qualities cannot be mutually exclusive. For example, optimizing for multitenant efficiency in a shared environment must not compromise the level of security safeguarding data access. A security pattern to resolve this conflict involves the use of “virtual isolation” mechanisms such as permission, SQL views and encryption.

Trusted database connections:

Access to data stored in databases is secured using one of two methods: impersonation and trusted subsystem account. The former enables users to access different database objects. The latter is for applications to connect to database using process identity and involves additional security to be implemented in the application itself. For multitenant applications where each tenant grants access to end user accounts, a hybrid approach is justified.

Secure database tables

This involves granting select, update, insert, delete on [TableName] for [UserName] and must be done once during the tenant provisioning process. It is appropriate for separate database and separate schema approaches.

Tenant View Filter:

SQL Views can be used to grant individual tenants access to some of the rows in each table, while preventing them from accessing other rows. A predicate is added to filter the records from say a SELECT statement. This predicate can use a built-in function to determine the security identifier of the user account accessing the database and matched with the column values corresponding to a tenant. Unlike secure database tables pattern, this uses shared schema with tenant qualification.

Tenant Data Encryption:

A way to further protect tenant data is by encrypting it within the database. Encryption can be done with both symmetric as well as asymmetric key. In symmetric cryptography, a key is used to encrypt and decrypt data. In asymmetric cryptography, two keys are used, namely, the private key and the public key. Data is encrypted with the public key but decrypted with the private key. Public key cryptography requires significant more computing power. A better approach might be to use a key wrapping system that combines the advantage of both systems.

Extensibility patterns include custom columns and preallocated fields.  Since different organizations have their own unique needs, some customizations are required. Preallocated fields is a technique to simply include a preset number of custom fields in every table. These additional fields are used differently by different tenants.

Custom fields are limited by their number. An alternative technique is to use tagging with name value pairs. When metadata defines separate labels and data types for each of the tenants’ custom fields, the data model can be extended arbitrarily. The main drawback is that it adds a level of complexity for database functions such as indexing, querying, and updating records.

Custom columns are those that can be added to the tenant’s tables directly. Custom rows can be added to a dedicated table without altering the data model for other tenants.

Data model extensions help only with the storage and not the operations. Any extension must be paired with a mechanism for integrating the additional fields into the application’s functionality.

Scalability patterns are useful for large scale enterprise software. Scalability is even more important because data belonging to all the customers must be supported. ISVs building on-premises software might be familiar with shifting minor leagues to majors, but the game also changes because the scope widens to supporting a vast user base. Databases can be scaled up or out and it is important to differentiate between scaling the application and scaling the data.