A method and system for implementing database row and column access control
By establishing relevant table structures and intermediate proxy layers in the database, the database row-rank permission control is simplified, the problems of complexity, performance overhead and maintenance difficulty in the existing technology are solved, and efficient fine-grained access control and dynamic permission management are realized.
Patent Information
- Application Number
- CN202411148185.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-21
- Publication Date
- 2024-12-13
- Estimated Expiration
- 2044-08-21
AI Technical Summary
The existing database rank-rank permission control technology has problems such as complex implementation, high performance overhead, high maintenance and difficulty in dynamic adjustment, especially in large-scale and complex systems, which affect system stability and user experience.
By establishing a table structure related to the permission control database, including user information table, row permission management table, column permission management table, etc., an intermediate agent is used to identify user access rights and generate new SQL query statements to simplify the permission verification process and reduce the database Direct changes.
It realizes that without changing the original project, setting fine-grained access control for users, ensuring data privacy and security, reducing execution overhead, improving performance indicators, simplifying the integration and maintenance process, and supporting dynamic adjustment of permission policies.
Smart Images
Figure CN118656874B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of database security and access control, and in particular to a method and system for implementing database row and column access control. Background Art
[0002] Fine-grained control of database row and column permissions is an important technology in database security management. Reasonable permission management can ensure that data is only accessed by authorized users, thereby ensuring and improving the security of the entire system and protecting privacy. Common methods and strategies for database permission management are mainly the following:
[0003] 1.1. User authentication and authorization:
[0004] User authentication and authorization are the basis for ensuring the security of database systems. Administrators establish an identification mechanism for database access by creating user accounts, and each account is assigned a unique username and password. By assigning appropriate permissions to each user account, administrators can precisely control user access and operation permissions to database objects. Administrators are advised to adopt password policies, including setting password complexity requirements, regularly updating passwords, and recording password history to enhance account security. Regular auditing and management of database accounts are also important measures, and accounts that are no longer needed or belong to former employees should be deleted in a timely manner to effectively prevent unauthorized access.
[0005] 1.2. Role authority management:
[0006] Role-based permission management simplifies and centralizes permission management through the concept of roles. Administrators can create different roles and assign specific permissions to each role, such as management roles and ordinary user roles. By assigning permissions to roles, users can indirectly obtain the corresponding permissions by being assigned to roles, avoiding the tedious process of setting permissions for each user one by one. Role management includes regular auditing and management of role permissions to ensure that the role permissions are consistent with actual needs and security policies. Some systems also support role inheritance, that is, one role can inherit the permissions of another role, which further simplifies permission management and maintenance.
[0007] 1.3. Fine-grained permission control:
[0008] Fine-grained permission control includes row-level permission control and column-level permission control, which are used to precisely control user access rights to data. Row-Level Security (RLS) implements permission control for a single row of data by defining access control policies for each row in a database table. It usually involves policy functions and policy definitions.
[0009] Column-Level Security (CLS) restricts user access to specific columns through views, dynamic query generation, or dedicated permission tables. The view method can create a view that only contains columns that users are allowed to access, and the dynamic query method generates a query statement that only contains columns that users are allowed to access based on user permissions.
[0010] Oracle provides row-level security and fine-grained access control, which is a typical implementation of database row and column permission control, ensuring refined data management and security. SQL Server provides row-level security and dynamic data masking functions, while PostgreSQL supports row-level and column-level security, allowing fine-grained permission control based on roles and policies. These functions help database management systems achieve higher security and flexibility.
[0011] Problems with traditional database-based fine-grained permission control:
[0012] 2.1. Implementation complexity: The existing row and column permission control mechanism has high technical complexity in the implementation process, especially in large-scale and complex systems. This requires developers to write and maintain a large number of permission policies and rules, which is not only time-consuming and labor-intensive, but also easily leads to system instability.
[0013] 2.2. Performance overhead: Fine-grained permission checks and dynamic query generation will significantly increase the system's performance overhead. In a high-concurrency environment, frequent permission checks may extend system response time and affect user experience. Especially in the case of complex permission policies, the consumption of system resources is more significant.
[0014] 2.3. Maintenance Difficulty: As the number of permission rules and policies continues to increase, it becomes more difficult to maintain and manage permissions. System administrators need to continuously update and optimize permission settings to ensure the security and effectiveness of the system. This requires sophisticated management and monitoring mechanisms, which are often difficult to achieve in actual operations.
[0015] 2.4. Dynamic adjustment: When user permissions change frequently, permission policies need to be adjusted dynamically, which increases the complexity of the system. In an environment where permissions change frequently, the management of permission updates and synchronization becomes particularly important. If not handled properly, it may lead to permission lag or permission leakage, affecting system security. Summary of the invention
[0016] In view of the shortcomings of the prior art, the purpose of the present invention is to provide a method and system for implementing database row and column access control, which solves the problems of difficult integration and dynamic adjustment in the prior art. The system authenticates the user's access rights to the database through an intermediate agent and generates a new SQL query statement.
[0017] The object of the present invention is achieved through the following technical solution: a method for implementing database row and column access control, comprising the following steps:
[0018] Step S1, establish database tables related to the permission control database, the database tables include user information table (table name user_attr), row permission management table (table name permission_manage_row), column permission management table (table name permission_manage_column), global row permission management table (table name global_permission_row), global column permission management table (table name global_permission_column), global permission management table (table name global_permission_manage), permission group table (table name permission_group), permission group member table (table name permission_group_members), database row condition table (table name permission_condition_row), database column information table (table name columns_manage), database meta information table (table name middle_tables);
[0019] Step S2, synchronizing user information in the user database to the user information table;
[0020] Step S3: Create a user group and configure column access permissions and row access permissions for the user and group;
[0021] Step S4: for a certain SQL request, parse all tables and column fields used in the initial SQL statement;
[0022] Step S5, check the permission permission field in the database meta-information table to determine whether the permission check switch is turned on. If the permission check switch is not turned on, skip the permission check and directly use the initial SQL statement as a new SQL statement to enter step S9; if the permission check switch is turned on, continue with the following steps;
[0023] Step S6: Check the column access permission. If there is no permission, return to the user that there is no query permission and end this SQL request; otherwise, continue with the following steps;
[0024] Step S7: Check the row permission condition. If there is no permission, return to the user that there is no query permission and end this SQL request; otherwise, continue with the following steps;
[0025] Step S8, concatenating the row permission condition queried in step S7 into the initial SQL statement as a new SQL statement;
[0026] Step S9: Use the new SQL statement to query the database and return the query result to the user.
[0027] Furthermore, the step S2 is specifically as follows:
[0028] Synchronize the user information in the user database that needs to control permissions to the user information table. The user information at least includes the primary key ID representing the user. When the user information data in the user database is changed, synchronize the user information table in the permission control database to ensure that the permissions of all users can be controlled.
[0029] Synchronize all table information and table field meta-information of the user database to the permission control database meta-information table and database column information table, start the timer, synchronize data regularly, and ensure that all table information can be set with relevant access permissions.
[0030] Furthermore, the step S3 comprises:
[0031] Create a permission group table according to user needs, group users, and write users of the same type into the permission group member table to facilitate creating the same permissions for users of the same type;
[0032] Configure the global column permission management table. The table field is the permission association ID. If the user ID or permission group ID exists in the global column permission management table, it means that this user or permission group has access rights to all columns.
[0033] Configure the column permission management table to record the permission association ID and column ID. If there is a column ID for the user ID of the user or the ID of the permission group, it means that the user or group has the access permission to this database column. If there is no corresponding column ID, it means that the user or group does not have the access permission to this database column.
[0034] Furthermore, the step S6 is specifically as follows:
[0035] Check the global column permission management table to see whether the user ID and the permission group ID list to which the user belongs are in the global column permission management table. If they are in the global column permission management table, it means that the user has global column permission, and continue with step S7; if they are not in the global column permission management table, continue with column permission query;
[0036] Query the column permission management table to query all database columns that can be accessed by the user ID and the list of permission group IDs to which the user belongs, represented by set S; determine whether the column fields parsed in step S4 in this SQL statement are all in set S; if there are non-existent column fields, it means that there is no column permission, and return to the user that there is no column permission, and end this SQL request; if they all exist, continue to step S7.
[0037] Furthermore, the step S7 is specifically as follows:
[0038] Check the global row permission management table to check the user ID and the permission group ID list to which the user belongs. If the global row permission management table contains at least one item of the user or permission group ID list, it means that the user has the global row condition, and the row condition "1=1" is returned, and step S7 ends; otherwise, continue with the following check;
[0039] Check the row permission management table, determine all table names parsed in step S4 in this SQL statement, search for all row conditions in the ID list of the user ID and the user's permission group, if not found, it means there is no row permission condition, return to the user that there is no row permission, and end this SQL request; if found, splice all the row conditions together and return to step S8.
[0040] A database row and column access control implementation system includes the following modules:
[0041] Backend management module, used to synchronize information in the user database to the permission control database, create user groups, configure database row and column access permissions for users and groups, configure global database row and column access permissions for users and groups, and turn permission control on or off;
[0042] The permission judgment module is used to parse all tables and columns used in SQL statements, determine whether the user has column access permissions, add the user's row conditions to the original SQL statement, and return the new SQL statement query results to the user.
[0043] Furthermore, the background management module also provides a REST-style interface, including: an administrator login and account creation interface; an interface for creating and managing groups, an interface for adding and editing column permissions for users and groups, an interface for creating and editing row conditions, and an interface for adding or deleting row conditions for users and groups.
[0044] Furthermore, the permission configuration of the background management module is specifically as follows:
[0045] Permission switch: operate the database meta-information table, modify the permission fields, start and close the permission switch of a table; operate the database global permission management table, open or close the global switch of rows and columns;
[0046] Column permission configuration: operate the global column permission management table to configure the global column access permissions of users and groups, indicating that the user has access permissions to all columns; operate the column permission management table to configure the column access permissions of users and groups;
[0047] Row permission configuration: operate the global row permission management table to configure whether users and groups have global row conditions; operate the row permission management table to configure row permissions for users and groups.
[0048] The present invention also provides an electronic device, comprising a memory and a processor, wherein the memory is coupled to the processor; wherein the memory is used to store program data, and the processor is used to execute the program data to implement the method for implementing database row and column access control.
[0049] The present invention also provides a computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, the method for implementing the database row and column access control is implemented.
[0050] The beneficial effects of the present invention are:
[0051] 1. The present invention can set fine-grained access control permissions for project users without changing the original project to ensure data privacy and security.
[0052] 2. The present invention does not need to modify the original database, has a small amount of code, is simple to integrate, and has flexible configuration.
[0053] 3. The execution overhead caused by the present invention is small, and the performance index is better than the traditional method of controlling user permissions at the database level. BRIEF DESCRIPTION OF THE DRAWINGS
[0054] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings in the following description are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative labor.
[0055] Figure 1 The present invention is an overall flow chart of a method and system for implementing database row and column access control;
[0056] Figure 2 A schematic diagram of the architecture of a method and system for implementing database row and column access control according to the present invention;
[0057] Figure 3 A schematic diagram of a backend management module of a method and system for implementing database row and column access control according to the present invention;
[0058] Figure 4 The present invention is a method for implementing database row and column access control and an overall flow chart of the authority verification of the system;
[0059] Figure 5 A flowchart of column permission checking of a method and system for implementing database row and column access control of the present invention;
[0060] Figure 6The present invention provides a row condition checking flow chart of a method and system for implementing database row and column access control. DETAILED DESCRIPTION
[0061] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present invention. Instead, they are merely examples of devices and methods consistent with some aspects of the present invention as detailed in the appended claims.
[0062] The terms used in the present invention are only for the purpose of describing specific embodiments and are not intended to limit the present invention. The singular forms "a", "the" and "the" used in the present invention and the appended claims are also intended to include plural forms unless the context clearly indicates other meanings. It should also be understood that the term "and / or" used herein refers to and includes any or all possible combinations of one or more associated listed items.
[0063] It should be understood that although the terms first, second, third, etc. may be used in the present invention to describe various information, these information should not be limited to these terms. These terms are only used to distinguish the same type of information from each other. For example, without departing from the scope of the present invention, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the word "if" as used herein may be interpreted as "at the time of" or "when" or "in response to determining".
[0064] The present invention is described in detail below in conjunction with the accompanying drawings. In the absence of conflict, the features of the following embodiments and implementations can be combined with each other.
[0065] The embodiment of the present invention provides a method for implementing database row and column access control, such as Figure 1 As shown, the following steps are included:
[0066] 1. Establish database tables related to the permission control database, including user information table (table name user_attr), row permission management table (table name permission_manage_row), column permission management table (table name permission_manage_column), global row permission management table (table name global_permission_row), global column permission management table (table name global_permission_column), global permission management table (table name global_permission_manage), permission group table (table name permission_group), permission group member table (table name permission_group_members), database row condition table (table name permission_condition_row), database column information table (table name columns_manage), and database meta information table (table name middle_tables).
[0067] 2. Synchronize user information in the user database to the user information table.
[0068] 2.1. Synchronize the user information in the user database that needs to control permissions to the user information table. User information should at least include the primary key user_id that represents the user. When the data in the user database changes, it needs to be synchronized to the user information table in this method at the same time to ensure that the permissions of all users can be effectively controlled.
[0069] 2.2. Synchronize the metadata of all tables in the user database to the database metadata table, and synchronize the column field information of all tables in the user database to the database column information table; start the timer and synchronize data regularly to ensure that all information in the tables in the user database can be set with relevant access permissions.
[0070] 3. Create user groups and configure column and row access permissions for users and groups.
[0071] 3.1. Create a permission group table according to user needs, group users, and write users of the same type into the permission group member table to facilitate creating the same permissions for users of the same type.
[0072] 3.2. Configure the global column permission management table. The main table field is the permission association ID. If the user ID or permission group ID exists in the global column permission management table, it means that this user or permission group has access rights to all columns.
[0073] 3.3. Configure the column permission management table, which mainly records the permission association ID and column ID. If there is a column ID for the user ID of the user or the ID of the permission group, it means that the user or group has the access permission to this database column. If there is no corresponding column ID, it means that the user or group does not have the access permission to this database column.
[0074] 4. For a certain SQL request, parse all tables and column fields used in the SQL statement, such as Figure 4 shown.
[0075] 5. Check the permission field in the database meta-information table to determine whether the permission check switch is turned on. If it is not turned on, directly execute the SQL statement to obtain data. If the permission is turned on, continue with the following steps.
[0076] 6. Check column access permissions. If no permission is granted, return a message indicating that the user has no query permission and terminate the SQL request. Otherwise, proceed to step 7.
[0077] Among them, Figure 5 As shown, the detailed steps of the column permission checking method are as follows:
[0078] 6.1. Get all the groups that the user belongs to based on the user ID.
[0079] 6.2. Check the global column permission management table to determine whether the global column permission is enabled. If the global column permission is enabled, execute the following step S3; if not, execute step S4.
[0080] 6.3. Check the user ID and the group ID list of the user's group. If the global column permission management table contains one of the user ID or group ID list, it means that the user has global column permissions, and the column permission check ends.
[0081] 6.4. Check the column permission management table to obtain the list of all columns and fields of the user and group. Determine whether all the columns and fields in the SQL statement are in the set. If a column is not in the set, it means that the user has no permission to this column, and this SQL query ends and returns no access permission to the user; if all columns are in the set, it means that this SQL query has column access permission.
[0082] 7. Check row access permissions. If no permission is granted, return a message indicating that the user has no query permission and terminate the SQL request. Otherwise, proceed to step 8.
[0083] Among them, Figure 6 As shown, the detailed steps of the row condition checking method are as follows:
[0084] 7.1. Get all the groups that the user belongs to based on the user ID.
[0085] 7.2. Check the global row permission management table to determine whether the global row permission is enabled. If the global row permission is enabled, proceed to step 7.3; if not, proceed to step 7.4.
[0086] 7.3. Check the user ID and the group ID list of the user's group. If the global row permission management table contains one of the user ID or group ID list, it means that the user has the global row condition. Return the row condition "1=1" and end this step; otherwise, execute step 7.4.
[0087] 7.4. Check the row permission management table, determine all table names parsed in this SQL statement, and search for all row conditions of the user ID and the group ID list of the user's group. If not found, return empty, indicating that the user has no row conditions. If found, concatenate these row conditions together and return them to the subsequent step 8.
[0088] 8. Add the row condition found in step 7 to the original SQL statement as a new SQL statement.
[0089] 9. Use the new SQL statement to query the database and return the query results to the user.
[0090] like Figure 1 , Figure 2 As shown, an implementation system of a database row and column access control provided by an embodiment of the present invention includes:
[0091] Backend management module: mainly used to synchronize information in the user database to the permission control database, create user groups, configure database row and column access permissions for users and groups, configure global database row and column access permissions for users and groups, and turn permission control on and off. Provides REST-style interfaces, mainly including administrator login and account creation interfaces; create and manage group interfaces, add and edit user and group column permission interfaces, create and edit row condition interfaces, and add or delete row condition interfaces for users and groups.
[0092] Permission judgment module: mainly used to parse all tables and columns used in SQL statements, determine whether the user has row access rights and column access rights, add the user's row conditions to the original SQL statement, and return the new SQL statement query results to the user.
[0093] Among them, a database row and column access control implementation system provided by an embodiment of the present invention also includes:
[0094] Data middle platform: The user's business system is mainly responsible for processing front-end page query requests and business logic processing.
[0095] Permission control system: The row and column access control gateway system of the present invention, after the data center has processed the front-end business request, before operating the database, needs to go through the permission control system of the present invention to determine whether the user has the relevant permissions and add row permission conditions. Since the present invention is an independent system, it is not invasive to the user's original system and is easy to integrate.
[0096] Database: The user's database system stores all the user's data. The present invention has been mainly verified and tested in the MySQL database.
[0097] like Figure 2 FIG. 1 is a schematic diagram of the architecture of a system for implementing database row and column access control, which describes the relationship between the user system and the system of the present invention.
[0098] The architecture diagram shows the hierarchical structure of the system. First, there is the interface layer, which is responsible for providing services and receiving requests. The interface layer includes two parts: the network interface of the user system and the background management interface of the present invention. The background interface is used to manage the access permission configuration of the user system.
[0099] The business layer is the core of the architecture, mainly including the user system and the background management module. The background management module of the system of the present invention implements row permission control, column permission control and global permission control to ensure that different users can only access and operate authorized data and functions. This layer is responsible for configuring all business logic of user permissions to ensure the security of the system and the integrity of data.
[0100] The bottom layer is the storage layer, which contains the database and is used to persist all data in the storage system. The storage layer provides data support for the business layer and ensures secure storage and compliant access to data through refined permission control.
[0101] like Figure 3 As shown, a background management module of a database row and column access control implementation system, the operation and function of each module are explained as follows:
[0102] Administrator login: The person who configures user permissions needs to determine the user's administrator permissions. The operable personnel information exists in the database administrator table.
[0103] User management module: displays the basic information of all personnel in the user system, and can edit groups to classify the same type of personnel into the same group.
[0104] Permission group management: Display and create all permission groups, add or delete users in the group, and enable or disable permission groups.
[0105] Data table management: Displays all database tables used by the user system, and can edit and configure column and row permissions for these tables. Column permissions are configured as follows: On the column permission configuration page, display all column field names of a table, and for each field, configure the users and permission groups allowed to access. Row permissions are configured as follows: On the row permission configuration page, create and display row permission conditions for a table, and then configure associated users and permission groups.
[0106] Global configuration: Global column configuration page, configure users and permission groups with access rights to the entire database. Global row configuration page, configure users and permission groups with global row access rights.
[0107] The main contents of the permission configuration for user operations in the background management module are:
[0108] Permission switch: operate the database meta-information table, modify the permission field, start and close the permission switch of a table. Operate the database global permission management table, and open or close the global switch of rows and columns.
[0109] Column permission configuration: Operate the global column permission management table to configure the global column access permissions of users and groups, which means that users have access permissions to all columns. Operate the column permission management table to configure the column access permissions of users and groups.
[0110] Row permission configuration: Operate the global row permission management table to configure whether users and groups have global row conditions. Operate the row permission management table to configure row permissions for users and groups.
[0111] The present invention does not rely on the authority control mechanism of the database itself, reduces the modification of the existing database system, and simplifies the implementation process of authority control.
[0112] By introducing the proxy layer, the present invention adopts efficient algorithms and optimization strategies when identifying user permissions and generating new SQL query statements, significantly reducing the impact of fine-grained permission checks on system performance and improving user experience. The proxy layer centrally manages permission policies, making it easier to update and maintain permission rules. Administrators do not need to perform complex modifications and debugging within the database, thereby reducing the difficulty of system maintenance.
[0113] In addition, the present invention supports dynamic adjustment of user rights. The proxy layer can monitor and update the rights policy in real time to ensure that changes in user rights can be reflected in database access in a timely manner. This dynamic adjustment capability improves the flexibility and adaptability of the system and meets the rights management requirements in different application scenarios.
[0114] An embodiment of the present invention also provides an electronic device, including a memory and a processor, wherein the memory is coupled to the processor; wherein the memory is used to store program data, and the processor is used to execute the program data to implement a method for implementing database row and column access control of any of the embodiments.
[0115] An embodiment of the present invention further provides a computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, a method for implementing database row and column access control in any of the embodiments is implemented.
[0116] The computer-readable storage medium may be an internal storage unit of any device with data processing capability described in any of the aforementioned embodiments, such as a hard disk or a memory. The computer-readable storage medium may also be any device with data processing capability, such as a plug-in hard disk, a smart media card (SMC), an SD card, a flash card, etc. equipped on the device. Furthermore, the computer-readable storage medium may also include both an internal storage unit of any device with data processing capability and an external storage device. The computer-readable storage medium is used to store the computer program and other programs and data required by any device with data processing capability, and may also be used to temporarily store data that has been output or is to be output.
[0117] The above description is only an embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent substitution, improvement, etc. made within the spirit and principle of the present invention should be included in the scope of the claims of the present invention.
Claims
1. A method for implementing database row and column access control, characterized in that: The following steps are involved: (S1) Establishing database tables related to the permission control database, the database tables including a user information table, a row permission management table, a column permission management table, a global row permission management table, a global column permission management table, a global permission management table, a permission group table, a permission group member table, a database row condition table, a database column information table, and a database meta information table; (S2) Synchronizing user information in the user database into the user information table; (S3) Create user groups and configure column access permissions and row access permissions for users and groups; The step (S3) comprises: Create a permission group table based on user needs, group users, and write users of the same type into the permission group member table; Configure the global column permission management table. The table field is the permission association ID. If the user ID or permission group ID exists in the global column permission management table, it means that this user or permission group has access rights to all columns. Configure the column permission management table to record the permission association ID and column ID. If a column ID exists for a user ID or a permission group ID, it means that the user or group has the access permission to this database column. If there is no corresponding column ID, it means that the user or group does not have the access permission to this database column. (S4) For a certain SQL request, parse all tables and column fields used in the initial SQL statement; (S5) Checking the permission permission field in the database meta-information table to determine whether the permission check switch is turned on. If the permission check switch is not turned on, skipping the permission check, directly using the initial SQL statement as a new SQL statement, and entering step (S9); if the permission check switch is turned on, continuing with the following steps; (S6) Check the column access permission. If there is no permission, return to the user that there is no query permission and end this SQL request; otherwise, continue with the subsequent steps; (S7) Check the row permission condition. If there is no permission, return to the user that there is no query permission and end this SQL request; otherwise, continue with the subsequent steps; (S8) splicing the row permission condition queried in step (S7) into the initial SQL statement as a new SQL statement; (S9) Using the new SQL statement to query the database, the query result is returned to the user.
2. A method for implementing database row and column access control according to claim 1, characterized in that: The step (S2) is specifically: Synchronize the user information in the user database that needs to control permissions to the user information table. The user information at least includes the primary key ID representing the user. When the user information data in the user database is changed, synchronize the user information table in the permission control database to ensure that the permissions of all users can be controlled. Synchronize all table information and table field meta-information of the user database to the permission control database meta-information table and database column information table, start the timer, synchronize data regularly, and ensure that all table information can be set with relevant access permissions.
3. A method for implementing database row and column access control according to claim 1, characterized in that: The step (S6) is specifically: Check the global column permission management table to see whether the user ID and the permission group ID list to which the user belongs are in the global column permission management table. If they are in the global column permission management table, it means that the user has global column permission, and continue with step (S7); if they are not in the global column permission management table, continue with column permission query; Query the column permission management table to query all database columns that can be accessed by the user ID and the list of permission group IDs to which the user belongs, represented by a set S; determine whether the column fields parsed in step (S4) in this SQL statement are all in the set S; if there are non-existent column fields, it means that there is no column permission, and return to the user that there is no column permission, and end this SQL request; if they all exist, continue to step (S7).
4. A method for implementing database row and column access control according to claim 1, characterized in that: The step (S7) is specifically: Check the global row permission management table to check the user ID and the permission group ID list to which the user belongs. If the global row permission management table contains at least one item of the user or permission group ID list, it means that the user has the global row condition, and the row condition "1=1" is returned, and the step (S7) ends; otherwise, continue with the following check; Check the row permission management table, determine all the table names parsed in step (S4) in this SQL statement, search for all the row conditions in the ID list of the user ID and the permission group to which the user belongs, if not found, it means there is no row permission condition, return to the user that there is no row permission, and end this SQL request; if found, splice all the row conditions together and return to step (S8).
5. A database row and column access control implementation system, characterized in that: Includes the following modules: Backend management module, used to synchronize information in the user database to the permission control database, create user groups, configure database row and column access permissions for users and groups, configure global database row and column access permissions for users and groups, and turn permission control on or off; The creating of user groups, configuring database row column access permissions for users and groups, and configuring global database row column access permissions for users and groups include: Create a permission group table based on user needs, group users, and write users of the same type into the permission group member table; Configure the global column permission management table. The table field is the permission association ID. If the user ID or permission group ID exists in the global column permission management table, it means that this user or permission group has access rights to all columns. Configure the column permission management table to record the permission association ID and column ID. If a column ID exists for a user ID or a permission group ID, it means that the user or group has the access permission to this database column. If there is no corresponding column ID, it means that the user or group does not have the access permission to this database column. The permission configuration of the background management module is specifically as follows: Permission switch: operate the database meta-information table, modify the permission fields, start and close the permission switch of a table; operate the database global permission management table, open or close the global switch of rows and columns; Column permission configuration: operate the global column permission management table to configure the global column access permissions of users and groups, indicating that the user has access permissions to all columns; operate the column permission management table to configure the column access permissions of users and groups; Row permission configuration: operate the global row permission management table to configure whether users and groups have global row conditions; operate the row permission management table to configure row permissions for users and groups; The permission judgment module is used to parse all tables and columns used in SQL statements, determine whether the user has column access permissions, add the user's row conditions to the original SQL statement, and return the new SQL statement query results to the user.
6. A database row and column access control implementation system according to claim 5, characterized in that: The backend management module also provides REST-style interfaces, including: administrator login and account creation interface; group creation and management interface, column permission interface for adding and editing users and groups, row condition creation and editing interface, and row condition interface for adding or deleting users and groups.
7. An electronic device comprising a memory and a processor, characterized in that: The memory is coupled to the processor; wherein the memory is used to store program data, and the processor is used to execute the program data to implement a method for implementing database row and column access control as described in any one of claims 1 to 4 above.
8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, a method for implementing database row and column access control as described in any one of claims 1 to 4 is implemented.