Project

General

Profile

Feature #4399

support session level "schema" switching

Added by Greg Shah over 4 years ago. Updated over 4 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Target version:
-
Start date:
Due date:
% Done:

0%

billable:
No
vendor_id:
GCD
version_reported:
version_resolved:

History

#1 Updated by Greg Shah over 4 years ago

When thinking about a multi-tenant approach, there are 3 possible approaches:

  • database instance per tenant
    • a separate physical database for each tenant
    • this is the current approach for FWD
    • no development is needed for this
    • extra management of instances
    • more flexible for dump/load
    • more secure
  • one database instance with a different schema for each tenant
    • one physical database contains multiple tenants
    • each tenant has its own schema
    • a schema is a kind of namespace that separates tables from one tenant from all other tenants
    • less secure, rights must be carefully managed
    • a "search path" can be set as part of a connection which will automatically select the right schema for all other usage of that connection
    • may perform better in some use cases
  • partitions by tenant ID
    • add explicit tenant ID column in every table
    • all query processing/access/edits must honor the current tenant ID
    • this is a kind of multiplexing of a common set of tables
    • least secure option
    • for very large tables, this may perform poorly since all indexing and access will have to scale to handle a table based on all customer data

This task is to implement the schema level support in FWD.

One possible approach is to implement the SET-TENANT() function to select the proper schema/set the search path properly in the connection processing. There is a question of how the connection pooling software handles these search paths.

Also available in: Atom PDF