Data Connections - Multi Tenancy

One of the most important considerations is making sure that no single tenant can view another tenant’s data.

The most common data scenarios we encounter are.

A single database containing all tenants and the database has a tenant ID associated with it.
A single database containing all tenants and the database tables have a tenant ID associated with them.
A single database containing all tenants and each user connects to the database with a separate user logon, which restricts the data within the database layer itself.
A single database, that contains a separate schema for each tenant.
A separate database for each tenant.
A single database and each tenant has different tables / views.

Sometimes you will have a combination of the above, but it is more common for one of the solutions below to be used by a partner. I.E, do not think that you’d have to apply all the solutions below to implement multi-tenancy, it’s much simpler than that.

Single DB with Tenant ID

A common requirement is to call a procedure as a pre-query before any other query is executed on the database.

The Pre Query panel is only valid if the database is SQL Server.

The pre-query can call package procedures or database functions. The use of the variable [[TENANT_ID]] allows each individual user account to pass a distinct value. This is set on the user account.

Single DB – Tables have a Tenant ID Column

Method 1:

User Restriction. This is the most common, and probably the simplest way to apply tenancy within a database. Here you can use any column, or combination of columns within a single or multiple data connections to restrict data.

All queries run against the data connection with a user restriction will be forced to show only matching data. The restrictions force an entry in every query WHERE clause.

User restrictions also allow you to view lists or groups of data.

You can also use user restrictions in combination with user variables.

Method 2:

Each table within a data connection can have a WHERE clause restriction applied.

Again, here we have applied the restriction using a user variable [[TENANT_ID]].

Any queries that use items from the restricted table will have a filter added to the WHERE clause.

A single DB – Use different Database User

You can exchange the values in the User Name and the Password fields for user variables.

You then set the corresponding variables on the user account. Note all variable values are encrypted in pi, but passwords are additionally obfuscated.

Each time a user queries any objects in the data connection, a connection to the database is made user the user details held in the variable.

A Single DB with a schema per Tenant

Each table can have a variable used for its associated schema.

As with previous solutions the variable for the schema is applied to each user account.

A Single DB which implements tables or views per Tenant

This is closely related to the solution above.

Then there is a variable on the users account.

You can actually replace almost anything with user variables, so that could be column selects, or parts of any calculation.

Separate DB for each Tenant

All connection details can be replaced by variables, this includes the database server, the port it runs on and the database name, in addition to the user details as per previous solutions.

The variables are then set per user.