Row data control method and system supporting main-sub table composite condition

Abstract

The invention relates to a main-sub table composite condition row data control method and system, and aims at the refined requirement of data access control in an enterprise-level application system, and by constructing an RBAC + ABAC fusion permission model, role-based batch permission allocation and attribute-based fine-grained permission rule generation are realized, and main-sub table association conditions are supported. During data access, a system analyzes login account permission through a permission matching and screening module, repeated permissions with a small range are removed, and a minimum permission set is generated. And the dynamic SQL generation module analyzes the association relationship between the main table and the sub-table, converts the composite condition into an SQL syntax tree, and generates an executable SQL fragment. The method breaks through the limitation of a traditional RBAC model, achieves intelligent optimization of authority management, meets the requirements of complex service scenes, improves the database query efficiency, and is suitable for a large-scale system with frequently-changed authority rules.

Description

Technical Field

[0001] This invention belongs to the field of data access technology, and in particular relates to a method and system for controlling data rows with composite conditions in master and child tables. Background Technology

[0002] In modern enterprise application systems, the granularity of data access control directly impacts system security and the accuracy of business logic. Row-level data control, as a crucial component of data access control, aims to ensure that different users can only access data rows that match their permissions. Traditional row-level data control methods are often based on simple permission models, such as single-table, single-condition, or role-based access control (RBAC). While these models simplify permission management to some extent, they fall short of the permission control requirements in complex business scenarios.

[0003] As business complexity increases, many scenarios require combining multiple user attributes with the characteristics of the data objects themselves for permission judgment. For example, in a thermal power system, a technical manager not only needs to access power generation data (main table data) for a specific area based on their role, but also needs to view detailed order information for specific customers within that area (sub-table data). Furthermore, access permissions for order information may be related to a combination of conditions such as order amount and order status. In addition, when cross-access between main and sub-table data is involved from users at different departments and levels, traditional single-condition permission control methods cannot meet such complex business needs. Summary of the Invention

[0004] (a) Purpose of the invention To overcome the above shortcomings, the purpose of this invention is to provide a method and system for controlling row data under composite conditions in master and child tables, so as to solve the above technical problems.

[0005] (II) Technical Solution To achieve the above objectives, the technical solution provided in this application is as follows: A method for controlling row data in a master-child table with combined conditions includes the following steps: Step 1: Construct an RBAC+ABAC integrated permission model: Assign row data permissions to different accounts, perform batch permission assignment based on the role to which the account belongs, and generate fine-grained permission rules by combining account attributes, operation environment attributes and data object attributes, supporting master-child table association conditions; Step 2: Permission matching and filtering: Based on the query of the main table and the permissions of the currently logged-in account, the permissions are matched, the inclusion relationship and duplication are analyzed, the permissions with small scope and duplicate permissions are removed, and the minimum permission set containing the main table filtering conditions, the sub-table association conditions and the compound logic is generated. Step 3: Dynamic SQL generation: Parse the master-child table relationship, convert the complex conditions in the permission rules into an SQL syntax tree, and generate an executable SQL fragment containing master-child table join conditions, filtering conditions, and complex logic; Step 4: SQL Query Concatenation and Execution: Based on the data usage scenario, dynamically generated SQL fragments are intelligently concatenated into the appropriate positions in the SQL query to achieve row data control.

[0006] Preferably, the method for constructing the RBAC+ABAC fused permission model includes: Based on the RBAC model: perform batch allocation of role-level permissions, and define the main table operation permissions and sub-table join query range corresponding to the role; Based on the ABAC model: Fine-grained permission rules containing field-level conditions are generated according to user attributes, operating environment attributes, and data object attributes. The user attributes include department and position, the operating environment attributes include terminal IP and time window, and the data object attributes include master and child table field values. Supports master-child table association conditions: establish master-child table data association relationships through foreign key associations and custom association fields.

[0007] Preferably, the permission matching and filtering process includes: Analyze all permissions of the currently logged-in account in the main table to determine if there are any duplicate permissions; For multiple permission conditions in the same main table, if the field value range of condition A completely includes condition B, then condition A is retained and condition B is removed. Duplicate permissions are deduplicated to generate a minimum set of permissions that includes the main table's filtering conditions, sub-table's join conditions, and complex logic.

[0008] Preferably, the dynamic SQL generation method includes: Master-Child Table Relationship Analysis: Identifies foreign key relationships, custom relationship fields, and join methods between the master and child tables; Compound conditional compilation: converts logical expressions in permission rules into SQL syntax trees; SQL Fragment Generation: Generates executable SQL fragments containing master-child table join conditions, filter conditions, and complex logic based on the syntax tree.

[0009] Preferably, the input for the master-child table association resolution is: Main table structure : Contains field information from the main table; Sub-table structure : Contains field information from the sub-table; Association conditions in permission rules : Describes the relationship between the main table and the child table; The specific steps are as follows: Identify foreign key relationships from the main table. and subtable In the structure, find the foreign key field FK in the main table and the related fields in the child table, and record these foreign key fields and their corresponding child table fields; Determine the connection method based on the association conditions in the permission rules. Determine the join method (JT). If the join condition requires the main table and the child table to match, use INNER JOIN; if it allows data from the main table to be returned even if there is no corresponding record in the child table, use LEFT JOIN. Establish the relationship between the master and child tables, construct the master-child table relationship JR, ​​record the foreign key fields and join methods, and output the master-child table relationship JR, ​​including the foreign key fields and join methods. The logical expression is parsed. The input of the compound conditional compilation module is the logical expression LE in the permission rule, and the logical expression LE is decomposed into the subexpression SE. The logical operators and their corresponding subexpressions are recorded. Construct a syntax tree (ST) according to the hierarchical relationship of logical expressions. Each node of the syntax tree represents a logical expression or logical operator, and the leaf nodes represent specific conditional expressions. Output the SQL syntax tree (ST) to represent the hierarchical structure of logical expressions. Convert the data into SQL statement fragments and execute them. Traverse the syntax tree ST, convert each node into an SQL statement fragment SF, integrate the statement fragments SF into the SQL statement for joining the master and child tables, and generate the final SQL query statement ESQ based on the master and child table relationship JR.

[0010] Preferably, the SQL query concatenation strategy includes: Judgment strategy based on data aggregation dimension: Based on the data volume and index usage, select to concatenate dynamic SQL fragments into the WHERE clause, HAVING clause, or other appropriate positions; Customization strategy based on business rules: Supports customizing the concatenation position of dynamic SQL fragments through configuration files.

[0011] Preferably, it also includes a distributed caching solution based on JVM memory, specifically including: It adopts a multi-level caching architecture, including a local caching layer and a distributed caching layer; Cache account permission information, filtered permission results, and dynamically organized SQL statements; The cache invalidation mechanism based on data change events automatically refreshes the relevant cache when data in the main table or sub-table changes.

[0012] A data control system supporting composite condition rows in master-child tables, used to implement the aforementioned data control method for composite condition rows in master-child tables, characterized in that it includes: Permission allocation module: used to build an RBAC+ABAC integrated permission model and assign row data permissions to different accounts; Permission matching and filtering module: Used to match permissions based on the query main table and the permissions of the currently logged-in account, filter duplicate permissions and permissions with small scope, and generate a minimum permission set; Dynamic SQL generation module: used to parse the relationship between master and child tables, convert complex conditions into SQL syntax trees, and generate SQL query fragments; Data control module: Used to intelligently concatenate dynamic SQL fragments into the appropriate position in the SQL query according to the data usage scenario, so as to realize row data control; Caching module: It adopts a distributed caching scheme based on JVM memory to cache account permission information, filtered permission results and dynamically organized SQL statements, and performs cache invalidation based on data change events.

[0013] The dynamic SQL generation module includes: The master-child table association resolution unit is used to identify the association relationship and connection method between the master table and the child table; The compound conditional compilation unit is used to convert logical expressions in permission rules into SQL syntax trees; The SQL fragment generation unit is used to generate executable SQL fragments based on the syntax tree.

[0014] Beneficial effects: Compared with existing technologies, this invention not only supports single-condition permission control for the main table, but also adds permission control for sub-tables and composite conditions, making it suitable for more complex business scenarios. At the same time, it improves overall performance by using a distributed caching scheme based on JVM memory and combining it with a permission matching and filtering mechanism. Attached Figure Description

[0015] Figure 1 This is a flowchart illustrating the present invention. Detailed Implementation

[0016] To make the objectives, technical solutions, and advantages of this invention clearer, the following detailed embodiments are described in conjunction with the appendix. Figure 1 The present invention will be described in further detail below. It should be understood that these descriptions are merely exemplary and not intended to limit the scope of the invention. Furthermore, descriptions of well-known structures and techniques are omitted in the following description to avoid unnecessarily obscuring the concept of the invention.

[0017] This invention provides a method for controlling row data in master-child tables under composite conditions, comprising the following steps: Step 1: Construct an RBAC+ABAC integrated permission model: Assign row data permissions to different accounts, perform batch permission assignment based on the role to which the account belongs, and generate fine-grained permission rules by combining account attributes, operation environment attributes and data object attributes, supporting master-child table association conditions; Step 2: Permission matching and filtering: Based on the query of the main table and the permissions of the currently logged-in account, the permissions are matched, the inclusion relationship and duplication are analyzed, the permissions with small scope and duplicate permissions are removed, and the minimum permission set containing the main table filtering conditions, the sub-table association conditions and the compound logic is generated. Step 3: Dynamic SQL generation: Parse the master-child table relationship, convert the complex conditions in the permission rules into an SQL syntax tree, and generate an executable SQL fragment containing master-child table join conditions, filtering conditions, and complex logic; Step 4: SQL Query Concatenation and Execution: Based on the data usage scenario, dynamically generated SQL fragments are intelligently concatenated into the appropriate positions in the SQL query to achieve row data control.

[0018] Preferably, the method for constructing the RBAC+ABAC fused permission model includes: Based on the RBAC model: perform batch allocation of role-level permissions, and define the main table operation permissions and sub-table join query range corresponding to the role; Based on the ABAC model: Fine-grained permission rules containing field-level conditions are generated according to user attributes, operating environment attributes, and data object attributes. The user attributes include department and position, the operating environment attributes include terminal IP and time window, and the data object attributes include master and child table field values. Supports master-child table association conditions: establish master-child table data association relationships through foreign key associations and custom association fields.

[0019] Preferably, the permission matching and filtering process includes: Analyze all permissions of the currently logged-in account in the main table to determine if there are any duplicate permissions; For multiple permission conditions in the same main table, if the field value range of condition A completely includes condition B, then condition A is retained and condition B is removed. Duplicate permissions are deduplicated to generate a minimum set of permissions that includes the main table's filtering conditions, sub-table's join conditions, and complex logic.

[0020] Preferably, the dynamic SQL generation method includes: Master-Child Table Relationship Analysis: Identifies foreign key relationships, custom relationship fields, and join methods between the master and child tables; Compound conditional compilation: converts logical expressions in permission rules into SQL syntax trees; SQL Fragment Generation: Generates executable SQL fragments containing master-child table join conditions, filter conditions, and complex logic based on the syntax tree.

[0021] Preferably, the input for the master-child table association resolution is: Main table structure : Contains field information from the main table; Sub-table structure : Contains field information from the sub-table; Association conditions in permission rules : Describes the relationship between the main table and the child table; The specific steps are as follows: Identify foreign key relationships from the main table. and subtable In the structure, find the foreign key field FK in the main table and the related fields in the child table, and record these foreign key fields and their corresponding child table fields; Determine the connection method based on the association conditions in the permission rules. Determine the join method (JT). If the join condition requires the main table and the child table to match, use INNER JOIN; if it allows data from the main table to be returned even if there is no corresponding record in the child table, use LEFT JOIN. Establish the relationship between the master and child tables, construct the master-child table relationship JR, ​​record the foreign key fields and join methods, and output the master-child table relationship JR, ​​including the foreign key fields and join methods. The logical expression is parsed. The input of the compound conditional compilation module is the logical expression LE in the permission rule, and the logical expression LE is decomposed into the subexpression SE. The logical operators and their corresponding subexpressions are recorded. Construct a syntax tree (ST) according to the hierarchical relationship of logical expressions. Each node of the syntax tree represents a logical expression or logical operator, and the leaf nodes represent specific conditional expressions. Output the SQL syntax tree (ST) to represent the hierarchical structure of logical expressions. Convert the data into SQL statement fragments and execute them. Traverse the syntax tree ST, convert each node into an SQL statement fragment SF, integrate the statement fragments SF into the SQL statement for joining the master and child tables, and generate the final SQL query statement ESQ based on the master and child table relationship JR.

[0022] Preferably, the SQL query concatenation strategy includes: Judgment strategy based on data aggregation dimension: Based on the data volume and index usage, select to concatenate dynamic SQL fragments into the WHERE clause, HAVING clause, or other appropriate positions; Customization strategy based on business rules: Supports customizing the concatenation position of dynamic SQL fragments through configuration files.

[0023] Preferably, it also includes a distributed caching solution based on JVM memory, specifically including: It adopts a multi-level caching architecture, including a local caching layer and a distributed caching layer; Cache account permission information, filtered permission results, and dynamically organized SQL statements; The cache invalidation mechanism based on data change events automatically refreshes the relevant cache when data in the main table or sub-table changes.

[0024] A data control system supporting composite condition rows in master-child tables, used to implement the aforementioned data control method for composite condition rows in master-child tables, characterized in that it includes: Permission allocation module: used to build an RBAC+ABAC integrated permission model and assign row data permissions to different accounts; Permission matching and filtering module: Used to match permissions based on the query main table and the permissions of the currently logged-in account, filter duplicate permissions and permissions with small scope, and generate a minimum permission set; Dynamic SQL generation module: used to parse the relationship between master and child tables, convert complex conditions into SQL syntax trees, and generate SQL query fragments; Data control module: Used to intelligently concatenate dynamic SQL fragments into the appropriate position in the SQL query according to the data usage scenario, so as to realize row data control; Caching module: It adopts a distributed caching scheme based on JVM memory to cache account permission information, filtered permission results and dynamically organized SQL statements, and performs cache invalidation based on data change events.

[0025] The dynamic SQL generation module includes: The master-child table association resolution unit is used to identify the association relationship and connection method between the master table and the child table; The compound conditional compilation unit is used to convert logical expressions in permission rules into SQL syntax trees; The SQL fragment generation unit is used to generate executable SQL fragments based on the syntax tree.

[0026] This invention breaks through the limitations of the traditional single RBAC model, proposing and implementing a deep integration of RBAC and ABAC. By overlaying attributes and conditions, this invention deepens access control from the "role level" to the "object attribute level," solving the segmented needs of "different permissions for the same role" in complex business scenarios, such as differentiated access to sensitive data by employees in different positions within the same department.

[0027] This invention designs and implements a permission matching and filtering engine that automatically identifies the inclusion relationship (such as parent and child permissions) and duplicates (different expressions of the same rule) between permission rules. By eliminating redundant permissions, it generates the minimum permission set that meets business needs, avoiding logical conflicts or over-authorization caused by permission overlap. The streamlined permission set directly reduces the complexity of subsequent SQL statement generation, such as reducing the number of JOIN tables, simplifying WHERE conditions, and improving database query efficiency. This invention, through the engine's automated processing, shifts permission management from "manual maintenance" to "intelligent optimization," making it particularly suitable for large systems with frequently changing permission rules.

[0028] This invention designs and implements a dynamic SQL engine, achieving the following key functions: automatically generating corresponding SQL fragments based on the master-child table association conditions in the permission rules; intelligently concatenating attribute conditions in the ABAC model, such as "data creator = current user ID" and "operation environment IP is in the whitelist," into the SQL; this invention solves problems such as multi-dimensional data filtering and master-child table joint permission verification by dynamically mapping permission rules and SQL statements through a pipeline mechanism of "condition parsing - fragment generation - intelligent assembly."

[0029] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0030] The above embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for controlling row data in a master-child table with composite conditions, characterized in that, Includes the following steps: Step 1: Construct an RBAC+ABAC integrated permission model: Assign row data permissions to different accounts, perform batch permission assignment based on the role to which the account belongs, and generate fine-grained permission rules by combining account attributes, operation environment attributes and data object attributes, supporting master-child table association conditions; Step 2: Permission matching and filtering: Based on the query of the main table and the permissions of the currently logged-in account, the permissions are matched, the inclusion relationship and duplication are analyzed, the permissions with small scope and duplicate permissions are removed, and the minimum permission set containing the main table filtering conditions, the sub-table association conditions and the compound logic is generated. Step 3: Dynamic SQL generation: Parse the master-child table relationship, convert the complex conditions in the permission rules into an SQL syntax tree, and generate an executable SQL fragment containing master-child table join conditions, filtering conditions, and complex logic; Step 4: SQL Query Concatenation and Execution: Based on the data usage scenario, dynamically generated SQL fragments are intelligently concatenated into the appropriate positions in the SQL query to achieve row data control.

2. The method for controlling row data in a master-child table with composite conditions according to claim 1, characterized in that, The method for constructing the RBAC+ABAC fusion permission model includes: Based on the RBAC model: perform batch allocation of role-level permissions, and define the main table operation permissions and sub-table join query range corresponding to the role; Based on the ABAC model: Fine-grained permission rules containing field-level conditions are generated according to user attributes, operating environment attributes, and data object attributes. The user attributes include department and position, the operating environment attributes include terminal IP and time window, and the data object attributes include master and child table field values. Supports master-child table association conditions: establish master-child table data association relationships through foreign key associations and custom association fields.

3. The method for controlling row data in a master-child table with composite conditions according to claim 1, characterized in that, The permission matching and filtering process includes: Analyze all permissions of the currently logged-in account in the main table to determine if there are any duplicate permissions; For multiple permission conditions in the same main table, if the field value range of condition A completely includes condition B, then condition A is retained and condition B is removed. Duplicate permissions are deduplicated to generate a minimum set of permissions that includes the main table's filtering conditions, sub-table's join conditions, and complex logic.

4. The method for controlling row data with composite conditions in master-child tables according to claim 1, characterized in that, The dynamic SQL generation method includes: Master-Child Table Relationship Analysis: Identifies foreign key relationships, custom relationship fields, and join methods between the master and child tables; Compound conditional compilation: converts logical expressions in permission rules into SQL syntax trees; SQL Fragment Generation: Generates executable SQL fragments containing master-child table join conditions, filter conditions, and complex logic based on the syntax tree.

5. The method for controlling row data with composite conditions in master-child tables according to claim 4, characterized in that, The input for the master-child table association resolution is: Main table structure : Contains field information from the main table; Sub-table structure : Contains field information from the sub-table; Association conditions in permission rules : Describes the relationship between the main table and the child table; The specific steps are as follows: Identify foreign key relationships from the main table. and subtable In the structure, find the foreign key field FK in the main table and the related fields in the child table, and record these foreign key fields and their corresponding child table fields; Determine the connection method based on the association conditions in the permission rules. Determine the join method (JT). If the join condition requires the main table and the child table to match, use INNER JOIN; if it allows data from the main table to be returned even if there is no corresponding record in the child table, use LEFT JOIN. Establish the relationship between the master and child tables, construct the master-child table relationship JR, ​​record the foreign key fields and join methods, and output the master-child table relationship JR, ​​including the foreign key fields and join methods. The logical expression is parsed. The input of the compound conditional compilation module is the logical expression LE in the permission rule, and the logical expression LE is decomposed into the subexpression SE. The logical operators and their corresponding subexpressions are recorded. Construct a syntax tree (ST) according to the hierarchical relationship of logical expressions. Each node of the syntax tree represents a logical expression or logical operator, and the leaf nodes represent specific conditional expressions. Output the SQL syntax tree (ST) to represent the hierarchical structure of logical expressions. Convert the data into SQL statement fragments and execute them. Traverse the syntax tree ST, convert each node into an SQL statement fragment SF, integrate the statement fragments SF into the SQL statement for joining the master and child tables, and generate the final SQL query statement ESQ based on the master and child table relationship JR.

6. The method for controlling row data with composite conditions in master-child tables according to claim 1, characterized in that, The SQL query concatenation strategy includes: Judgment strategy based on data aggregation dimension: Based on the data volume and index usage, select to concatenate dynamic SQL fragments into the WHERE clause, HAVING clause, or other appropriate positions; Customization strategy based on business rules: Supports customizing the concatenation position of dynamic SQL fragments through configuration files.

7. The method for controlling row data in a master-child table with composite conditions according to claim 1, characterized in that, It also includes distributed caching solutions based on JVM memory, specifically including: It adopts a multi-level caching architecture, including a local caching layer and a distributed caching layer; Cache account permission information, filtered permission results, and dynamically organized SQL statements; The cache invalidation mechanism based on data change events automatically refreshes the relevant cache when data in the main table or sub-table changes.

8. A data control system supporting composite condition rows in master-daughter tables, used to implement the data control method for composite condition rows in master-daughter tables based on any one of claims 1-7, characterized in that, include: Permission allocation module: used to build an RBAC+ABAC integrated permission model and assign row data permissions to different accounts; Permission matching and filtering module: Used to match permissions based on the query main table and the permissions of the currently logged-in account, filter duplicate permissions and permissions with small scope, and generate a minimum permission set; Dynamic SQL generation module: used to parse the relationship between master and child tables, convert complex conditions into SQL syntax trees, and generate SQL query fragments; Data control module: Used to intelligently concatenate dynamic SQL fragments into the appropriate position in the SQL query according to the data usage scenario, so as to realize row data control; Caching module: It adopts a distributed caching scheme based on JVM memory to cache account permission information, filtered permission results and dynamically organized SQL statements, and performs cache invalidation based on data change events.

9. A data control system supporting composite conditional rows in master and child tables according to claim 8, characterized in that, The dynamic SQL generation module includes: The master-child table association resolution unit is used to identify the association relationship and connection method between the master table and the child table; The compound conditional compilation unit is used to convert logical expressions in permission rules into SQL syntax trees; The SQL fragment generation unit is used to generate executable SQL fragments based on the syntax tree.

Images