CN112597169A - Method for realizing industrial software multi-tenant data isolation - Google Patents
Method for realizing industrial software multi-tenant data isolation Download PDFInfo
- Publication number
- CN112597169A CN112597169A CN202011608173.3A CN202011608173A CN112597169A CN 112597169 A CN112597169 A CN 112597169A CN 202011608173 A CN202011608173 A CN 202011608173A CN 112597169 A CN112597169 A CN 112597169A
- Authority
- CN
- China
- Prior art keywords
- sql script
- database
- tenant
- database table
- data isolation
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000002955 isolation Methods 0.000 title claims abstract description 39
- 238000000034 method Methods 0.000 title claims abstract description 23
- 238000013515 script Methods 0.000 claims abstract description 67
- 230000009191 jumping Effects 0.000 claims description 3
- 238000002372 labelling Methods 0.000 abstract 1
- 238000012545 processing Methods 0.000 description 6
- 238000012423 maintenance Methods 0.000 description 3
- 230000008569 process Effects 0.000 description 3
- 230000006872 improvement Effects 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000000605 extraction Methods 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 230000001105 regulatory effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
Abstract
The invention discloses a method for realizing multi-tenant data isolation of industrial software, which is characterized in that a filter technology is utilized to obtain an original SQL statement of user access data, whether the original SQL statement is a database table to be operated and isolated is judged, if yes, the SQL statement is modified and executed through a self-defined rule, so that the purpose of automatically adding labels to the multi-tenant data is realized, the efficiency of labeling the data is greatly improved, and the accuracy is ensured. The method is flexible and efficient, and can automatically add the tenant label to the SQL script only by a small amount of configuration work, thereby realizing the efficient isolation of the industrial software multi-tenant data.
Description
Technical Field
The invention relates to the field of data processing, in particular to a method for realizing multi-tenant data isolation of industrial software.
Background
Multi-tenant technology (multi-tenant technology), or multi-tenancy technology, is increasingly used in this age, and in some scenarios of the industry, multi-tenant technology is essential. The multi-tenant technology is a software architecture technology which is used for discussing and realizing how to share the same system or program components in a multi-user environment and can still ensure the isolation of data among users. In short, a plurality of tenants (users not associated with each other) use the same system, and each tenant realizes its own operation and does not affect each other.
In the process of implementing the multi-tenant technology, the storage of data is the most important thing, and the main solutions are the following three types:
(1) each independent database
The scheme is that each tenant is allocated with a separate database, so that the data of each tenant is isolated. The isolation level of the scheme is the highest, the security is the best, but the following aspects of server cost, operation and maintenance cost, upgrading cost and the like are also the highest.
(2) Shared databases, independent schema data structures
The data of each tenant is in a database, but each tenant is allocated with a schema for realizing data isolation. The isolation level of the scheme is lower than that of the first scheme, the safety is reduced, but the server cost, the operation and maintenance success and the upgrading cost are reduced.
(3) Shared database, shared schema and database table
According to the scheme, tenants share the same database and the same schema, the TenantId bound with users in a one-to-one mode is added to data in the database to distinguish and identify, the sharing degree is highest, the isolation level is lowest, and meanwhile the server cost, the operation and maintenance success and the upgrade cost are lowest.
In summary, the three solutions have advantages and disadvantages, and in general, the first solution has the highest isolation but the highest cost, the third solution has the lowest isolation but the lowest cost, and the second isolation and the cost are all in the middle.
Considering the practical situation of industrial software, the cost of the first scheme is high, many enterprises cannot afford the solution, the cost of the third scheme is the lowest, the enterprises are more inclined to the scheme, and meanwhile, the scheme is also very practical as long as effective data isolation measures are made.
In practical industrial software, the third scheme is widely used, which requires that the use of TenantId must be extremely strictly regulated to ensure effective isolation of data between different users. However, in the actual development process, adding TenantId manually for each database access requires a great labor cost, and has a potential risk of missing or adding errors. Therefore, the improvement of safe, efficient and flexible automatic TennantId addition is imperative, and meanwhile, the realization of the method can greatly improve the software safety and reduce the labor cost.
Disclosure of Invention
The technical problem to be solved by the invention is as follows: a method for realizing multi-tenant data isolation of industrial software is provided.
In order to solve the technical problems, the technical scheme adopted by the invention is as follows:
a method for realizing industrial software multi-tenant data isolation comprises the following steps: .
S1, initializing, and specifying a database table name needing data isolation to obtain a target database table vector T [ i ]; setting schema, setting a field tenant _ id corresponding to TennantId in a database table contained in T [ i ]; acquiring a TenantId corresponding to a login user;
s2, intercepting the original SQL script operated by the login user to the database, and analyzing the original SQL script to obtain a database table name Tx contained in the original SQL script;
s3, judging whether Tx is contained in the T [ i ], if yes, adding a tent _ id corresponding to a login user in data contained in the original SQL script, and generating a modified SQL script; otherwise, directly executing the next step;
and S4, executing the SQL script and operating the database.
Compared with the prior art, the invention has the following technical effects:
by utilizing the method, the automatic adding of the tenant _ id of all users can be realized by adding a small amount of codes in front of the main program, the efficiency of marking the data under the modes of sharing the database, sharing the schema and the database table is greatly improved, and the accuracy of marking is ensured.
The invention not only solves the workload problem caused by manually adding TennantId, the problems of adding by mistake, adding by omission, adding by little and the like, but also only adds a small amount of configuration work, thereby realizing the application of various conditions in a flexible and efficient configuration mode. Therefore, only a simple configuration rule is needed, the tenant label, namely the TennantId label, can be automatically added to the SQL script, and the high-efficiency implementation of the multi-tenant data isolation of the industrial software is guaranteed.
On the basis of the technical scheme, the invention can be further improved as follows.
Preferably, in S2, the original SQL script operated on the database by the login user is intercepted by using the existing filter technology.
The method has the advantages that the implementation method is simple, and the use threshold of developers and the corresponding learning cost are reduced.
The TenantId and T [ i ] of the logged-in user are preferably loaded into the system cache.
The further scheme has the advantages of avoiding the time of requesting the database for many times and reducing the access load to the system and the database.
Preferably, the step of obtaining the database table name Tx in the SQL script in S2 is as follows:
s2-1, taking the 'blank space' as a separator, and separating the original SQL script into a plurality of segments;
s2-2, searching the position of the next 'FROM' keyword in the original SQL script;
s2-3, judging whether the first section of SQL script behind the FROM is "(", if yes, executing the step S2-5, otherwise jumping to the step S2-4;
s2-4, judging whether the second section of SQL script behind the FROM is as, if so, taking the third section of SQL script behind the FROM as the name of the database table to be searched; otherwise, executing step S2-5;
s2-5, judging whether all the segments of the SQL script are searched, if not, returning to the step S2-2, otherwise, ending.
The beneficial effect of adopting the further proposal is that the syntax rule of the SQL statement is utilized to automatically obtain the database table name to be processed in the SQL script.
Drawings
FIG. 1 is a flow chart of a method of implementing multi-tenant data isolation for industrial software in accordance with the present invention;
FIG. 2 is a flow chart of the present invention for obtaining database table names from an SQL script.
Detailed Description
The principles and features of this invention are described below in conjunction with the following drawings, which are set forth by way of illustration only and are not intended to limit the scope of the invention.
Fig. 1 is a schematic structural diagram of an implementation method of multi-tenant data isolation of industrial software according to the present invention. The method for realizing the industrial software multi-tenant data isolation comprises the following steps:
s1, initializing, and specifying a database table name needing data isolation to obtain a target database table vector T [ i ]; setting schema, setting a field tenant _ id corresponding to TennantId in a database table contained in T [ i ]; acquiring a TenantId corresponding to a login user; loading the TennantId and the T [ i ] of the login user into a system cache can improve the access speed and reduce the access load on a system and a database;
s2, intercepting the original SQL script operated by the login user to the database, and analyzing the original SQL script to obtain a database table name Tx contained in the original SQL script;
intercepting an original SQL script operated by a login user on the database by using the existing filter technology in the S2;
s3, judging whether Tx is contained in the T [ i ], if yes, adding a tent _ id corresponding to a login user in data contained in the original SQL script, and generating a modified SQL script; otherwise, directly executing the next step;
and S4, executing the SQL script and operating the database.
The steps are as follows:
the step of acquiring the database table name Tx in the SQL script in S2 is as follows:
s2-1, taking the 'blank space' as a separator, and separating the original SQL script into a plurality of segments;
s2-2, searching the position of the next 'FROM' keyword in the original SQL script;
s2-3, judging whether the first section of SQL script behind the FROM is "(", if yes, executing the step S2-5, otherwise jumping to the step S2-4;
s2-4, judging whether the second section of SQL script behind the FROM is as, if so, taking the third section of SQL script behind the FROM as the name of the database table to be searched; otherwise, executing step S2-5;
s2-5, judging whether all the segments of the SQL script are searched, if not, returning to the step S2-2, otherwise, ending.
The basic principle of the invention is that only under the condition of conforming to the SQL grammar rule, the SQL script to be executed is added with the SQL script which conforms to the SQL grammar and contains the TennantId value according to the rule defined by the developer, the service of the SQL script is not influenced, thereby the normal operation of the service is not influenced, and the aim of multi-tenant data isolation is achieved.
The rules that are customized by the developer are customizable from the dimensions of the database. Under the dimension of the database, the invention uses the filter technology to obtain the SQL script, and carries out corresponding processing and execution on the SQL script, thereby realizing corresponding functions.
The invention needs to prepare a configuration file, and can configure the database table name needing data isolation or the database table name not needing data isolation in yml or properties configuration files according to the requirements of the user, wherein the two database table names can be configured in one type, and the difference is that when the SQL script is processed, one forward processing and one reverse processing are adopted, the database table name needing data isolation is provisionally selected and configured for description and understanding, and a plurality of table names can be separated by commas. Therefore, when the method is used, the corresponding configuration file can be found through the path of the configuration file, and the data in the configuration is further acquired. Furthermore, the value corresponding to TenantId also needs to be stored in a system cache for being acquired when in use. After the user successfully logs in the system, the system can acquire all information of the user according to the user account, including the corresponding TenantId value, and then put the TenantId value and the user information into a cache of the system frame. Therefore, when the value of the TenantId is obtained, the time for requesting the database for many times is effectively avoided, and the load on the system and the database is reduced. Meanwhile, when the database table is created, a field corresponding to the value of the TenantId is added to the database table needing data isolation, and the field is tentatively named as 'tentjd' for convenience of explanation and distinction from the TenantId.
Then, the SQL instruction of the tenant to access the database is intercepted, and the related SQL script information can be obtained by utilizing the existing open filter technology. The filter technology is an existing open technology, and the use of the filter technology can meet the use requirement of a current scene and can also reduce the use threshold of a developer and the corresponding learning cost.
Then, the intercepted SQL script is disassembled, and the table name of the database is extracted. According to the SQL general standard and syntax, the SQL script is spaced by spaces, so that the SQL script can be divided into N sections by the spaces. The extraction rule of the table name is as follows: finding all 'FROM' keywords in the SQL script, judging whether a first section behind the FROM is a left bracket or not, and skipping if the first section behind the FROM is the left bracket; if not, then judging whether the second section is 'as', if so, then judging the third section is the table name, if not, then considering the first section is the table name. The temporarily acquired table name is a.
And finally, judging whether the table a belongs to the database table name needing data isolation in the configuration file, if so, modifying the SQL script, and executing the modified SQL script, otherwise, directly executing the SQL script. Finding a corresponding configuration file according to a path of the configuration file, further obtaining a database table name needing data isolation in the configuration, matching in a character string comparison mode, if the matching is successful, judging whether a section next to the table name a in the SQL script is a ' where ' key ' or not, if so, splicing ' the table name a.tennant _ id ' after ' where ' the table name a.tennant _ id ' is a value and of TennantId in a system cache, if not, splicing ' the table name a.tennant _ id ' after ' where ' the table name a.tennant _ id ' is a value of TennantId in the system cache, sequentially searching all table names in the SQL script according to the rule for corresponding processing, and directly executing the processing. If the table names do not match, the original SQL script is directly executed.
The above embodiments only relate to query operations on a database, specifically to operations of inserting, deleting, and modifying the database, and the specific rules of the SQL statements are slightly different, and the corresponding modification modes of the original SQL statements are also slightly different.
In general, the process of automatic data isolation is that prior to background code accessing a database, information related to SQL scripts is obtained by using the existing filter technology, meanwhile, a corresponding TennantId value is obtained from cache information performed after a user system successfully logs in according to information of a current user, then, SQL is automatically processed according to a custom rule of a developer, an SQL script conforming to SQL syntax is automatically added to SQL needing data isolation, and then, the SQL script is executed; the SQL script which does not need data isolation is directly executed.
The invention not only solves the workload problem caused by manually adding TennantId, the problems of adding by mistake, adding by omission, adding by little and the like, but also only adds a small amount of configuration work, thereby realizing the application of various conditions in a flexible and efficient configuration mode. Therefore, the method can realize safe, efficient and flexible automatic addition of the TennantId only by simple configuration rules so as to ensure the efficient implementation of the multi-tenant data isolation of the industrial software.
The above description is only for the purpose of illustrating the preferred embodiments of the present invention and is not to be construed as limiting the invention, and any modifications, equivalents, improvements and the like that fall within the spirit and principle of the present invention are intended to be included therein.
Claims (4)
1. A method for realizing industrial software multi-tenant data isolation is characterized by comprising the following steps:
s1, initializing, and specifying a database table name needing data isolation to obtain a target database table vector T [ i ]; setting schema, setting a field tenant _ id corresponding to TennantId in a database table contained in T [ i ]; acquiring a TenantId corresponding to a login user;
s2, intercepting the original SQL script operated by the login user to the database, and analyzing the original SQL script to obtain a database table name Tx contained in the original SQL script;
s3, judging whether Tx is contained in the T [ i ], if yes, adding a tent _ id corresponding to a login user in data contained in the original SQL script, and generating a modified SQL script; otherwise, directly executing the next step;
and S4, executing the SQL script and operating the database.
2. The method for implementing multi-tenant data isolation of industrial software according to claim 1, wherein an original SQL script for operating the database by a login user is intercepted in S2 by using an existing filter technology.
3. The method for realizing the multi-tenant data isolation of the industrial software as claimed in claim 1 or 2, wherein TennantId and T [ i ] of a login user are loaded into a system cache.
4. The method for implementing multi-tenant data isolation of industrial software according to claim 1 or 2, wherein the step of obtaining the database table name Tx in the SQL script in S2 is as follows:
s2-1, taking the 'blank space' as a separator, and separating the original SQL script into a plurality of segments;
s2-2, searching the position of the next 'FROM' keyword in the original SQL script;
s2-3, judging whether the first section of SQL script behind the FROM is "(", if yes, executing the step S2-5, otherwise jumping to the step S2-4;
s2-4, judging whether the second section of SQL script behind the FROM is as, if so, taking the third section of SQL script behind the FROM as the name of the database table to be searched; otherwise, executing step S2-5;
s2-5, judging whether all the segments of the SQL script are searched, if not, returning to the step S2-2, otherwise, ending.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011608173.3A CN112597169A (en) | 2020-12-30 | 2020-12-30 | Method for realizing industrial software multi-tenant data isolation |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011608173.3A CN112597169A (en) | 2020-12-30 | 2020-12-30 | Method for realizing industrial software multi-tenant data isolation |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112597169A true CN112597169A (en) | 2021-04-02 |
Family
ID=75206233
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011608173.3A Pending CN112597169A (en) | 2020-12-30 | 2020-12-30 | Method for realizing industrial software multi-tenant data isolation |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112597169A (en) |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102456028A (en) * | 2010-10-27 | 2012-05-16 | 金蝶软件（中国）有限公司 | Multi-tenant-oriented data acquisition method, device and system |
| CN104462421A (en) * | 2014-12-12 | 2015-03-25 | 中国科学院声学研究所 | Multi-tenant expanding method based on Key-Value database |
| CN108920494A (en) * | 2018-05-21 | 2018-11-30 | 深圳市彬讯科技有限公司 | Isolation access method, server-side and the storage medium of multi-tenant database |
| CN110555030A (en) * | 2018-03-28 | 2019-12-10 | 北京京东尚科信息技术有限公司 | SQL statement processing method and device |
-
2020
- 2020-12-30 CN CN202011608173.3A patent/CN112597169A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102456028A (en) * | 2010-10-27 | 2012-05-16 | 金蝶软件（中国）有限公司 | Multi-tenant-oriented data acquisition method, device and system |
| CN104462421A (en) * | 2014-12-12 | 2015-03-25 | 中国科学院声学研究所 | Multi-tenant expanding method based on Key-Value database |
| CN110555030A (en) * | 2018-03-28 | 2019-12-10 | 北京京东尚科信息技术有限公司 | SQL statement processing method and device |
| CN108920494A (en) * | 2018-05-21 | 2018-11-30 | 深圳市彬讯科技有限公司 | Isolation access method, server-side and the storage medium of multi-tenant database |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US8244780B1 (en) | Parameter oriented automated database command | |
| US20130125098A1 (en) | Transformation of Computer Programs | |
| CN111008020B (en) | Method for analyzing logic expression into general query statement | |
| CN107077496B (en) | Systems, methods, and apparatus for index compilation to optimize index usage | |
| US7970757B2 (en) | Computer program product for database query optimization | |
| US8230406B2 (en) | Compiler option consistency checking during incremental hardware design language compilation | |
| CN110851514B (en) | ETL (extract transform load) processing method based on FLINK (Linear rotation injection) | |
| WO2021031583A1 (en) | Method and apparatus for executing statements, server and storage medium | |
| CN113051514A (en) | Element positioning method and device, electronic equipment and storage medium | |
| CN114356964A (en) | Data blood margin construction method and device, storage medium and electronic equipment | |
| CN111694846B (en) | Separation mode distributed storage process implementation method based on Type 2JDBC driver | |
| CN111949328A (en) | Starting acceleration method and device, computer equipment and storage medium | |
| CN112597169A (en) | Method for realizing industrial software multi-tenant data isolation | |
| CN116204550A (en) | Database query statement optimization method, storage medium and device | |
| CN116010461A (en) | Data blood relationship analysis method and device, storage medium and electronic equipment | |
| US10102115B2 (en) | Incremental program construction from embedded program code for external testing | |
| EP4113284A1 (en) | Cross-platform code conversion method and device | |
| CN113296786A (en) | Data processing method and device, electronic equipment and storage medium | |
| CN114461454A (en) | Data recovery method and device, storage medium and electronic equipment | |
| CN111400396A (en) | Method and device for generating data synchronization script and computer readable storage medium | |
| US10102109B2 (en) | Exception resolution in a software development session | |
| US11734021B2 (en) | Automated runtime service optimization via modification of a configuration file | |
| US11947931B2 (en) | Generic factory class | |
| US11449353B2 (en) | System and method for proprietary source code interpretation | |
| CN115129740B (en) | Method and system for updating distributed micro-service database in cloud native environment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20210402 |