Multi-tenant Database Query Isolation via Pre-defined Attributes

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

In cloud environments, multi-tenant database provisioning faces challenges in efficiently managing shared resources, particularly in identifying and isolating data for multiple tenants, which affects load balancing, accounting, log filtering, and problem determination.

Innovation Solution

The method involves determining a database server attribute as a tenant identifier, modifying database queries to include this identifier, and using it to query the database, thereby enabling efficient data isolation and management for multiple tenants without requiring significant changes to existing applications or infrastructure.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Loss of energy

If multiple tenants share the same database instance with a tenant ID column to identify rows, then cost is reduced, but data isolation and security become more complex to manage

Engineering Contradiction:

ImprovecostVSAvoiddata isolation complexity

Core Design Contradiction:

Loss of energyVSDevice complexity

Solution Approach 1:

The system pre-generates SQL query templates with placeholder parameters for tenant identification before actual queries are executed. These templates are prepared in advance with the correct structure and syntax, reducing the complexity of real-time query modification while maintaining data isolation.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

An intermediary layer (query template engine) is introduced between the application and database. This intermediary handles the complexity of tenant ID injection and query modification, shielding applications from direct complexity while enabling multi-tenant data isolation through parameterized queries.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Reliability

If each tenant has its own dedicated database table with a different schema, then data isolation is improved, but infrastructure complexity and costs increase

Engineering Contradiction:

Improvedata isolationVSAvoidinfrastructure complexity

Core Design Contradiction:

ReliabilityVSDevice complexity

Solution Approach 1:

A single database schema and table structure serves multiple tenants universally. The same database tables are shared across all tenants, with tenant identification achieved through parameterized queries rather than physical schema separation. This universal structure reduces infrastructure complexity while maintaining data isolation through logical separation.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

Instead of creating physical copies of schemas for each tenant, the system uses query template copying where standardized SQL templates with tenant-specific parameters are generated. This allows data isolation without duplicating the actual database infrastructure.

Inventive Principle:

Principle #26Copying

3Reliability

If tenant ID is injected into database queries dynamically, then data isolation is maintained, but query processing time increases

Engineering Contradiction:

Improvedata isolationVSAvoidquery processing time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

SQL query templates are pre-compiled and validated before runtime. The structural aspects of queries are prepared in advance, allowing only parameter substitution (tenant ID injection) during execution. This preliminary preparation significantly reduces the time required for dynamic query modification while maintaining data isolation.

Inventive Principle:

Principle #10Preliminary action

4Ease of operation

If existing database server attributes are used as tenant identifiers, then ease of operation is improved, but adaptability for different multi-tenant models is reduced

Engineering Contradiction:

Improveease of operationVSAvoidmulti-tenant model adaptability

Core Design Contradiction:

Ease of operationVSAdaptability or versatility

Solution Approach 1:

The system uses parameterized queries where tenant identification is achieved through changeable parameters rather than fixed schema attributes. This allows the same query template to adapt to different multi-tenant models (shared tables, dedicated tables, hybrid models) by simply changing the parameter values, maintaining both ease of operation and adaptability.

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS10528528B2Supporting multi-tenant applications on a shared database using pre-defined attributes

Publication Date: 2020.01.07 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

Exposing existing database server attributes that are used for load balancing, accounting, log filtering, problem determination, and end user identification as tenant identifiers. An example of such attribute is the values in existing client information fields that are available to applications for passing additional information to the database server via connections. These values are then used by the database server for enhanced operational functions of load balancing, accounting, log filtering, problem determination, and end user identification.