Synchronization and Data security for multitenant databases

This is a continuation of the articles on multitenancy with the most recent one linked https://1drv.ms/w/s!Ashlm-Nw-wnWhLZnYUBoDUNcjAHNwQ?e=NAo7vM. This article focuses on data synchronization and security. 

The customer’s data is stored in a separate business database, each of which is a tenant in the deployment. By separating application from data, the same stack can be deployed to many customers with centralized maintenance of the application and the isolation of each tenant.  The application database contains the tables that define an application.

Data and applications are partitioned and isolated from one tenant to another in a multitenant application. The application data is in a dedicated application database, separate from the business data in the tenant database. The revert to a single-tenant deployment mode requires the revert of the separation of application data and business data and the merging of the two databases. A single tenant can be folded from the multitenant mode and leaving the remaining tenants in the existing deployment by adding the application tables to the relevant tenant database and leaving the original application database unchanged. If the application data is separated into a dedicated database, but it is not used in a multitenant deployment mode, the databases can still be merged.

Tenant database must be synchronized with the application database in both modes of deployment – single tenancy as well as multitenancy.

Synchronization can occur in the following ways: 1) continuously, which is typical for transactional replication, 2) On demand, which is typical for merge replication. And 3) On a schedule, which is typical for snapshot replication. Replication happens by way of state or operations transfer which is like the well-known log shipping and mirroring options. State gets overwritten on the destinations whereas operations are re-executed on the destination. 

 

If there are ten customers to the current solution, each of them can get a tenant database dedicated to store the company’s business data. The knowledge about the shared application is then stored in a dedicated application database. When this solution is upgraded but the deployment mode is not changed, we can still have a single database that has one or more companies in it. We can also choose to extract the application tables to an application database but still have one business data database that has one or more companies in it. 

 

A security system must be in place to control which objects or tables a user can access within each database. We can specify the type of access each user has to these objects and tables and whether they are able to read, modify or enter data. Permissions can be granted at both the table level and the record level. It should be easy to query the permissions assigned to a user and to find out who can access a particular database. Typically, there are four different levels of security – Database, Company, Object, and Record. The first layer of security is database security when the credentials are checked even before the database is opened. Logins can be native to the database server or integrated with the operating system. The user is granted access only after the login is authenticated. Authorization is determined from role-based access control. A database can have several companies. Each company can use its own tables or share the tables with other companies. User’s access to companies is controlled by permissions sets. Object level security is determined by the permissions within the permission set. Permissions can be defined for all types of objects such as table data, table, pages, report, code units, data import and export units, query, and system. The lowest level of security is the record level security which limits the access that a user has to the data in a table. It is implemented by creating security filters on table data.