Building Multi-Tenanted Applications - MS Access

Whether you are creating a demonstration of your product or building a product that can work for multiple companies, cloud computing makes us all think more carefully about the potential savings from starting to design our database to supporting multiple organizations. The popular terminology for building these applications is to make them multitenanted— that is, each tenant is a customer.

Most Access developers are used to using the table as the key method for users interacting with data and then layering queries on top of the table to deliver up data that is filtered or organized to meet a particular need. To move to a multi-tenanted architecture, you need to insert another layer on top of your tables that isolates each tenant’s information. The SQL Server terminology for a query is the View. You start with an architecture in which users do not have permissions on the underlying tables but will use a View, and this View will be filtered to only show the tenant data for that user.

User Tables and Views

Because we are going to end up with an increased number of objects to manage in our SQL Server database, we will use schemas to partition the design and manage security. A schema is a mechanism for grouping together a set of objects. Begin by constructing a schema to hold information about your users; use a separate schema for this because you might want to assign read/write permissions for users who could only have read permissions in other parts of the system. On this schema, you will create a filter table that will hold one record for each user in the system (MultiTenanted.sql):

Users do not have any permissions on the [Filtering_T] schema. This prevents them from seeing data that belongs to other tenants. The second step is to create another schema on which users will be granted read/write permissions. On this schema, you create a view through which users can edit data:

The use of the SUSER_SNAME() function restricts a user to seeing only their own record in the underlying table. To make it simpler for users to refer to the view without the need to specify schema information, you create a synonym, which provides a shortcut name for referring to the view:

These steps create the table and view shown in Figure.

Adding new users to the system requires either an administrator to enter data directly in the physical table or some other special processing that can directly access the table.

Adding new users to the system requires either an administrator to enter data directly in the physical table

The final step is to construct a function that can be used in other parts of the system to return the tenant identifier, as demonstrated in the following:

Application Tables and Views

When constructing the application, you should decide on a set of useful schemas to further split up the application. This step is not essential, but it is useful for improving the maintainability of the application. Each schema has an underscore and the letter T appended to the name; all of these schemas will contain the base tables on which no users will have any permissions. In the following example, you will construct a table for holding customer details for each tenant:

By adding the function as a default on the TenantID, when users enter new records, they are tagged appropriately. We have also added the TenantID to the primary key. It is also useful to add a trigger to this table that prevents any user from altering the TenantID to a value other than that which is allowed for their business:

When you construct a multi-tenanted design, all the primary keys must be composite and include the TenantID. This also means that all your foreign keys will be composite. In the following example, you will create a related table, Projects, which has the composite primary key ([TenantID,ProjectID]), and then add the foreign key relationship to the Customers table; the foreign key needs to be composite on ([TenantID],[CustomerID]):

The final step is to create a schema on which users will have permissions, and provide a view for managing the data:

The preceding tables and views and their interaction with the user specific tables are shown in Figure.


Using the filter table so that each user can work only with the data associated with their TenantID.

Using the filter table so that each user can work only with the data associated with their TenantID.

This technique can be extended easily, allowing users to make filtering choices that can then be applied in the system, as shown in the following:

Creating optional parameters by using the COALESCE function

In the previous example, when filtering by using the statement c.CustomerID = f.CustomerID, you would have a problem when no filter has been set and f.CustomerID was NULL. One solution to this problem is to use the COALESCE function, which returns the first non-null value from a list of choices. If instead you use WHERE c.Customer ID = COALESCE (f.Customer ID,c.Customer ID), then when a NULL is supplied for f.CustomerID, it will compare c.Customer ID to f.CustomerID, which is always true (unless c.Customer ID is NULL because the test NULL = NULL would return false; this you might find a surprising fact).

To extend this method to handle a NULL in the column c.CustomerID, you could use the test WHERE COALESCE (c.CustomerID, '') = COALESCE(f.CustomerID, c.CustomerID, ''). This means that if C.CustomerID and f.CustomerID are both NULL, you are comparing two empty strings, which will be true. If you had to perform the comparison on a numeric, you would change the empty string to compare 0 to 0. And if comparing a date, you could use any fixed date; for example, '1 january 2000' = '1 january 2000'.

Managing Security

Having organized all your tables into schemas, you can now look at granting security permissions on the schemas. To further simplify managing the security, you will create three database roles and assign permissions on the schemas to the database roles:

In the previous example, only the Administrator role is granted permissions on the schemas that contain the actual tables. The system stored procedure sp_addrolemember can be used to assign membership for a single user account or Windows group.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

MS Access Topics