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.
