[go: up one dir, main page]

CN112463792B - Data authority control method and device - Google Patents

Data authority control method and device Download PDF

Info

Publication number
CN112463792B
CN112463792B CN202011318043.6A CN202011318043A CN112463792B CN 112463792 B CN112463792 B CN 112463792B CN 202011318043 A CN202011318043 A CN 202011318043A CN 112463792 B CN112463792 B CN 112463792B
Authority
CN
China
Prior art keywords
sql statement
segment
sql
module
keyword
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Active
Application number
CN202011318043.6A
Other languages
Chinese (zh)
Other versions
CN112463792A (en
Inventor
曹铠平
陈显健
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Guangdong Jingyi Information Technology Co ltd
Original Assignee
Aerospace Jingyi Guangdong Information Technology Co ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Aerospace Jingyi Guangdong Information Technology Co ltd filed Critical Aerospace Jingyi Guangdong Information Technology Co ltd
Priority to CN202011318043.6A priority Critical patent/CN112463792B/en
Publication of CN112463792A publication Critical patent/CN112463792A/en
Application granted granted Critical
Publication of CN112463792B publication Critical patent/CN112463792B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/22—Indexing; Data structures therefor; Storage structures
    • G06F16/2228—Indexing structures
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F21/00—Security arrangements for protecting computers, components thereof, programs or data against unauthorised activity
    • G06F21/60—Protecting data
    • G06F21/604—Tools and structures for managing or administering access control systems
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F2221/00—Indexing scheme relating to security arrangements for protecting computers, components thereof, programs or data against unauthorised activity
    • G06F2221/21—Indexing scheme relating to G06F21/00 and subgroups addressing additional information or applications relating to security arrangements for protecting computers, components thereof, programs or data against unauthorised activity
    • G06F2221/2141—Access rights, e.g. capability lists, access control lists, access tables, access matrices

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Software Systems (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • Automation & Control Theory (AREA)
  • Health & Medical Sciences (AREA)
  • Bioethics (AREA)
  • General Health & Medical Sciences (AREA)
  • Computer Hardware Design (AREA)
  • Computer Security & Cryptography (AREA)
  • Mathematical Physics (AREA)
  • Computational Linguistics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The invention discloses a data authority control method and device, and relates to the technical field of electronic information. The method comprises the steps of obtaining SQL sentences, dividing the SQL sentences with defined data authority policies to obtain a plurality of SQL sentence sections, traversing the SQL sentence sections one by one, judging whether the SQL sentence sections starting from a select are the first divided elements, dividing the SQL sentence sections again if not, analyzing the table names of the SQL sentence sections, screening out the SQL sentence sections with the table names defining the data authority policies, replacing the table names of the SQL sentence sections with the table names defining the data authority policies to be the select sentences added with authority conditions to obtain a plurality of changed SQL sentence sections, and reconnecting the changed SQL sentence sections to obtain target SQL sentence sections. According to the invention, the SQL statement generated by Hibernates is intercepted and modified to achieve data authority management, and authority management is carried out on certain types of data, so that a user can only inquire and manage user information under the department where the user is located.

Description

Data authority control method and device
Technical Field
The present invention relates to the field of electronic information technologies, and in particular, to a method and an apparatus for controlling data authority.
Background
The data authority control is an important ring in the middle and background management system all the time, and a reasonable authority management strategy can bring great convenience to the system. Most developers currently choose to create rights management modules based on a Role-based access control (RBAC) rights model. RBAC is an effective access control way for implementing enterprise-oriented security policies, the basic idea is that various rights to system operations are not directly granted to specific users, but a role set is established between the user set and the rights set. Each role corresponds to a respective set of permissions. Once a user has been assigned the appropriate role, the user has all the operating rights for that role. The method has the advantages that the operation of assigning the rights is not needed when the user is created every time, only the corresponding role of the user is assigned, and the rights of the role are changed much less than those of the user, so that the rights management of the user is simplified, and the overhead of the system is reduced.
However, the RBAC model can only manage menu directories or other rights stored in a database (such as adding or deleting functional rights), and cannot manage rights of certain types of data. For example, if a user has the authority of a 'user management' menu, the user can inquire the information of all users and can randomly modify the information, but ideally, the user can only manage the user information under the department where the user is located.
Disclosure of Invention
The invention aims to provide a data authority control method, which is used for realizing accurate control on data lines according to a configurable data strategy, intercepting SQL sentences generated by Hibernate and modifying the SQL sentences according to actual configuration so as to achieve the aim of controlling the data authority.
In order to achieve the above purpose, the embodiment of the invention provides a data authority control method, which comprises the steps of obtaining an SQL sentence, dividing the SQL sentence with a defined data authority strategy to obtain a plurality of SQL sentence segments, traversing the SQL sentence segments one by one, judging whether the SQL sentence segments starting with a select are the first element after division, if not, carrying out secondary division on the SQL sentence segments, if so, analyzing the table names of the SQL sentence segments, screening out the SQL sentence segments with the table names defining the data authority strategy, replacing the table names of the SQL sentence segments with the table names defining the data authority strategy to be select sentences with authority conditions added, obtaining a plurality of modified SQL sentence segments, and reconnecting the modified SQL sentence segments to obtain a target SQL sentence segment.
The method comprises the steps of analyzing table names of SQL statement sections, screening out the SQL statement sections with the table names defining a data authority policy, and screening out the SQL statement sections with the table names defining the data authority policy by searching and marking the starting positions, the interval positions and the cut-off positions of the SQL statement sections, wherein the starting positions comprise positions of from keywords, the cut-off positions comprise final indexes of the SQL statement sections, and the SQL statement sections with the table names defining the data authority policy are screened out by combining the starting positions, the interval positions and the cut-off positions.
The method comprises the steps of searching and marking the starting position, the interval position and the cut-off position of a table in an SQL statement segment, searching a from keyword by using a regular expression, searching a separator keyword or a separation character by using the regular expression, wherein the separator keyword comprises left, join and/or on, the separation character comprises comma, searching a window, group by, order by and/or a division keyword by using the regular expression, using the position of the from keyword as the starting position of the table, using the position of the separator keyword and/or the separation character as the interval position of the table, using the position of the separator keyword and/or the separation character as the cut-off position of the table, and using the final index of the SQL statement segment as the cut-off position of the table when any keyword of the window, group by, order by and division is not searched.
Specifically, the method for acquiring the SQL statement comprises the steps of creating EmptyInterceptor interfaces provided by an interceptor for realizing Hibernate, and rewriting onPrePareStatement the generated SQL statement.
Specifically, the SQL statement with the defined data authority policy is divided, so that the SQL statement with the defined data authority policy is divided in a plurality of SQL statement segments, and the used regular expression comprises a regular expression for judging the start of sub-query (.
The embodiment of the invention also provides a data authority control device which comprises an acquisition module, a segmentation module, a judgment module, a circulation module, a screening module and a replacement module, wherein the acquisition module is used for acquiring an SQL sentence, the segmentation module is used for segmenting the SQL sentence with a defined data authority strategy to obtain a plurality of SQL sentence segments, the judgment module is used for traversing the SQL sentence segments one by one and judging whether the SQL sentence segments started by a select are the first elements after segmentation, the circulation module is used for enabling the SQL sentence segments started by the select and being the first elements to enter circulation for secondary segmentation, the screening module is used for analyzing the table names of the SQL sentence segments started by the select and not being the first elements and screening out the SQL sentence segments with the table names defining the data authority strategy, the replacement module is used for replacing the table names of the SQL sentence segments with the table names defining the data authority strategy to be the selected sentence segments with the authority conditions added, and the reshaping module is used for reconnecting the modified SQL sentence segments to obtain the target SQL sentence segments.
The screening module comprises a searching and marking module and a combining module, wherein the searching and marking module is used for searching and marking the starting position, the interval position and the cut-off position of the table in the SQL sentence segment, the starting position comprises the position of the from key word, the cut-off position comprises the last index position of the SQL sentence segment, and the combining module is used for combining the starting position, the interval position and the cut-off position to screen the SQL sentence segment with the table name defining the data authority strategy.
The search marking module comprises a starting position search module, an interval position search module and a position marking module, wherein the starting position search module is used for searching a from keyword by using a regular expression, the interval position search module is used for searching a separator keyword or a separator character by using the regular expression, the separator keyword comprises left, join and/or on, the separator character comprises comma, the ending position search module is used for searching a where, group by, order by and/or a moving keyword by using the regular expression, the position marking module is used for taking the position of the from keyword as the starting position of a table, the separator keyword and/or the separator character as the interval position of the table, the position of the where, group by, order by and/or the moving keyword are taken as the ending position of the table, and when any keyword of the where, group by, order by and moving keyword is not searched, the last index of the SQL segment is taken as the ending position of the table.
The embodiment of the invention also provides electronic equipment, which comprises a processor, a memory and a bus, wherein the bus is used for connecting the processor and the memory, the memory is used for storing operation instructions, and the processor is used for executing the data authority control method by calling the operation instructions.
The embodiment of the invention also provides a computer storage medium for storing computer instructions which, when run on a computer, enable the computer to perform the data authority control method.
The embodiment of the invention has the following beneficial effects:
The embodiment of the invention provides a data authority control method and device, wherein the method comprises the steps of obtaining an SQL sentence, dividing the SQL sentence with a defined data authority strategy to obtain a plurality of SQL sentence segments, traversing the SQL sentence segments one by one, judging whether the SQL sentence segments starting with a select are the first divided element, adding the data authority strategy for the SQL sentence segments if not, dividing again, analyzing the table names of the SQL sentence segments, screening out the SQL sentence segments with the table names defining the data authority strategy, replacing the table names of the SQL sentence segments with the table names defining the data authority strategy for select sentences with authority conditions, obtaining a plurality of changed SQL sentence segments, and reconnecting the changed SQL sentence segments to obtain a target SQL sentence segment.
Compared with the prior art, the invention can achieve data authority management by intercepting and modifying the SQL statement generated by Hibernates, and can realize authority management on certain types of data, so that a user can only inquire and manage user information under the department where the user is located. The invention can reduce the complexity of data authority control development in WEB application, add the control of the data authority after the development is completed, adjust the data control authority at any time and rarely even do not need to modify the applied code, and can enhance the stability and the robustness of the data authority control. After the data authority control scheme code completes the test, only the defined data strategy is required to be tested and adjusted for the data authority control in the subsequent project.
Drawings
In order to more clearly illustrate the technical solutions of the present invention, the drawings that are needed in the embodiments will be briefly described below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and that other drawings can be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a flow chart of a method for controlling data rights according to an embodiment of the present invention;
FIG. 2 is a schematic diagram of a data authority control device according to an embodiment of the present invention;
Fig. 3 is a schematic structural diagram of an electronic device according to an embodiment of the present invention.
Detailed Description
The following description of the embodiments of the present invention will be made with reference to the accompanying drawings, in which it is evident that the embodiments described are only some embodiments of the present invention, but not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
It should be understood that the step numbers used herein are for convenience of description only and are not limiting as to the order in which the steps are performed.
It is to be understood that the terminology used in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention. As used in this specification and the appended claims, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise.
The terms "comprises" and "comprising" indicate the presence of stated features, integers, steps, operations, elements, and/or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and/or groups thereof.
The term "and/or" refers to any and all possible combinations of one or more of the associated listed items, and includes such combinations.
In order to facilitate understanding of the embodiments of the present invention by those skilled in the art, the following description will explain the related art content of the embodiments of the present invention.
The data authority policy indicates what kind of constraint is added to the main body (table or view), for example, when the user table is queried, the department ID is added to the department where the current login user is located, that is, the data authority policy.
The Hibernate is an object relation mapping framework of open source codes, which encapsulates the JDBC with very light weight, builds the mapping relation between POJO and database table, is a fully automatic orm framework, hibernate can automatically generate SQL sentences and automatically execute, so that Java programmers can operate the database by using object programming thinking as desired. The Hibernate can be applied to any occasion using JDBC, can be used in Java client programs, and can also be used in Web applications of servlets/JSP, and most revolutionarily, the Hibernate can replace CMP in JaveEE architecture applying EJB to finish the re-conception of data persistence.
Regular expressions, (Regular Expression, also known as regular expressions, often abbreviated in code as regex, regex p, or RE), are commonly used to retrieve, replace text that conforms to a certain pattern (rule). A regular expression is a logic formula for operating on a character string, and a rule character string is formed by a plurality of specific characters defined in advance and a combination of the specific characters, and the rule character string is used for expressing a filtering logic for the character string.
The following list of rule strings used in parsing SQL statements:
1. the regular expression between brackets [ - (- ])
2. The regular expression of the table name is [ a-zA-Z_ 0-9] +
3. Judging whether the SQL statement starts with a regular expression of SELECT:
\A(\s*|\()\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]
4. judging a regular expression of sub-query start:
(?!\A)\(\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]
5. regular expressions matching FROM keywords:
(\s+|[*])[fF][rR][oO][mM](\s+|(?=\())
6. Regular expressions matching UPDATE keywords:
\s*[uU][pP][dD][aA][tT][eE]\s+(?!(\s|[(]))
7. regular expressions matching DELETE keywords:
\s*[dD][eE][lL][eE][tT][eE]\s+(?!(\s|[(]))
8. regular expressions matching DELETE FROM keywords:
[dD][eE][lL][eE][tT][eE]\s+[fF][rR][oO][mM]\s+(?!(\s|[(]))
9. regular expressions matching the WHERE key:
(\)|\s)[wW][hH][eE][rR][eE](\s+|(?=\())
10. Regular expressions matching the HAVING keywords:
(\)|\s)[hH][aA][vV][iI][nN][gG](\s+|(?=\())
11. Regular expressions matching ORDERBY keywords:
(\)|\s)[oO][rR][dD][eE][rR]\s+[bB][yY](\s+|(?=\())
12. regular expressions matching GROUP BY keywords:
(\)|\s)[gG][rR][oO][uU][pP]\s+[bB][yY](\s+|(?=\())
13. Regular expressions matching FOR UPDATE keywords:
(\)|\s+)[fF][oO][rR]\s+[uU][pP][dD][aA][tT][eE](\s+|(?=\())
14. regular expressions matching the UNION keywords:
(\)|\s+)[uU][nN][iI][oO][nN](\s+|(?=\())
15. regular expressions matching the UNION ALL keywords:
(\)|\s+)[uU][nN][iI][oO][nN]\s+[aA][lL][lL](\s+|(?=\())
16. regular expressions matching MINUS keywords:
(\)|\s+)[mM][iI][nN][uU][sS](\s+|(?=\())
17. Regular expressions matching the INTERSECT keywords:
(\)|\s+)[iI][nN][tT][eE][rR][sS][eE][cC][tT](\s+|(?=\())
18. Regular expressions matching ON keywords:
(\)|\s+)[oO][nN]\s*
19. regular expressions matching JOIN keywords:
(\)|\s+)[jJ][oO][iI][nN]\s+(?!(\s|[(]))
20. regular expressions matching the LEFT JOIN key:
(\)|\s+)[lL][eE][fF][tT]\s+[jJ][oO][iI][nN](\s+|(?=\())
21. regular expressions matching RIGHT JOIN keywords:
(\)|\s+)[rR][iI][gG][hH][tT]\s+[jJ][oO][iI][nN](\s+|(?=\())
22. regular expressions matching FULL JOIN keywords:
(\)|\s+)[fF][uU][lL][lL]\s+[jJ][oO][iI][nN](\s+|(?=\())
23. regular expressions matching the LEFT output JOIN key:
(\)|\s+)[lL][eE][fF][tT]\s+[oO][uU][tT][eE][rR]\s+[jJ][oO][iI][nN](\s+|(?=\())
24. Regular expressions matching RIGHT OUTER JOIN keywords:
(\)|\s+)[rR][iI][gG][hH][tT]\s+[oO][uU][tT][eE][rR]\s+[jJ][oO][iI][nN](\s+|(? =\())
25. Regular expressions matching the FULL OUTER JOIN key:
(\)|\s+)[fF][uU][lL][lL]\s+[oO][uU][tT][eE][rR]\s+[jJ][oO][iI][nN](\s+|(?=\())
26. regular expressions matching INNER JOIN keywords:
(\)|\s+)[iI][nN][nN][eE][rR]\s+[jJ][oO][iI][nN](\s+|(?=\())
the data authority control method provided by the embodiment of the invention will be described and illustrated in detail through several specific embodiments.
Referring to fig. 1, fig. 1 is a flowchart of a data authority control method according to an embodiment of the invention. To achieve the above object, an embodiment of the present invention provides a data authority control method, including:
step S110, an SQL sentence is acquired.
The means for obtaining the SQL statement comprises creating EmptyInterceptor interfaces provided by the interceptor for realizing Hibernate, and rewriting onPrePareStatement the SQL statement generated by interception.
Step S120, the SQL sentence with the defined data authority strategy is segmented to obtain a plurality of SQL sentence segments.
In this embodiment, a regular expression is used to analyze an SQL statement, and the SQL statement having a defined data authority policy is segmented, where the used regular expression includes a regular expression for determining the start of a sub-query:
(?!\A)\(\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]。
For example, an SQL statement "select ename,job,sal from emp where job＝(select job from emp where ename＝'SCOTT')and sal>(select sal from emp where ename＝'JAMES')", is analyzed by regular expression and then partitioned into five sections, namely a first section "SELECT ENAME, job, sal from emp where job = (", a second section "select job from EMP WHERE ENAME = 'SCOTT'" third section ") and sal > (", a fourth section "SELECT SAL from EMP WHERE ENAME = 'JAMES'", a fifth section ")". The first segment is the first element after segmentation.
Step S130, traversing the SQL sentence segments one by one, and judging whether the SQL sentence segment starting with the select is the first element after segmentation.
If the SQL statement segment is started with select but not the first element after segmentation, the process returns to step S120, and if the SQL statement segment is started with select and the first element after segmentation, step S140 is executed.
The first element after segmentation, representing the first of several segments of statements obtained by segmentation using regular expression analysis, performs this determination because the SQL statement segment from which the second selection begins may also have sub-queries in it.
And step S140, analyzing the table name of the SQL statement section, and screening out the SQL statement section with the table name defining the data authority policy.
The method comprises the steps of firstly, searching and marking a starting position, an interval position and a cut-off position of a table in an SQL statement section by judging whether the table name of the SQL statement section defines a data authority strategy or not, wherein the starting position comprises a position of a from keyword, the cut-off position comprises a last index position of the SQL statement section, and the starting position, the interval position and the cut-off position are combined to screen the SQL statement section with the table name defining the data authority strategy.
Step S150, replacing the SQL statement segment with the table name defining the data authority policy with the table name of the selected statement added with the authority condition to obtain a plurality of modified SQL statement segments. The sphere clause of this select statement is a well defined data authority policy for the current table.
And step S160, reconnecting the modified SQL sentence segment to obtain a target SQL sentence segment. As a data authority control strategy based on the Hibernate, the target SQL statement is finally provided to the Hibernate.
The method comprises the steps of searching and marking the starting position, the interval position and the cut-off position of a table in an SQL sentence segment, searching a from keyword by using a regular expression, searching a separator keyword or a separator character by using the regular expression, wherein the separator keyword comprises left, join and/or on, the separator character comprises comma (,), searching a where, a group by, an order by and/or a having keyword by using the regular expression, taking the position of the from keyword as the starting position of the table, taking the position of the separator keyword and/or the separator character as the interval position of the table, taking the position of the where, the group by, the order by and/or the having keyword as the cut-off position of the table, and taking the last index of the SQL sentence segment as the cut-off position of the table when any keyword of where the word is found is not found.
The regular expression used for searching the from keyword is as follows:
(\s+|[*])[fF][rR][oO][mM](\s+|(?=\())。
The purpose of searching the keywords of where, group by, order by, have, etc. is to search the cut-off position of the table which may exist, and the regular expression used includes:
Regular expressions matching the WHERE key:
(\)|\s)[wW][hH][eE][rR][eE](\s+|(?=\())
regular expressions matching the HAVING keywords:
(\)|\s)[hH][aA][vV][iI][nN][gG](\s+|(?=\())
Regular expressions matching ORDERBY keywords:
(\)|\s)[oO][rR][dD][eE][rR]\s+[bB][yY](\s+|(?=\())
Regular expressions matching GROUP BY keywords:
(\s) |gG ] [ rR ] [ oO ] [ uU ] [ pP ] [ bB ] [ yY ] (\s+ | (.
After searching the from keyword, other regular expressions need to be searched instead, including:
Regular expressions matching the UNION keywords:
(\)|\s+)[uU][nN][iI][oO][nN](\s+|(?=\())
Regular expressions matching the UNION ALL keywords:
(\)|\s+)[uU][nN][iI][oO][nN]\s+[aA][lL][lL](\s+|(?=\())
regular expressions matching MINUS keywords:
(\)|\s+)[mM][iI][nN][uU][sS](\s+|(?=\())
regular expressions matching the INTERSECT keywords:
(\)|\s+)[iI][nN][tT][eE][rR][sS][eE][cC][tT](\s+|(?=\())
Regular expressions matching ON keywords:
(\)|\s+)[oO][nN]\s*
regular expressions matching JOIN keywords:
(\)|\s+)[jJ][oO][iI][nN]\s+(?!(\s|[(]))
Regular expressions matching the LEFT JOIN key:
(\)|\s+)[lL][eE][fF][tT]\s+[jJ][oO][iI][nN](\s+|(?=\())
Regular expressions matching RIGHT JOIN keywords:
(\)|\s+)[rR][iI][gG][hH][tT]\s+[jJ][oO][iI][nN](\s+|(?=\())
regular expressions matching FULL JOIN keywords:
(\)|\s+)[fF][uU][lL][lL]\s+[jJ][oO][iI][nN](\s+|(?=\())
Regular expression of LEFT output JOIN key:
(\)|\s+)[lL][eE][fF][tT]\s+[oO][uU][tT][eE][rR]\s+[jJ][oO][iI][nN](\s+|(?=\())
regular expression of RIGHT OUTER JOIN key:
(\)|\s+)[rR][iI][gG][hH][tT]\s+[oO][uU][tT][eE][rR]\s+[jJ][oO][iI][nN](\s+|(? =\())
regular expression of FULL OUTER JOIN key:
(\)|\s+)[fF][uU][lL][lL]\s+[oO][uU][tT][eE][rR]\s+[jJ][oO][iI][nN](\s+|(?=\())
regular expression of INNER JOIN keywords:
(\)|\s+)[iI][nN][nN][eE][rR]\s+[jJ][oO][iI][nN](\s+|(?=\())
Referring to fig. 2, fig. 2 is a schematic diagram of a data authority control device according to an embodiment of the invention. The same parts as those of the above embodiment in this embodiment are not described here again. The embodiment of the invention also provides a data authority control device, which comprises:
An obtaining module 210, configured to obtain an SQL statement;
the segmentation module 220 is configured to segment an SQL statement having a defined data authority policy to obtain a plurality of SQL statement segments;
A judging module 230, configured to traverse the SQL statement segments one by one, and judge whether the SQL statement segment started with select is the first element after segmentation;
A loop module 240 for entering the SQL statement segment beginning at select and being the first element back to the partition module 220 for secondary partitioning;
the filtering module 250 is configured to parse a table name of an SQL statement segment that starts with a select and is not the first element, and filter out the SQL statement segment whose table name defines the data authority policy;
A replacing module 260, configured to replace a selected statement whose table name defines an SQL statement segment of the data authority policy, where the table name is a select statement added with an authority condition, to obtain a plurality of modified SQL statement segments;
and the remodelling module 270 is configured to reconnect the modified SQL statement segment to obtain a target SQL statement segment.
The screening module 250 comprises a searching and marking module 251 and a combining module 252, wherein the searching and marking module 251 is used for searching and marking the starting position, the interval position and the cut-off position of the table in the SQL sentence segment, the starting position comprises the position of the from keyword, the cut-off position comprises the last index position of the SQL sentence segment, and the combining module 252 is used for combining the starting position, the interval position and the cut-off position to screen the SQL sentence segment with the table name defining the data authority strategy.
The lookup marking module 251 comprises a starting position lookup module for searching a from keyword by using a regular expression, an interval position lookup module for searching a separator keyword or a separator character by using the regular expression, wherein the separator keyword comprises left, join and/or on, the segmentation character comprises comma, a cut-off position lookup module for searching a where, group by, order by and/or a moving keyword by using the regular expression, and a position marking module for taking the position of the from keyword as the starting position of the table, the separator keyword and/or the separator character as the interval position of the table, and the position of the separator keyword and/or the separator keyword as the cut-off position of the table, and taking the last index of the SQL sentence segment as the cut-off position of the table when any keyword of where, group by, order by and/or moving is not searched.
In an alternative embodiment, an electronic device is provided, as shown in FIG. 3, the electronic device 300 shown in FIG. 3 comprising a processor 310 and a memory 330. Wherein the processor 310 is coupled to the memory 330, such as via a bus 320. Optionally, the electronic device 300 may also include a transceiver 340. It should be noted that, in practical applications, the transceiver 340 is not limited to one, and the structure of the electronic device 300 is not limited to the embodiment of the present application.
The processor 310 may be a CPU, general-purpose processor, DSP, ASIC, FPGA or other programmable logic device, transistor logic device, hardware component, or any combination thereof. Which may implement or perform the various exemplary logic blocks, modules and circuits described in connection with this disclosure. Processor 310 may also be a combination that performs computing functions, e.g., including one or more microprocessor combinations, a combination of a DSP and a microprocessor, etc.
Bus 320 may include a path that communicates information between the components. Bus 320 may be a PCI bus, an EISA bus, or the like. The bus 320 may be classified into an address bus, a data bus, a control bus, and the like. For ease of illustration, only one thick line is shown in fig. 3, but not only one bus or one type of bus.
Memory 330 may be, but is not limited to, a ROM or other type of static storage device that can store static information and instructions, a RAM or other type of dynamic storage device that can store information and instructions, an EEPROM, a CD-ROM or other optical disk storage, optical disk storage (including compact disks, laser disks, optical disks, digital versatile disks, blu-ray disks, etc.), magnetic disk storage media or other magnetic storage devices, or any other medium that can be used to carry or store desired program code in the form of instructions or data structures and that can be accessed by a computer.
Memory 330 is used to store application code for performing the execution of aspects of the present application and is controlled by processor 310. Processor 310 is operative to execute application code stored in memory 330 to implement what is shown in any of the method embodiments described previously.
Among them, the electronic devices include, but are not limited to, mobile terminals such as mobile phones, notebook computers, digital broadcast receivers, PDAs (personal digital assistants), PADs (tablet computers), PMPs (portable multimedia players), car terminals (e.g., car navigation terminals), and the like, and stationary terminals such as digital TVs, desktop computers, and the like.
Yet another embodiment of the present application provides a computer storage medium having a computer program stored thereon, which when run on a computer, causes the computer to perform the corresponding method embodiments described above. Compared with the prior art, the application can realize the authority management of certain data by intercepting the SQL statement generated by Hibernates and modifying the SQL statement so as to realize the authority management of certain data, and realize that the user can only inquire and manage the user information under the department where the user is located. The application can reduce the complexity of data authority control development in WEB application, add the control of the data authority after the development is completed, adjust the data control authority at any time and rarely even do not need to modify the applied code, and can enhance the stability and the robustness of the data authority control. After the data authority control scheme code completes the test, only the defined data strategy is required to be tested and adjusted for the data authority control in the subsequent project.
While the foregoing is directed to the preferred embodiments of the present invention, it will be appreciated by those skilled in the art that changes and modifications may be made without departing from the principles of the invention, such changes and modifications are also intended to be within the scope of the invention.

Claims (5)

1.一种数据权限控制方法，其特征在于，包括：1. A data authority control method, comprising: 获取SQL语句；Get SQL statements; 对有定义数据权限策略的SQL语句进行分割，得到若干SQL语句段；所述对有定义数据权限策略的SQL语句进行分割，得到若干SQL语句段中，分割有定义数据权限策略的所述SQL语句，所使用的正则表达式包括判断子查询开始的正则表达式：(?!\A)\(\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]；The SQL statement with defined data permission policy is segmented to obtain a plurality of SQL statement segments; in the segmentation of the SQL statement with defined data permission policy to obtain a plurality of SQL statement segments, the SQL statement with defined data permission policy is segmented, and the regular expression used includes the regular expression for judging the start of the subquery: (?!\A)\(\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]; 逐个遍历所述SQL语句段，并判断以select开始的SQL语句段是否为分割后的第一个元素；Traverse the SQL statement segments one by one, and determine whether the SQL statement segment starting with select is the first element after segmentation; 若否，对SQL语句段进行二次分割；If not, split the SQL statement segment twice; 若是，解析所述SQL语句段的表名，筛选出表名定义了数据权限策略的SQL语句段；If yes, parse the table name of the SQL statement segment and filter out the SQL statement segment whose table name defines the data permission policy; 替换所述表名定义了数据权限策略的SQL语句段的表名为添加了权限条件的select语句，得到若干更改后的SQL语句段；The table name of the SQL statement segment that defines the data permission policy is replaced with a select statement that adds permission conditions, thereby obtaining several changed SQL statement segments; 将所述更改后的SQL语句段重新连接，得到目标SQL语句段；Reconnect the modified SQL statement segments to obtain the target SQL statement segments; 所述解析所述SQL语句段的表名，筛选出表名定义了数据权限策略的SQL语句段，包括：The parsing of the table name of the SQL statement segment to filter out the SQL statement segment whose table name defines the data permission policy includes: 查找并标记所述SQL语句段中表的开始位置、间隔位置和截止位置；其中，所述开始位置包括from关键字的位置，所述截止位置包括所述SQL语句段的最后索引处；Find and mark the starting position, interval position and end position of the table in the SQL statement segment; wherein the starting position includes the position of the from keyword, and the end position includes the last index of the SQL statement segment; 所述查找并标记所述SQL语句段中表的开始位置、间隔位置和截止位置，包括：The step of searching and marking the start position, the interval position and the end position of the table in the SQL statement segment includes: 利用正则表达式查找from关键字；Use regular expressions to find the from keyword; 利用正则表达式查找分隔符关键字或分隔字符；所述分隔符关键字包括left、join和/或on，所述分隔字符包括逗号；Using a regular expression to search for a separator keyword or a separator character; the separator keyword includes left, join and/or on, and the separator character includes a comma; 利用正则表达式查找where、group by、order by和/或having关键字；Use regular expressions to find where, group by, order by, and/or having keywords; 将from关键字的位置作为表的开始位置，分隔符关键字和/或分隔字符作为表的间隔位置，where、group by、order by和/或having关键字的位置作为表的截止位置；当没有查找到where、group by、order by和having中任一关键字时，将所述SQL语句段的最后索引处作为表的截止位置；The position of the from keyword is used as the starting position of the table, the delimiter keyword and/or the delimiter character is used as the interval position of the table, and the position of the where, group by, order by and/or having keywords is used as the end position of the table; when any of the where, group by, order by and having keywords is not found, the last index of the SQL statement segment is used as the end position of the table; 联合所述开始位置、间隔位置和所述截止位置，筛选出表名定义了数据权限策略的SQL语句段。The start position, the interval position and the end position are combined to filter out the SQL statement segment whose table name defines the data permission policy. 2.根据权利要求1所述的数据权限控制方法，其特征在于，所述获取SQL语句，包括：创建拦截器实现Hibernate提供的EmptyInterceptor接口，重写onPrePareStatement方法拦截生成的SQL语句。2. The data permission control method according to claim 1 is characterized in that obtaining the SQL statement includes: creating an interceptor to implement the EmptyInterceptor interface provided by Hibernate, and overriding the onPrePareStatement method to intercept the generated SQL statement. 3.一种数据权限控制装置，其特征在于，包括：3. A data authority control device, comprising: 获取模块，用于获取SQL语句；Acquisition module, used to obtain SQL statements; 分割模块，用于对有定义数据权限策略的SQL语句进行分割，得到若干SQL语句段；所述对有定义数据权限策略的SQL语句进行分割，得到若干SQL语句段中，分割有定义数据权限策略的所述SQL语句，所使用的正则表达式包括判断子查询开始的正则表达式：(?!\A)\(\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]；A segmentation module is used to segment the SQL statement with defined data permission policy to obtain a plurality of SQL statement segments; the SQL statement with defined data permission policy is segmented to obtain a plurality of SQL statement segments, and the regular expression used to segment the SQL statement with defined data permission policy includes a regular expression for determining the start of a subquery: (?!\A)\(\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]; 判断模块，用于逐个遍历所述SQL语句段，并判断以select开始的SQL语句段是否为分割后的第一个元素；A judgment module, used for traversing the SQL statement segments one by one, and judging whether the SQL statement segment starting with select is the first element after segmentation; 循环模块，用于对以select开始且为第一个元素的SQL语句段进行二次分割；The loop module is used to split the SQL statement segment that starts with select and is the first element twice; 筛选模块，用于解析以select开始且不是第一个元素的SQL语句段的表名，并筛选出表名定义了数据权限策略的SQL语句段；The filtering module is used to parse the table name of the SQL statement segment that starts with select and is not the first element, and filter out the SQL statement segment whose table name defines the data permission policy; 替换模块，用于替换所述表名定义了数据权限策略的SQL语句段的表名为添加了权限条件的select语句，得到若干更改后的SQL语句段；A replacement module is used to replace the table name of the SQL statement segment that defines the data permission policy with a select statement that adds permission conditions, and obtain a number of changed SQL statement segments; 重塑模块，用于将所述更改后的SQL语句段重新连接，得到目标SQL语句段；A reshaping module is used to reconnect the modified SQL statement segments to obtain a target SQL statement segment; 所述筛选模块具体，包括：The screening module specifically includes: 查找标记模块，用于查找并标记所述SQL语句段中表的开始位置、间隔位置和截止位置；其中，所述开始位置包括from关键字的位置，所述截止位置包括所述SQL语句段的最后索引处；A search and marking module, used to search and mark the starting position, interval position and end position of the table in the SQL statement segment; wherein the starting position includes the position of the from keyword, and the end position includes the last index of the SQL statement segment; 所述查找标记模块，包括：The tag search module includes: 开始位置查找模块，用于利用正则表达式查找from关键字；The starting position search module is used to search for the from keyword using regular expressions; 间隔位置查找模块，用于利用正则表达式查找分隔符关键字或分隔字符；所述分隔符关键字包括left、join和/或on，所述分隔字符包括逗号；A separation position search module, used to search for a separator keyword or a separator character using a regular expression; the separator keyword includes left, join and/or on, and the separator character includes a comma; 截止位置查找模块，用于利用正则表达式查找where、group by、order by和/或having关键字；The cut-off position search module is used to search for where, group by, order by and/or having keywords using regular expressions; 位置标记模块，用于将from关键字的位置作为表的开始位置，分隔符关键字和/或分隔字符作为表的间隔位置，where、group by、order by和/或having关键字的位置作为表的截止位置；当没有查找到where、group by、order by和having中任一关键字时，将所述SQL语句段的最后索引处作为表的截止位置；a position marking module, used to use the position of the from keyword as the starting position of the table, the separator keyword and/or the separator character as the interval position of the table, and the position of the where, group by, order by and/or having keywords as the end position of the table; when any of the where, group by, order by and having keywords is not found, the last index of the SQL statement segment is used as the end position of the table; 联合模块，用于联合所述开始位置、间隔位置和所述截止位置，筛选出表名定义了数据权限策略的SQL语句段。The combining module is used to combine the starting position, the interval position and the ending position to filter out the SQL statement segment whose table name defines the data permission policy. 4.一种电子设备，其特征在于，其包括：4. An electronic device, characterized in that it comprises: 处理器、存储器和总线；processor, memory, and bus; 所述总线，用于连接所述处理器和所述存储器；The bus is used to connect the processor and the memory; 所述存储器，用于存储操作指令；The memory is used to store operation instructions; 所述处理器，用于通过调用所述操作指令，执行上述权利要求1-2中任一项所述的数据权限控制方法。The processor is used to execute the data authority control method described in any one of claims 1 to 2 by calling the operation instruction. 5.一种计算机存储介质，其特征在于，所述计算机存储介质用于存储计算机指令，当其在计算机上运行时，使得计算机可以执行上述权利要求1-2中任一项所述的数据权限控制方法。5. A computer storage medium, characterized in that the computer storage medium is used to store computer instructions, and when the computer storage medium is run on a computer, the computer can execute the data authority control method described in any one of claims 1 to 2.
CN202011318043.6A 2020-11-20 2020-11-20 Data authority control method and device Active CN112463792B (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202011318043.6A CN112463792B (en) 2020-11-20 2020-11-20 Data authority control method and device

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202011318043.6A CN112463792B (en) 2020-11-20 2020-11-20 Data authority control method and device

Publications (2)

Publication Number Publication Date
CN112463792A CN112463792A (en) 2021-03-09
CN112463792B true CN112463792B (en) 2024-11-29

Family

ID=74800050

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202011318043.6A Active CN112463792B (en) 2020-11-20 2020-11-20 Data authority control method and device

Country Status (1)

Country Link
CN (1) CN112463792B (en)

Families Citing this family (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN114138849A (en) * 2021-11-30 2022-03-04 平安科技（深圳）有限公司 Multi-tenant data permission control method, device, computer and readable storage medium
CN116578583B (en) * 2023-07-12 2023-10-03 太平金融科技服务（上海）有限公司 Abnormal statement identification method, device, equipment and storage medium
CN116796306B (en) * 2023-08-15 2023-11-14 浩鲸云计算科技股份有限公司 Method for controlling authority of notebook table under same tenant

Citations (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN104484621A (en) * 2014-12-31 2015-04-01 中博信息技术研究院有限公司 Data authority control method based on SQL (Structured Query Language)

Family Cites Families (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US8185518B2 (en) * 2004-11-12 2012-05-22 International Business Machines Corporation Method, system and program product for rewriting structured query language (SQL) statements
CN109815284B (en) * 2019-01-04 2024-08-16 平安科技（深圳）有限公司 Data processing method and device

Patent Citations (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN104484621A (en) * 2014-12-31 2015-04-01 中博信息技术研究院有限公司 Data authority control method based on SQL (Structured Query Language)

Also Published As

Publication number Publication date
CN112463792A (en) 2021-03-09

Similar Documents

Publication Publication Date Title
CN112463792B (en) Data authority control method and device
US8601474B2 (en) Resuming execution of an execution plan in a virtual machine
US20190155794A1 (en) Access control for nested data fields
US11334474B2 (en) Fast change impact analysis tool for large-scale software systems
US8108367B2 (en) Constraints with hidden rows in a database
US9298829B2 (en) Performing a function on rows of data determined from transitive relationships between columns
US10459889B2 (en) Multi-user database execution plan caching
US11687546B2 (en) Executing conditions with negation operators in analytical databases
US8924373B2 (en) Query plans with parameter markers in place of object identifiers
CN108563694B (en) SQL execution method and device for logic deletion, computer equipment and storage medium
US11176133B2 (en) Filter evaluation for table fragments
US7533136B2 (en) Efficient implementation of multiple work areas in a file system like repository that supports file versioning
US9275155B1 (en) Querying across a composite join of multiple database tables using a search engine index
US20090070300A1 (en) Method for Processing Data Queries
KR102368775B1 (en) Method, apparatus, device and storage medium for managing index
CN107145549B (en) Database cache control method and system
CN114443699B (en) Information query method, device, computer equipment and computer readable storage medium
WO2024113740A1 (en) Data query method, and system, device and computer-readable storage medium
US20070061294A1 (en) Source code file search
US8396858B2 (en) Adding entries to an index based on use of the index
US10521430B1 (en) Dynamic selection of query execution operators
CN114861229A (en) Hive dynamic desensitization method and system
CN119201885A (en) Metadata migration method and device between databases, storage medium, and equipment
US20250021554A1 (en) Patterned query statements with hints
CN111125216A (en) Method and device for importing data into Phoenix

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
GR01 Patent grant
GR01 Patent grant
CP03 Change of name, title or address

Address after: 510000 No. 502, 504, 506, 508, 510, 512, 516, 518, North District, 5 / F, No. 9, Jiangong Road, phase I Industrial Park, Tianhe Science and Technology Park, Tianhe District, Guangzhou City, Guangdong Province

Patentee after: Guangdong Jingyi Information Technology Co.,Ltd.

Country or region after: China

Address before: 510000 No. 502, 504, 506, 508, 510, 512, 516, 518, North District, 5 / F, No. 9, Jiangong Road, phase I Industrial Park, Tianhe Science and Technology Park, Tianhe District, Guangzhou City, Guangdong Province

Patentee before: AEROSPACE JINGYI (GUANGDONG) INFORMATION TECHNOLOGY Co.,Ltd.

Country or region before: China

CP03 Change of name, title or address