...
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 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.
...
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.
...
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.
...
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.
...