Database statement rewriting method, storage medium, electronic equipment and product

Abstract

The invention provides a database statement rewriting method, a storage medium, electronic equipment and a product, and the method comprises the steps that an original database statement corresponding to a user is acquired, and at least one target filtering condition used for representing the data access permission of the user in a database is missed in the original database statement; acquiring authority information of the user from an authority access list corresponding to the database, wherein the authority information represents a data access authority of the user in the database; and rewriting the original database statement into a target database statement according to the permission information, wherein the target database statement comprises the target filtering condition.

Description

TECHNICAL FIELD

[0001] The present specification relates to the technical field of computer, and particularly relates to a database statement rewriting method, a storage medium, an electronic device and a product. BACKGROUND

[0002] With the application and development of big data technology, data has become a key resource for decision analysis in various industries. Among them, considering that data is usually stored in the corresponding database, and the data often contains sensitive information, therefore, when a user accesses specific data in the database, it is required that one or more filtering conditions for representing the user's data access permission in the database are included in the related database statement, so as to ensure that the data accessed by the user matches the user's data access permission, and thus ensure data security.

[0003] In the related art, when at least one of the above filtering conditions is missing in the database statement corresponding to the user, the missing filtering condition is usually determined according to the correspondence between the user's identity information and the filtering condition, and then the original database statement and the determined filtering condition are spliced to obtain a complete database statement. However, this method completely depends on the pre-defined correspondence, and once the user's actual data access permission changes dynamically, the above correspondence cannot be modified in time, resulting in that the determined filtering condition and the spliced database statement also do not have reliability, and thus the risk of data leakage is generated. SUMMARY

[0004] Therefore, the present specification provides a database statement rewriting method, a storage medium, an electronic device and a product to solve the deficiencies in the related art.

[0005] Specifically, the present specification is realized through the following technical solutions:

[0006] According to a first aspect of an embodiment of the present specification, a database statement rewriting method is provided, and the method comprises:

[0007] Obtaining an original database statement corresponding to a user, wherein at least one target filtering condition for representing the user's data access permission in the database is missing in the original database statement;

[0008] Obtaining the user's permission information from the permission access list corresponding to the database, wherein the permission information represents the user's data access permission in the database;

[0009] Rewriting the original database statement into a target database statement according to the permission information, wherein the target database statement contains the target filtering condition.

[0010] According to a second aspect of the embodiments of the present specification, a computer readable storage medium is provided, and the computer readable storage medium stores a computer program, and the computer program is executed by a processor to implement the steps of the method according to the first aspect.

[0011] According to a third aspect of the embodiments of the present specification, an electronic device is provided, and the electronic device comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor implements the steps of the method according to the first aspect when executing the program.

[0012] According to a fourth aspect of the embodiments of the present specification, a computer program product is provided, and the computer program product comprises computer programs / instructions, and the computer programs / instructions are executed by a processor to implement the steps of the method according to the first aspect.

[0013] In the technical solutions provided in the present specification, when an original database statement missing at least one target filtering condition is obtained, the permission information of the user can be obtained from the access control list (ACL) corresponding to the database, and the original database statement is rewritten into a target database statement containing the target filtering condition according to the permission information. The permission information can represent the data access permission of the user in the database, and the modification operation on the data access permission essentially acts on the access control list itself. Therefore, even if the actual data access permission of the user changes, the permission information obtained from the access control list can still represent the changed data access permission in a timely manner, so that the rewritten target database statement is reliable, thereby effectively avoiding the risk of data leakage.

[0014] It should be understood that the above general description and the following detailed description are only exemplary and explanatory, and cannot limit the present specification. BRIEF DESCRIPTION OF DRAWINGS

[0015] In order to more clearly illustrate the technical solutions in the embodiments of the present specification or the prior art, the drawings needed in the embodiment or prior art description will be briefly introduced below. Obviously, the drawings in the following description are only some embodiments described in the present specification, and other drawings can also be obtained by those skilled in the art based on these drawings.

[0016] Figure 1 is a schematic diagram of a database statement rewriting system according to an exemplary embodiment of the present specification;

[0017] Figure 2 is a flowchart of a database statement rewriting method according to an exemplary embodiment of the present specification;

[0018] Figure 3ais a schematic diagram of an original abstract syntax tree shown in an exemplary embodiment of the present specification;

[0019] Figure 3b is a schematic diagram of a target abstract syntax tree shown in an exemplary embodiment of the present specification;

[0020] Figure 4 is a flowchart of another rewriting method of a database statement shown in an exemplary embodiment of the present specification;

[0021] Figure 5a is a schematic diagram of another original abstract syntax tree shown in an exemplary embodiment of the present specification;

[0022] Figure 5b is a schematic diagram of another target abstract syntax tree shown in an exemplary embodiment of the present specification;

[0023] Figure 6 is a schematic structural diagram of an electronic device shown in an exemplary embodiment of the present specification;

[0024] Figure 7 is a structural schematic diagram of a database statement rewriting device shown in an exemplary embodiment of the present specification. DETAILED DESCRIPTION

[0025] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) involved in the present specification are information and data authorized by the user or authorized by all parties, and the collection, use and processing of related data need to comply with relevant national and regional laws, regulations and standards, and provide corresponding operation portal for user to choose authorization or refusal.

[0026] In the traditional data query process, the user's database operation is usually limited by specific function services, which means that the user can only access the data they are authorized to access. The ability of such permission control can be achieved in the following several mainstream ways:

[0027] 1. Role-Based Access Control (RBAC)

[0028] In the RBAC model, permissions are not assigned directly to individual users, but to roles that users play. Users can be assigned one or more roles, each of which is associated with a predefined set of data access permissions that can be defined according to the user's job responsibilities, department, or other relevant factors. In this model, a user's data access permissions are bound to specific database statements or views, ensuring that when the user performs a database operation, they are actually performing a predefined database operation that already contains permission control. This binding mechanism allows the model to dynamically apply the corresponding permission control when the user performs an operation, ensuring the security and compliance of data access.

[0029] 2. Access control based on access control list

[0030] As a security mechanism, ACL can achieve fine-grained access control over database resources such as data rows. Specifically, ACL can assign data access permissions to different users, thereby explicitly specifying which users can perform specific database operations on specific database resources such as tables or views, for example, setting ACL for a user to only allow it to perform query operations on a specific table, while prohibiting data insertion, update, or deletion.

[0031] 3. Access control at the database level:

[0032] In a database system, security policies can be defined through the function of row-level security (RLS), which can dynamically restrict access to specific data rows when a specific user performs a database operation. It is important to note that the so-called specific user in the above security policy refers to a specific account used by the administrator to actually access the database, which is fundamentally different from the ordinary account generated by the user at the functional level. Even if each ordinary account is considered an independent specific account, the large number of accounts and maintenance costs in actual scenarios will make the above RLS not feasible. In short, unlike ACL, the above RLS cannot achieve row-level access control for ordinary accounts.

[0033] 4. Predefined access control

[0034] For the above functional services, database statements can be created that already contain the necessary filter conditions, thereby ensuring that users can only access the data they are authorized to access. Among them, the predefined database statements are static, in other words, they are determined at least during the development phase of the above functional services, and data access is statically limited according to the user's role. In addition, these database statements can also be implemented as database views, stored procedures or functions in application code, all of which have embedded the necessary filter conditions, so that the user cannot further expand the scope of the data they can operate.

[0035] 5. Application-level access control:

[0036] Before executing the database statement formally, it can be determined whether the user has the right to access the requested data through security technologies such as Open Authorization (OAuth) and JSON Web Tokens (JWT). If the user does not have sufficient data access permissions, the database statement can be directly rejected for execution or an exception prompt result can be returned.

[0037] The above methods can usually be used in combination to form a complete access control strategy to achieve secure access control of data, for example, RBAC can be used to manage user roles and basic permissions, and ACL can be used for more fine-grained access control to specific resources, such as table-level, column-level, or even row-level access control. Of course, in addition to the above methods, other methods such as Attribute-Based Access Control (ABAC) and network and physical layer access control can also be used to achieve the above permission control capabilities, which will not be listed one by one in this specification.

[0038] In summary, if the existing access control strategy is required to achieve row-level access control, the user's database operation needs to be intercepted for secure access based on the pre-defined user, role or attribute in the permission control system established based on ACL. Among them, ACL can directly map the corresponding data access permissions in the form of filter conditions to the database statements corresponding to the database operations, however, this case is limited to static database statements, that is, the above database statements are determined at the time of program compilation. Once the above database statement is dynamic, that is, it is constructed at the time of program execution, the above access control strategy cannot predefine the database statement, so it cannot map the corresponding data access permissions to the above database statement, resulting in that the user can access the private data that is not authorized to access through the database statement, which damages the security of the data.

[0039] It should be noted that the above dynamic database statement is usually applied to a natural language to structured query language (NL2SQL) scenario, for example, a user proposes a data query requirement to an intelligent customer service assistant, personalized recommendation, a personal artificial intelligence (AI) assistant, etc. through the form of daily conversation, and the above object helps the user query the corresponding data in the database through the conversation content. The technical principle of the scenario is to generate a corresponding database statement through the understanding of a large language model (LLM) for natural language expression for querying data, and then execute the corresponding database operation through the database statement. Among them, the above database statement is completely generated based on the natural language expression, so there is no filtering condition for representing the user's data access permission in the database.

[0040] To solve the defects of the above access control strategy for dynamic database statements, a custom plug-in based on a big data processing engine such as Flink can be used to implement row-level access control for dynamic database statements. The technical principle is to predefine the correspondence between user identity information and filtering conditions, which can be as shown in Table 1. For example, when user A executes a structured query language (SQL) statement "SELECT * FROM orders;", the processing engine can determine the corresponding row-level permission condition from the correspondence according to the username "user A" and the table name "orders" in the SQL statement to rewrite the SQL statement. The final SQL can be "SELECT * FROM orders WHERE region = 'beijing'".

[0041] Table 1 Correspondence table of custom plug-in

[0042] User name Table name Row-level permission condition User A orders region='beijing' User B orders region='hangzhou'

[0043] However, the main disadvantage of the above method is poor real-time performance of the rewritten result. The reason is that the above processing engine and the ACL corresponding permission control system are independent of each other and cannot realize data intercommunication. Therefore, once the user's username is renamed or the corresponding data access permission is changed in the ACL, the above correspondence will cause rewriting errors of the database statement until it is manually modified, which in turn causes the data accessed by the user to be inconsistent with the user's data access permission.

[0044] In addition, other disadvantages of the above method are poor flexibility, i.e., the rewriting of the database statement is limited to simple splicing between texts, and if the data column is processed by alias in the database statement, directly adding the row-level permission condition will result in an unexecutable database statement. Taking the above SQL "SELECT * FROM orders;" as an example, assuming that the data column is set to an alias t1, the SQL "SELECT * FROM orders t1;" is obtained, and the rewritten SQL statement is expected to be "SELECT * FROM orders t1 WHERE t1.region = 'beijing;'". However, the above correspondence cannot perform additional recognition processing on the alias, so that the row-level permission condition cannot be dynamically modified according to the alias during the rewriting process. This problem is more prominent in the scenario where multiple aliases exist.

[0045] To solve the above problems, a database statement rewriting method is introduced in the specification, and the technical solution is as follows:

[0046] Figure 1 FIG. 1 is a schematic diagram of an architecture of a database statement rewriting system according to an example embodiment of the present specification, as shown in the figure, the system can include a server 11, a network 12, a plurality of electronic devices 13 (for example, a PC (Personal Computer), a mobile phone, a server) and the like. Figure 1

[0047] The server 11 can be an object that needs to perform a database statement rewriting operation. The server 11 can be a physical server containing a standalone host, or the server 11 can be a virtual server carried by a host cluster.

[0048] In the technical solution of one or more embodiments of the present specification, the server 11 can be deployed with related functions for implementing the rewriting method of the database statement in the present embodiment, for example, a server program for rewriting the database statement can be run to rewrite the database statement from the electronic device 13. The server 11 can obtain the original database statement of the user, obtain the permission information of the user from the permission access list corresponding to the database, and rewrite the original database statement into a target database statement containing a target filtering condition according to the permission information. Wherein, the original database statement can be directly generated by the electronic device 13 and sent to the server 11, or generated locally by the server 11 according to the related information sent by the electronic device 13, which is not limited in the present specification.

[0049] ​In addition, the interaction of the electronic device 13 with the server 11 can specifically rely on data transmission between the electronic device 13 and the server 11, which can be implemented through a network 12, which can include various types of wired or wireless networks, and the present specification does not limit this.

[0050] Figure 2 is a flow diagram of a database statement rewriting method according to an exemplary embodiment of the present specification, which specifically includes the following steps:

[0051] In step S202, an original database statement corresponding to a user is obtained, and the original database statement is missing at least one target filtering condition for representing the data access permission of the user in the database.

[0052] For the above server, it is necessary to first obtain the original database statement corresponding to the user, and then perform targeted rewriting operation. Since the above original database statement is missing at least one of the above target filtering conditions, in theory, it can access part of the data in the database that is not allowed to be accessed by the user, and even in the case where no target filtering condition is included, the user can access any data in the database, so it is necessary to limit the data range that can be accessed by the original database statement based on the data access permission of the user in the database. Among them, the above original database statement can be implemented in mainstream SQL language, or procedural extended SQL language (Procedural Language / SQL, PL / SQL), transaction SQL language (Transact-SQL, T-SQL), and non-relational database query language (NoSQL Queries) according to the specific type of the database and the different use scenarios, and the present specification does not limit the comparison.

[0053] The above original database statement can be obtained in the following different ways.

[0054] In an embodiment, the above original database statement can be directly written by the user, and sent to the server by running a client program for rewriting the database statement in the corresponding electronic device. In this embodiment, since the above original database statement has been artificially defined, it is not necessary to consume additional device resources to determine the original database statement, but considering the high cost and difficulty of manually writing the database statement by the user, and the correctness cannot be guaranteed, therefore it is usually only suitable for technical test scenarios.

[0055] In another embodiment, the requirement information input by the user based on the natural language can be acquired, and then the original database statement is determined according to the analysis result of the natural language corresponding to the requirement information. In this embodiment, the process of acquiring the original database statement corresponds to the above-mentioned NL2SQL scenario. Specifically, the process of obtaining the original database statement based on the requirement information can be implemented based on the natural language processing (NLP) technology. For example, the client of the company's office software installed in the mobile phone of the user A has an AI assistant for interaction based on the dialogue. Then, the user A can first input the requirement information "Can you help me find the total contract income of the department A last year?" in the dialogue interface of the AI assistant. Through the analysis of the NLP technology, the key semantic components can be identified from the above requirement information, such as the entity corresponding to "department A", the attribute corresponding to "contract income" and "all", the time range corresponding to "last year", the operation corresponding to "query", and the like. Then, it is determined that the requirement of the user A is "querying the total contract income of the department A last year", and the SQL statement such as "select sum(income) from income_table where sale_time=LAST_YEAR() AND dept_name='department A'" is obtained, where income_table is a data table recording the contract income of all departments, income, sale_time and dept_name correspond to the income amount, transaction time and contract department field in the above-mentioned data table, respectively, and LAST_YEAR() can be regarded as a time function in the database for obtaining the year of last year. Of course, the above-mentioned process of analyzing the requirement information can occur independently in the above-mentioned client or in the server, or can occur in the client and the server respectively in stages. The present specification does not limit this.

[0056] In step S204, the permission information of the user is acquired from the permission access list corresponding to the database, and the permission information represents the data access permission of the user in the database.

[0057] In order to ensure that the above original database statement can only access the data that the user is allowed to access, it is necessary to determine the actual data access permission of the user in the database, and the data access permission can be represented by the permission information of the user recorded in the access control list (ACL). The permission information can be created based on the structure of Table 2. As shown in Table 2, the user A with user identification "u123" and the user B with user identification "uAdmin" are recorded in the ACL, and each user records one piece of permission information, and each piece of permission information can correspond to all the contents in a row of Table 2.

[0058] Table 2: Permission information structure

[0059] User identification Role Function permission Permission scope u123 Ordinary user View Only own sales record uAdmin Administrator View, modify All records

[0060] As shown in the table, the identity of the user can be obtained to match the permission information corresponding to the identity in the access control list. The identity can be a user identification (ID), a work number, an identity card number, or other information that can uniquely identify a user.

[0061] Those skilled in the art can understand that Table 2 is only an abstract description of the permission information structure. The function permission can include but is not limited to the read (select), insert, update, and delete of data, and the creation, deletion, and structure modification of database objects such as tables or views, and the execution of stored procedures or functions.

[0062] The content of the permission range can be a value matching a corresponding expression in one or more specific fields. Taking the user A with user identification "u123" as an example, the user A can only query the sales records of the sales records table. The content of the permission range in the permission information can be an expression "salesperson of sales records in sales records table = user identification". Taking the user B with user identification "uAdmin" as an example, the permission range can be directly set to "database" representing the entire database.

[0063] In addition, the permission information can include other contents in actual scenarios, such as the type of database object to which the permission applies, such as tables, views, stored procedures or functions, the name of the database object to which the permission applies, the permission effective / expiration date, the permission grantor, the permission state indicating whether the permission is effective, and the like. This specification does not limit the above.

[0064] It should be noted that, compared with the above large data processing engine determining the row-level permission condition to be spliced from the preset corresponding relationship through the plug-in, the technical solution of the present specification essentially directly obtains the data access permission from the ACL, so as to timely respond to any changes in the corresponding permission information as long as the ACL is obtained before the permission information. Therefore, the problem of poor real-time performance of the original database statement rewritten based on the above corresponding relationship is effectively solved.

[0065] In step S206, the original database statement is rewritten into a target database statement according to the permission information, and the target database statement contains the target filter condition.

[0066] After obtaining the permission information, the original database statement can be rewritten into a target database statement containing a target filter condition according to the permission information. The Abstract Syntax Tree (AST) technology can be used to efficiently rewrite the original database statement.

[0067] In an embodiment, the obtained original database statement can be parsed to generate a corresponding original abstract syntax tree, then a corresponding database statement fragment is generated according to the obtained permission information, and the generated database statement fragment is added as a node to the original abstract syntax tree to obtain a new target abstract syntax tree, and then the target database statement is generated based on the target abstract syntax tree. In this embodiment, the object of rewriting is actually the original abstract syntax tree corresponding to the original database statement, rather than the original database statement itself. Since the abstract syntax tree has a natural structure, the database statement fragment to be added can be added at a suitable position in the abstract syntax tree, and the added database statement fragment can be adaptively changed according to the position, for example, if the original database statement sets an alias for a data table, the database statement fragment can adjust its content according to the specific structure in the abstract syntax tree, and the adjustment operation includes but is not limited to adding or replacing the alias and adding a conjunction.

[0068] The following takes a SQL statement for querying data as an example, and the original abstract syntax tree and the target abstract syntax tree after adding the database statement fragment are described respectively. For the convenience of understanding the content of the figure, the present specification introduces two data tables, the sales record table "sale" and the product table "product", based on Table 2. It can be assumed that there is no privacy information in the product table, and it can be accessed by any user by default. See Tables 3 and 4 for details. Figures 3a-3b

[0069] Table 3 Sales Record Table

[0070] user_id sale_time product_id sale_num User ID Sale time Sale product id Sale quantity ​

[0071] Table 4 commodity table

[0072] product_id product_name product_price Product ID Product name Product unit price

[0073] As Figure 3a shown, assuming that user A with user identification "u123" in Table 2 inputs "How much money did the total sales of the products this month?" as the demand information based on natural language, then through the related technology of NL2SQL, the following query SQL can be inferred as the original database statement:

[0074] SELECT SUM(t1.sale_num*t2.product_price)

[0075] FROM sale t1 LEFT JOIN product t2 ON t1.product_id=t2.product_id

[0076] WHERE MONTH(t1.sale_time)=MONTH(NOW())

[0077] First, the above database statement is used to query data, and the basic statement structure usually includes but is not limited to the following several parts: SELECT: specifies the data item to be retrieved from the database. It can be an asterisk (*), indicating all columns, or a specific column name; FROM: specifies the data source, usually a table name, but also a subquery or the connection of multiple tables; WHERE: specifies the filtering condition, which is used to filter the results and only returns records that meet the specific conditions.

[0078] Second, each statement structure and object in the original database statement is sequentially displayed in a tree structure. Specifically, in Figure 3aIn this way, SQL can be used as the default root node 30, and three major child nodes 301-303 can be connected under the root node. For the query data "SELECT" node 311, its child node is a summation function node 311, which is used to sum the product of the sale quantity and the product price shown in node 321. For the data source "FROM" node 302, its child node is a table connection node "LEFT JOIN" 312, and the corresponding table connection statement indicates that the original database statement is based on the sale table shown in node 322a, left table connection node 322b, and the connection condition shown in node 322c, while the sale table is set as an alias t1 and the product table is set as an alias t2, so that other nodes can directly use the alias to represent the corresponding data table. For the filter condition "WHERE" node 303, its child node 313a limits the sale time of the queried data to satisfy the function MONTH(NOW()), wherein the function NOW() can be used to obtain the current time, and the MONTH() function can obtain the month corresponding to the input time.

[0079] According to Table 2, user A is defined as a normal user in the role of the ACL permission information, and the data access permission of user A is limited to only querying the sale record of user A. Therefore, the generated database statement fragment should be "t1.user_id='u123'", and the corresponding node of the database statement fragment should be added to the original abstract syntax tree as shown in node 313b of the filter condition "WHERE" node 303, which is the sibling node of node 313a. In summary, the original abstract syntax tree after adding the node can be regarded as a new target abstract syntax tree, and the corresponding target database statement can be generated based on the target abstract syntax tree, that is: Figure 3b

[0080] SELECT SUM(t1.sale_num*t2.product_price)

[0081] FROM sale t1 LEFT JOIN product t2

[0082] ON t1.product_id=t2.product_id

[0083] WHERE MONTH(t1.sale_time)=MONTH(NOW())

[0084] AND t1.user_id="u123"

[0085] ​It should be noted that due to the differences in types and versions of database statements and parsers, the same original database statement can generate different structures of original abstract syntax trees, and different target database statements can be generated according to the same target abstract syntax tree. Therefore, the original abstract syntax tree, the target abstract syntax tree, and the target database statement in the above embodiment are only one of many cases. In other words, as long as the above differences are unified and standardized, the original abstract syntax tree, the target original abstract syntax tree, and the target database statement obtained from the original database statement are necessarily unique.

[0086] As described above, each permission information can correspond to all contents in a row of data in Table 2. The present specification can define a so-called row of data as a permission item, each permission item corresponds to at least one data access permission of the user in the database, and each user can correspond to multiple permission items. When any data access permission of the user corresponds to multiple different permission ranges, the intersection of the multiple permission ranges can also be taken.

[0087] On the premise of defining the above permission item, the process of generating the corresponding database statement fragment according to the above permission information in the above can introduce a domain specific statement (Domain Specific Language, DSL) as an intermediate carrier.

[0088] In an embodiment, each permission item in the permission information can be converted into a corresponding domain specific statement according to a unified conversion rule, and a corresponding database statement fragment can be generated according to the domain specific statement corresponding to at least one permission item. The above conversion rule is a kind of database schema, which is a structure description of data in the database. In this embodiment, the above conversion rule can be used to express the abstract set of contents contained in the permission item, which can contain information such as attributes, declarations, and data type definitions of elements. In addition, since the permission item structures of the permission information in different ACLs are often different, the unified conversion rule can be used to convert the permission items with different structures into corresponding domain specific statements, and then generate the corresponding database statement fragment from the domain specific statements with the same structure.

[0089] Here, the above example of the original database statement is still taken as an example. It is assumed that the above conversion rule describes the following structure:

[0090]

[0091] Then the domain specific statement converted from the above original database statement according to the above conversion rule can be:

[0092]

[0093] wherein the definition of each attribute of the domain-specific sentence is as follows: id is a field name, tableName is a table name to which the field belongs; operators is a ternary expression operator, such as a greater than (GT) “>”, a less than (LT) “<”, and an equals (EQU) “=”, etc.; value is a field value; condition is a logical condition between different domain-specific sentences, such as AND or OR; and rules is a filtering rule, which can be used to assist in describing the complex inclusion relationship of different domain-specific sentences in the case of multiple permission items. In particular, since the number of permission items corresponding to user A in Table 2 is 1, the condition attribute does not affect the final generated database sentence fragment whether it takes AND (i.e., takes the intersection of the data access permissions corresponding to the permission items) or OR (i.e., takes the union of the data access permissions corresponding to the permission items), and at the same time, the id, operators, tableName, and value attributes and values can be contained in the rules or the rules can be set to empty, and the above attributes and values are set in the same level as the condition attribute, which is not limited by those skilled in the art.

[0094] In another case, assuming that the permission range of user A with the user identifier “u123” in Table 2 is further limited from the original “only own sales records” to “only own sales records, and the sales quantity in each sales record is greater than 5”, then the original database sentence can be generated as follows:

[0095] SELECT SUM(t1.sql_num*t2.product_price)

[0096] FROM sale t1 LEFT JOIN product t2

[0097] ON t1.product_id=t2.product_id

[0098] WHERE MONTH(t1.sale_time)=MONTH(NOW())

[0099] AND t1.user_id="u123"AND t1.sale_num>5

[0100] Correspondingly, the domain-specific sentence converted from the above-mentioned original database sentence according to the conversion rule can be as follows:

[0101]

[0102] It can be understood that in the ACL, the number of database statement fragments to be generated is irrelevant to the number of permission items corresponding to the user in the permission information, but is related to the database objects involved in the original database statement. It is assumed that the user A with the user identifier "u123" in Table 2 corresponds to 10 roles, each of which has a viewing permission for different data tables, but since the above original database statement only involves two database objects of sale and product, if the tableName in any domain-specific statement does not match the above database objects, the corresponding database statement fragment does not need to be generated, and more does not need to be added as a node to the original abstract syntax tree, thereby avoiding the generation of a large number of meaningless database statement fragments, reducing resource overhead, and improving the generation efficiency of effective database statement fragments.

[0103] After obtaining the database statement fragments, the technical solution of the specification can also accurately determine the addition position of the corresponding node of the database statement fragment in the original abstract syntax tree by analyzing the original abstract syntax tree.

[0104] In an embodiment, each database statement fragment can include a corresponding data access permission applicable data source in the domain-specific statement; in the case where the original abstract syntax tree includes at least one condition filtering node, the following operations can be performed for each condition filtering node in the at least one condition filtering node: first, determine the target sibling node corresponding to the condition filtering node, and the subtree of the target sibling node is used to represent the data source of the condition filtering node; then, according to the data source corresponding to the target sibling node and the applicable data source of each database statement fragment, determine the database statement fragment corresponding to the condition filtering node; finally, add the determined database statement fragment as a child node of the condition filtering node to the original abstract syntax tree. The applicable data source can be the property tableName defined by the conversion rule, or an alias of a data table, a table join, a query result of another database statement, etc., as long as the conversion rule is configured with the relevant properties in advance.

[0105] The condition filtering node can correspond to the WHERE statement in SQL. Those skilled in the art can understand that when there are multiple condition filtering nodes in the original abstract syntax tree, it means that there is a subquery statement in the original database statement corresponding to the original abstract syntax tree, and vice versa. When there is no condition filtering node or only one condition filtering node in the original abstract syntax tree, it means that there is no subquery statement in the original database statement corresponding to the original abstract syntax tree.

[0106] The following will be described in conjunction with Figure 4Taking a query statement in SQL as an original database statement as an example, a specific process of rewriting the above database statement is introduced. The process specifically includes the following steps:

[0107] In step S402, an original SQL is obtained.

[0108] In an embodiment, a user wants to query specific data, so the user inputs a piece of natural language as demand information in a corresponding client of a mobile phone. The client can generate a data query request to a server, wherein the data query request can add the demand information and an identity of the user. When the server receives the data query request, a corresponding SQL statement can be determined as an original SQL based on an NL2SQL technology.

[0109] In step S404, the original SQL is parsed to generate a corresponding original abstract syntax tree.

[0110] In an embodiment, the server can use tools such as Calcite and FastSql to parse the original SQL and generate an original abstract syntax tree.

[0111] In step S406, a parent query node / child query node in the original abstract syntax tree is recursively traversed.

[0112] In an embodiment, the recursive traversal of the parent query node on the syntax tree can be started from a root node of the original abstract syntax tree. If the step is triggered by step S418, the recursive traversal of the corresponding child query node under the current node is performed. The contents under the parent query node and the child query node can correspond to a query statement in SQL such as “SELECT……FROM……”, and the difference is that the latter is nested in the parent query node or other child query nodes.

[0113] In step S408, a condition filtering node in the current node is recursively traversed.

[0114] In an embodiment, for the current parent query node / child query node, the condition filtering node therein can be recursively traversed. The content under the condition filtering node can correspond to a condition filtering statement in SQL such as “WHERE”.

[0115] In step S410, it is determined whether the current condition filtering node contains a child query node.

[0116] In an embodiment, the server can further determine whether the current condition filtering node contains a child query node. If yes, step S406 is performed to add a corresponding database statement segment to the condition filtering node of the child query node. If no, step S416 is performed.

[0117] Step S412, obtaining the permission information of the user from the permission access list.

[0118] In an embodiment, this step can be processed in parallel with step S402, and after receiving the data query request, the server can obtain the identity of the user therefrom, and match the permission information corresponding to the identity in the access control list corresponding to the database.

[0119] Step S414, converting the permission items in the permission information into corresponding domain-specific statements.

[0120] In an embodiment, the server can convert each permission item in the permission information into a corresponding domain-specific statement according to a unified conversion rule.

[0121] Step S416, generating a corresponding database statement segment according to the domain-specific statement.

[0122] In an embodiment, the server has obtained the domain-specific statement corresponding to the user, and can generate the database statement segment to be added by each condition filtering node found by recursive traversal in steps S406-S410 according to the corresponding applicable data source, such as table name information, in each domain-specific statement.

[0123] Step S418, adding the database statement segment corresponding to each condition filtering node in the original abstract syntax tree.

[0124] In an embodiment, the server can add the database statement segment determined in the previous step as a child node of each condition filtering node to the original abstract syntax tree.

[0125] Step S420, generating a target database statement based on the target abstract syntax tree.

[0126] In an embodiment, when the condition filtering node in the original abstract syntax tree adds a node containing the corresponding database statement segment, the original abstract syntax tree can be referred to as a target abstract syntax tree, and the server can generate a target database statement according to the target abstract syntax tree for execution and feed back the execution result to the client.

[0127] For the convenience of description Figure 4 the specific operations performed by the server for each condition filtering node are described in combination with Figure 5a and Figure 5b the original abstract syntax tree corresponding to the original database statement containing the subquery statement is described.

[0128] Before proceeding, let's assume we add a new customer information table called "buyer", which includes customer ID, customer gender (predefined number 0 represents female and number 1 represents male) and customer address, as detailed in Table 5.

[0129] Table 5 Customer Information Form

[0130] buyer_id buyer_sex buyer_address Buyer ID Buyer sex Buyer address

[0131] Simultaneously, a new customer (i.e., the seller) ID field "buyer_id" is added to Table 3, and a new permission item is added to Table 2 for user A with the user identifier "u123". This permission item assigns user A the role of "Beijing area salesperson" and grants query permissions to all customer information in the customer information table whose address is "beijing". Correspondingly, assuming that user A inputs "How much money did you sell to female customers this month?" as the request information using natural language, the corresponding original database statement can be:

[0132] SELECT SUM(t1.sale_num*t2.product_price)

[0133] FROM sale t1 LEFT JOIN product t2

[0134] ON t1.product_id=t2.product_id

[0135] WHERE MONTH(t1.sale_time)=MONTH(NOW())

[0136] AND t1.buyer_id IN(SELECT t3.buyer_id FROM buyer t3 WHERE t3.buyer_sex=0)

[0137] Correspondingly, the domain-specific statement obtained by transforming the original database statement according to the above transformation rules can be:

[0138]

[0139] like Figure 5a As shown, the raw abstract syntax tree generated after parsing the original database statement can use SQL as the default root node 50, and contain three main sub-nodes 501 to 503 under this root node. For the query data "SELECT" node 501 and the data source "FROM" node 502, their subtree structures and the meanings of each node are respectively as follows: Figure 3aThe nodes 301, 302 in the above are consistent, and thus are not described in detail in the present specification. For the filter condition "WHERE" node 503, the node 513a thereof defines that the sales time of the queried data needs to satisfy the function MONTH(NOW()); the node 513b requires that the customer ID in the sales record table satisfies a specified condition; and the node 513c represents the specified condition based on the query result of a subquery, and the node 513c can be regarded as another SQL node, which includes three sub-nodes 523a-523c. For the query data "SELECT" node 523a, the sub-node 531 thereof specifies that the data to be retrieved from the database is the customer ID in the customer information table; for the data source "FROM" node 523b, the sub-node 532 thereof defines that the data source of the subquery statement is the customer information table, and sets an alias t3 for the product table; and for the filter condition "WHERE" node 523c, the sub-node 533a thereof limits that the customer gender of the queried data needs to be female.

[0140] In the above, the table 2 in which the new permission item is added, it can be known that the user A is defined as a normal user and a salesperson in Beijing in the permission information of the ACL, and the data access permission thereof is limited to only the query operation on the sales record of the user A or the query operation on the customer information of all customers with the address "beijing" in Beijing. Therefore, on the basis of the original database statement, the generated database statement segment should be "t1.user_id='u123'" and "t3.buyer_adress='beijing'". The positions of the nodes corresponding to the two database statement segments added in the original abstract syntax tree can be discussed separately.

[0141] 1. For the database statement segment "t1.user_id='u123'", it is known that the corresponding condition filter node is the node 503. The target sibling node corresponding to the condition filter node can be determined. Since the sub-tree of the target sibling node is used to represent the data source of the condition filter node, and only the sub-tree of the node 502 is used to represent the data source (i.e., the left table join node 512) of the condition filter node among the sibling nodes of the node 503, the target sibling node of the node 503 is the node 502. After the target sibling node is determined, it can be determined that the data source corresponding to the target sibling node is the sales record table and the product table, and the applicable data source contained in the domain-specific statement corresponding to the database statement segment is the sales record table. By comparison, it can be determined that the sales record table matches the data source corresponding to the target sibling node and the applicable data source. Therefore, the database statement segment "t1.user_id='u123'" can be added to the original abstract syntax tree as the sub-node 513d of the condition filter node 503.

[0142] 2、For the database statement fragment "t3.buyer_adress='beijing'", it is known that the corresponding condition filtering node is node 523c, it can be tried to determine the target sibling node corresponding to the condition filtering node, since the sub-tree of the target sibling node is used to represent the data source of the condition filtering node, and only the sub-tree of node 523b is used to represent the data source (i.e. node 532) of the condition filtering node among the sibling nodes of node 523c, thus the target sibling node of node 523c is node 523b, after determining the target sibling node, it can be determined that the data source corresponding to the target sibling node is the customer information table, and the applicable data source contained in the domain-specific statement corresponding to the database statement fragment is the customer information table, by comparison, it can be determined that the customer information table matches the data source corresponding to the target sibling node and the applicable data source at the same time, thus the database statement fragment "t3.buyer_adress='beijing'" can be added to the original abstract syntax tree as the child node 533b of the condition filtering node 523c, and further as the sibling node of node 533a.

[0143] At this point, a plurality of database statement fragments are added to different positions in the original abstract syntax tree, and a target abstract syntax tree as shown in Figure 5b is obtained.

[0144] Figure 6 is a schematic structural diagram of an electronic device provided by an exemplary embodiment. Please refer to Figure 6 , at the hardware level, the device includes a processor 602, an internal bus 604, a network interface 606, a memory 608, and a non-volatile memory 610, and of course, other hardware required by functions. One or more embodiments of the present specification can be implemented in a software manner, such as reading a corresponding computer program from the non-volatile memory 610 into the memory 608 by the processor 602 and then running. Of course, in addition to the software implementation, one or more embodiments of the present specification do not exclude other implementation manners, such as logic devices or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or a logic device. Corresponding to the embodiment of the rewriting method of the database statement, the present specification also provides an embodiment of a rewriting device of the database statement.

[0145] Please refer to Figure 7 , the rewriting device of the database statement can be applied to the device as shown in Figure 7 to implement the technical solutions of the present specification. Wherein, the rewriting device of the database statement can include:

[0146] The original database statement acquisition unit 71 is configured to acquire an original database statement corresponding to a user, wherein the original database statement is missing at least one target filtering condition used to represent data access authority of the user in a database;

[0147] The permission information acquisition unit 72 is configured to acquire permission information of the user from a permission access list corresponding to the database, wherein the permission information represents data access authority of the user in the database;

[0148] The original database statement rewriting unit 73 is configured to rewrite the original database statement into a target database statement according to the permission information, wherein the target database statement contains the target filtering condition.

[0149] Optionally, the original database statement acquisition unit 71 is specifically configured to:

[0150] acquire requirement information of the user based on natural language input;

[0151] determine the original database statement according to a parsing result of the natural language corresponding to the requirement information.

[0152] Optionally, the permission information acquisition unit 72 is specifically configured to:

[0153] acquire an identity of the user;

[0154] match permission information corresponding to the identity in the access control list.

[0155] Optionally, the original database statement rewriting unit 73 further includes:

[0156] An original abstract syntax tree generation unit 74 is configured to parse the original database statement to generate a corresponding original abstract syntax tree;

[0157] A target abstract syntax tree generation unit 75 is configured to generate a database statement segment corresponding to the permission information, and add the database statement segment as a node into the original abstract syntax tree to acquire a new target abstract syntax tree;

[0158] A target database statement generation unit 76 is configured to generate the target database statement based on the target abstract syntax tree.

[0159] Optionally, the permission information includes permission items, and each permission item corresponds to at least one kind of data access authority of the user in the database; and the target abstract syntax tree generation unit 75 is specifically configured to:

[0160] convert each permission item in the permission information into a corresponding domain-specific sentence according to a unified conversion rule;

[0161] generate a corresponding database statement segment according to the domain-specific sentence corresponding to the at least one permission item.

[0162] Optionally, the domain-specific sentence corresponding to each database statement segment contains an applicable data source of the corresponding data access permission; and the target abstract syntax tree generation unit 75 is specifically configured to:

[0163] In the case where the original abstract syntax tree contains at least one conditional filtering node, for each conditional filtering node in the at least one conditional filtering node:

[0164] determine a target sibling node corresponding to the conditional filtering node, and a subtree of the target sibling node is used to represent a data source of the conditional filtering node;

[0165] determine a database statement segment corresponding to the conditional filtering node according to the data source corresponding to the target sibling node and the applicable data source corresponding to each database statement segment;

[0166] add the determined database statement segment to the original abstract syntax tree as a child node of the conditional filtering node.

[0167] Optionally, the permission information contains a permission control range of the corresponding data access permission, and the permission control range includes a database, a data table in the database, a data column or a data row in the data table, and the permission control range defines data access content of the user.

[0168] For details of the implementation process of the functions and effects of each unit in the above apparatus, please refer to the implementation process of the corresponding steps in the above method, which will not be repeated here.

[0169] Based on the same idea as the above method, the present specification also provides an electronic device, which includes a processor, a memory for storing processor-executable instructions, and wherein the processor implements the steps of the method according to any one of the above embodiments by running the executable instructions.

[0170] Based on the same idea as the above method, the present specification also provides a computer-readable storage medium having computer instructions stored thereon, which are executed by a processor to implement the steps of the method according to any one of the above embodiments.

[0171] Based on the same idea as the above method, the present specification also provides a computer program product, which includes computer programs / instructions, which are executed by a processor to implement the steps of the method according to any one of the above embodiments.

Claims

1. A method for rewriting database statements, characterized in that, The method includes: Obtain the original database statement corresponding to the user, wherein at least one target filtering condition is missing from the original database statement, which is used to characterize the user's data access permissions in the database; The user's permission information is obtained from the permission access list corresponding to the database, and the permission information represents the user's data access permissions in the database; The original database statement is rewritten into a target database statement based on the permission information, and the target database statement contains the target filtering conditions.

2. The method according to claim 1, characterized in that, The statement for retrieving the original database corresponding to the user includes: Obtain the user's demand information based on natural language input; The original database statement is determined based on the parsing results of the natural language corresponding to the demand information.

3. The method according to claim 1, characterized in that, The step of retrieving the user's permission information from the permission access list corresponding to the database includes: Obtain the user's identity identifier; Match the permission information corresponding to the identity in the access control list.

4. The method according to claim 1, characterized in that, The step of rewriting the original database statement into the target database statement based on the permission information includes: The original database statements are parsed to generate the corresponding original abstract syntax tree; Generate corresponding database statement fragments based on the permission information, and add the database statement fragments as nodes to the original abstract syntax tree to obtain a new target abstract syntax tree; The target database statement is generated based on the target abstract syntax tree.

5. The method according to claim 4, characterized in that, The permission information includes permission items, and each permission item corresponds to at least one data access permission of the user in the database. The step of generating the corresponding database statement fragment based on the permission information includes: Each permission item in the permission information is converted into a corresponding domain-specific statement according to a unified conversion rule; Generate corresponding database statement fragments based on the domain-specific statement corresponding to at least one permission item.

6. The method according to claim 5, characterized in that, Each database statement fragment contains the applicable data source with corresponding data access permissions in the domain-specific statement. Adding the database statement fragment as a node to the original abstract syntax tree includes: In the case where the original abstract syntax tree contains at least one conditional filter node, for each of the at least one conditional filter node: Determine the target sibling node corresponding to the conditional filtering node, and the subtree of the target sibling node is used to represent the data source of the conditional filtering node; Based on the data source corresponding to the target sibling node and the applicable data source corresponding to each database statement fragment, determine the database statement fragment corresponding to the condition filtering node; The determined database statement fragment is added as a child node of the condition filter node to the original abstract syntax tree.

7. The method according to claim 1, characterized in that, The permission information includes the permission control scope of the corresponding data access permissions. The permission control scope includes: the database, the data table in the database, the data column or data row in the data table, and the permission control scope defines the data access content of the user.

8. A computer-readable storage medium having a computer program / instructions stored thereon, characterized in that, When the program / instruction is executed by the processor, it implements the steps of the method as described in any one of claims 1 to 7.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps of the method as described in any one of claims 1 to 7.

10. A computer program product comprising a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method as described in any one of claims 1 to 7.

Images