Multi-tenant Database Secure Access via Query Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-tenant relational database architectures, developers face challenges in securely accessing tenant data without exposing sensitive information from other tenants, and they often need to navigate complex database structures to find the correct database containing the data they need.
Innovation Solution
A system that provides secure, point-and-click access to production data for a single tenant, allowing users to select accessible tables and data types without specifying particular columns, with features like hyperlink-driven drill-downs and automatically constructed SQL queries, ensuring secure access without prior knowledge of database structures or tenant data locations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If developers are given direct access to multi-tenant databases to access tenant data, then they can resolve problems and test solutions in real-time, but sensitive data from other tenants may be exposed
Solution Approach 1:
The system segments the multi-tenant database into isolated tenant contexts. When a developer queries data, the system automatically segments the query to include only the specified tenant's data by injecting tenant identification parameters into the query execution, preventing exposure of other tenants' data while maintaining real-time access capability.
Solution Approach 2:
The system introduces an intermediary layer between the developer and the multi-tenant database. This intermediary automatically manages tenant context by intercepting queries, adding necessary tenant identification parameters, and executing them within the correct tenant's security context, thus preventing direct exposure risks while enabling real-time access.
2Ease of operation
If developers need to access tenant data, they must open sessions to global databases to identify the specific database, then open another session to access the data, but this increases operational complexity
Solution Approach 1:
The system merges the tenant identification step and the data access step into a single unified operation. Instead of requiring separate sessions to first identify the database and then access data, the system combines these operations by automatically resolving tenant-to-database mapping within the same query execution context, eliminating the need for manual session management.
Solution Approach 2:
The system implements self-service by automatically performing the database identification and context setup that would otherwise require manual developer intervention. When a developer specifies a tenant, the system autonomously determines which database contains that tenant's data and configures the query execution context, eliminating the need for developers to manually open multiple sessions.
3Adaptability or versatility
If developers can access data without knowing the database structure, then accessibility is improved, but automatic query construction requires complex processing
Solution Approach 1:
The system introduces an intermediary query construction layer that translates high-level user requests into detailed SQL queries. This intermediary automatically retrieves database structure information, maps user-friendly data requests to the actual database schema, and constructs the appropriate queries without requiring developers to understand the underlying structure, while the system handles the complexity of query generation.
Solution Approach 2:
The system performs preliminary actions by pre-loading and caching database structure information, table relationships, and data dictionaries before developers need to access data. This preliminary preparation enables the system to quickly translate user requests into executed queries without requiring developers to understand the structure, as the structural mapping work has already been completed in advance.
Data Source
AI summary
Access to an individual tenant's data within a multi-tenant relational database architecture is provided, without unduly exposing data of other tenants. A list of accessible tables is presented to an authenticated individual, such as a developer. Specific information can be designated as sensitive and can be excluded from the information the developer can access. A specialized user interface provides secure point-and-click access to data by allowing selection of a set of higher-level data types to retrieve. Access to data from a related table can be enabled by providing a hyperlink that, when activated, retrieves a relevant record from the related table. Primary key values can also be rendered as hyperlinks that can activate a pop-up menu showing all tables that refer to that primary key value. In at least one embodiment, SQL queries are automatically constructed based on the user's selection of items from the pop-up menu.