Proxy Service for Multi-tenant Database Access

Abstract

Abstract. The database of multi-tenant Software as a Service (SaaS) applications has challenges in designing and developing a relational database for multi-tenant applications. In addition, combining relational tables and virtual relational tables to make them work together and act as one database for each single tenant is a hard and complex problem to solve. Based on our multitenant Elastic Extension Tables (EET), we are proposing in this paper a multitenant database proxy service to combine multi-tenant relational tables and virtual relational tables, to make them act as one database for each single tenant. This combined database is suitable to run with multi-tenant SaaS single instance applications, which allow tenants designing their database and automatically configuring its behavior during application runtime execution. In addition, these applications allow retrieving tenants data by simply calling functions from this service which spare tenants from spending money and efforts on writing SQL queries and backend data management codes, and instead allowing them to focus on their business and to create their web, mobile, and desktop applications. The feasibility and effectiveness of the proposed service are verified by using experimental data on some of this service functions.

Multi-tenant Database Access Control

“…SELECT tr.table_column_id, tr.value, tr.table_row_id, tr.serial_id FROM table_row tr WHERE tr.tenant_id =1000 AND tr.db_table_id = 16 AND tr.table_row_id IN ( SELECT distinct tr.table_row_id FROM table_row tr WHERE tr.tenant_id = 1000 AND tr.db_table_id = 16 AND tr.table_column_id = 50 AND ( cast(value as numeric) > '9000' ) ) ORDER BY 3,4 LIMIT 800 OFFSET 0 [15] Q2: SELECT tr.table_column_id, tr.value, tr.table_row_id, tr.serial_id FROM table_row tr WHERE tr.tenant_id =1000 AND tr.db_table_id = 16 AND tr.table_column_id in (47,48,49) AND tr.table_row_id IN ( SELECT distinct tr.table_row_id FROM table_row tr WHERE tr.tenant_id = 1000 AND tr.db_table_id = 16 AND tr.table_column_id = 50 AND ( cast(value as numeric) > '9000' ) ) ORDER BY 3,4 LIMIT 800 OFFSET 0 [15] Q3:…”

“…The EETPS has designed and developed to serve multiple tenants on one instance application [15]. However, in this paper the aim of the experiments is evaluating the performance after applying the EETAC method on the EETPS for one tenant.…”

“…VII. EXPERIMENTS After developing the EETPS [15], we applied on this service the EETAC method that we propose in this paper, and we carried out two types of experiments to verify the practicability of applying our EETAC on the EETPS. We have evaluated the response time through invoking the EETPS functions, which converts multi-tenant queries into traditional database queries, instead of accessing the database directly.…”

“…1 ELASTIC EXTENSION TABLES (EET) [15] IV. ELASTIC EXTENSION TABLES PROXY SERVICE We have proposed a multi-tenant database proxy service to combine, generate, and execute tenants' queries by using a code base solution, which converts multi-tenant queries into traditional database queries, and execute these traditional database queries in any Relational Database Management System (RDBMS) [15]. This service has two objectives, first, to enable tenants' applications retrieve rows from CTTs, retrieve combined rows from two or more tables of CTTs and VETs, or retrieve rows from VETs.…”

“…Further, we propose an access control algorithm which allows users to access their data that stored in columns and rows, and granted to them. Furthermore, we ran two experiments to verify the practicability of granting a tenant's user accessibility on a tenant's table columns and rows, by using EETAC method and the proposed Elastic Extension Tables Proxy Service (EETPS) [15]. In these experiments, we found that the cost of executing a query for a user who is granted access to fewer numbers of the table columns or rows is less than the cost of a user who is granted access to more numbers of the table columns or rows.…”