CN112463792A - Data authority control method and device - Google Patents
Data authority control method and device Download PDFInfo
- Publication number
- CN112463792A CN112463792A CN202011318043.6A CN202011318043A CN112463792A CN 112463792 A CN112463792 A CN 112463792A CN 202011318043 A CN202011318043 A CN 202011318043A CN 112463792 A CN112463792 A CN 112463792A
- Authority
- CN
- China
- Prior art keywords
- sql
- segment
- keyword
- module
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 37
- 230000011218 segmentation Effects 0.000 claims abstract description 25
- 238000012216 screening Methods 0.000 claims abstract description 16
- 230000014509 gene expression Effects 0.000 claims description 76
- 238000001914 filtration Methods 0.000 claims description 2
- 239000012634 fragment Substances 0.000 claims 2
- 238000011217 control strategy Methods 0.000 description 5
- 238000011161 development Methods 0.000 description 4
- 230000018109 developmental process Effects 0.000 description 4
- 238000012360 testing method Methods 0.000 description 4
- 238000010586 diagram Methods 0.000 description 3
- 230000003287 optical effect Effects 0.000 description 3
- 235000019800 disodium phosphate Nutrition 0.000 description 2
- 230000006870 function Effects 0.000 description 2
- 238000013507 mapping Methods 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000000926 separation method Methods 0.000 description 2
- 230000003068 static effect Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000004590 computer program Methods 0.000 description 1
- 238000000802 evaporation-induced self-assembly Methods 0.000 description 1
- 230000009545 invasion Effects 0.000 description 1
- 238000005192 partition Methods 0.000 description 1
- 230000002688 persistence Effects 0.000 description 1
- 238000000638 solvent extraction Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- 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; CALCULATING OR 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; the method comprises the steps of dividing SQL sentences with defined data permission strategies to obtain a plurality of SQL sentence segments; traversing the SQL sentence segments one by one, and judging whether the SQL sentence segment starting from select is the first element after segmentation; if not, the SQL sentence segment is segmented again; if yes, analyzing the table name of the SQL sentence segment, and screening out the SQL sentence segment of which the table name defines the data authority strategy; replacing the table name of the SQL statement segment of which the table name defines the data authority strategy as a select statement added with the authority condition to obtain a plurality of modified SQL statement segments; and re-connecting the changed SQL sentence segments to obtain the target SQL sentence segment. The invention realizes the authority management of certain data by intercepting and modifying the SQL sentences generated by Hibernates, and realizes that a user can only inquire and manage the user information under the department of the user.
Description
Technical Field
The invention relates to the technical field of electronic information, in particular to a data authority control method and device.
Background
Data right control is always an important ring in a middle background management system, and a reasonable right management strategy can bring great convenience to the system. Most developers today choose to create rights management modules Based on a Role-Based Access Control (RBAC) rights model. The RBAC is an effective access control mode for implementing an enterprise-oriented security policy, and the basic idea is that various permissions for system operation are not directly granted to specific users, but a role set is established between a user set and a permission set. Each role corresponds to a set of corresponding permissions. Once a user is assigned the appropriate role, the user has all the operational rights for that role. The method has the advantages that the operation of distributing the authority is not needed to be carried out every time the user is created, only the corresponding role of the user is needed to be distributed, and the authority change of the role is much less than that of the user, so that the authority 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 (such as rights of functions of adding and deleting changes) saved in the database, and cannot manage the rights of certain types of data. For example: if a user has the right to manage the menu, the user can inquire the information of all users and modify the information at will, 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 can accurately control data rows according to a configurable data strategy, intercept SQL sentences generated by Hibernate and modify the SQL sentences according to actual configuration so as to achieve the purpose of data authority control.
In order to achieve the above object, an embodiment of the present invention provides a data authority control method, including: acquiring an SQL statement; the method comprises the steps of dividing SQL sentences with defined data permission strategies to obtain a plurality of SQL sentence segments; traversing the SQL sentence segments one by one, and judging whether the SQL sentence segment starting with select is the first element after segmentation; if not, carrying out secondary segmentation on the SQL sentence segment; if yes, analyzing the table name of the SQL sentence segment, and screening out the SQL sentence segment of which the table name defines the data authority strategy; replacing the table name of the SQL statement segment of which the table name defines the data authority strategy with a select statement added with the authority condition to obtain a plurality of modified SQL statement segments; and reconnecting the changed SQL sentence segments to obtain a target SQL sentence segment.
Specifically, the analyzing the table name of the SQL statement segment, and screening out the SQL statement segment whose table name defines the data authority policy includes: searching and marking the starting position, the interval position and the ending position of the table in the SQL sentence period; wherein the start position comprises a position of a from keyword, and the end position comprises a last index of the SQL statement segment; and screening out the SQL sentence segments with the table names defining the data authority strategies by combining the starting position, the interval position and the stopping position.
Specifically, the searching and marking the start position, the interval position and the stop position of the table in the SQL statement segment includes: searching from keywords by using a regular expression; searching for a separator keyword or a separator character by using a regular expression; the separator keywords comprise left, join and/or on, and the segmentation characters comprise commas; finding a where keyword, a group by keyword, an order by keyword and/or a having keyword by using a regular expression; taking the position of the from keyword as the starting position of the table, taking the separator keyword and/or the separator character as the interval position of the table, and taking the position of the where, group by, order by and/or happy keyword as the cut-off position of the table; and when any keyword in the where, the group by, the order by and the having is not found, taking the last index of the SQL statement segment as the cut-off position of the table.
Specifically, the acquiring the SQL statement includes: and the creation interceptor realizes an emptyInterreceptor interface provided by Hibernate and rewrites the SQL statement intercepted and generated by the onPrePareStatement method.
Specifically, the SQL statement with the defined data permission policy is segmented to obtain a plurality of SQL statement segments, where the SQL statement with the defined data permission policy is segmented, and the regular expression used includes a regular expression that judges the start of a sub-query: (.
An embodiment of the present invention further provides a data right control apparatus, including: the acquisition module is used for acquiring SQL sentences; the segmentation module is used for segmenting the SQL sentences with the defined data permission strategies to obtain a plurality of SQL sentence segments; the judging module is used for traversing the SQL sentence segments one by one and judging whether the SQL sentence segment starting from select is the first element after segmentation; the loop module is used for enabling the SQL statement segment which starts with select and is the first element to enter a loop for secondary segmentation; the screening module is used for analyzing the table name of the SQL sentence segment which begins with select and is not the first element, and screening the SQL sentence segment of which the table name defines the data authority strategy; the replacing module is used for replacing the table name of the SQL sentence segment of which the table name defines the data authority policy as a select sentence added with the authority condition to obtain a plurality of modified SQL sentence segments; and the remolding module is used for reconnecting the changed SQL sentence segments to obtain the target SQL sentence segment.
Specifically, the screening module includes: the searching and marking module is used for searching and marking the starting position, the interval position and the ending position of the table in the SQL sentence period; wherein the starting position comprises a from key position, and the ending position comprises a last index of the SQL statement segment; and the combination module is used for combining the starting position, the interval position and the ending position to screen out the SQL sentence segments with the table names defining the data authority strategies.
Specifically, the search tagging module includes: the starting position searching module is used for searching from keywords by using the regular expression; the interval position searching module is used for searching for the key characters or the separation characters of the separation characters by using a regular expression; the separator keywords comprise left, join and/or on, and the segmentation characters comprise commas; the cutoff position searching module is used for searching the where, group by, order by and/or changing keywords by using the regular expression; the position marking module is used for taking the position of the from keyword as the starting position of the table, taking the separator keyword and/or the separator character as the interval position of the table, and taking the position of the where, group by, order by and/or having keyword as the cut-off position of the table; and when any keyword in the where, the group by, the order by and the having is not found, taking the last index of the SQL statement segment as the cut-off position of the table.
The embodiment of the invention also provides electronic equipment, which comprises a processor, a memory and a bus; 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 instruction.
The embodiment of the invention also provides a computer storage medium, which is used for storing computer instructions, and when the computer storage medium runs on a computer, the computer can execute the data authority control method.
The embodiment of the invention has the following beneficial effects:
the invention provides a data authority control method and a device, wherein the method comprises the following steps: the method comprises the steps of obtaining SQL sentences; the method comprises the steps of dividing SQL sentences with defined data permission strategies to obtain a plurality of SQL sentence segments; traversing the SQL sentence segments one by one, and judging whether the SQL sentence segment starting from select is the first element after segmentation; if not, adding a data authority strategy for the SQL sentence segment, and segmenting again; if yes, analyzing the table name of the SQL sentence segment, and screening out the SQL sentence segment of which the table name defines the data authority strategy; replacing the table name of the SQL statement segment of which the table name defines the data authority strategy as a select statement added with the authority condition to obtain a plurality of modified SQL statement segments; and reconnecting the changed SQL sentence segments to obtain the target SQL sentence segment.
Compared with the prior art, the method and the device have the advantages that the SQL sentences generated by Hibernates are intercepted and modified to achieve data authority management, authority management is carried out on certain data, and users can only inquire and manage user information under the department where the users are located. The code intrusion degree of the authority control strategy is low, the adding and removing modes of the control strategy are simple, and the original code does not need to be modified; the invention can reduce the complexity of data authority control development in WEB application, and adds the control of data authority after the development is finished, and the data control authority is adjusted at any time with little or even no need of modifying the applied code; the invention can enhance the stability and robustness of data authority control. After the data authority control scheme code completes the test, the data authority control in the subsequent project only needs to test and adjust the defined data strategy.
Drawings
In order to more clearly illustrate the technical solution of the present invention, the drawings needed to be used 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 it is obvious for those skilled in the art that other drawings can be obtained according to the drawings without creative efforts.
Fig. 1 is a schematic flow chart of a data right control method according to an embodiment of the present invention;
fig. 2 is a schematic structural diagram of a data authority control apparatus 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 technical solutions in the embodiments of the present invention will be described clearly and completely with reference to the accompanying drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
It should be understood that the step numbers used herein are for convenience of description only and are not intended as limitations on the order in which the steps may be performed.
It is to be understood that the terminology used in the description of the invention herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention. As used in the specification of the present invention 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 the described 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 and includes any and all possible combinations of one or more of the associated listed items.
In order to facilitate understanding of the embodiments of the present invention by those skilled in the art, the following description will discuss relevant art of the embodiments of the present invention.
The data authority policy indicates what kind of restriction condition is added to the subject (table or view), for example, when the user table is queried, the department where the department ID is added as the current login user is a data authority policy.
Hibernate is an object relation mapping framework of open source code, which encapsulates JDBC with very light weight objects, establishes a mapping relation between POJO and a database table, is a fully automatic orm framework, can automatically generate SQL statements and automatically execute, so that Java programmers can use object programming thinking to manipulate the database at will. Hibernate can be applied to any occasions using JDBC, can be used in Java client programs and can also be used in Servlet/JSP Web applications, and has the revolutionary significance that Hibernate can replace CMP in a JaveEE architecture applying EJB to complete the important role of data persistence.
Regular expressions (Regular expressions, also called Regular expressions, often abbreviated in code as regex, regexp or RE) are often used to retrieve and replace text that conforms to a certain pattern (rule). The regular expression is a logic formula for operating on character strings, and is a regular character string formed by a plurality of specific characters defined in advance and a combination of the specific characters, and the regular character string is used for expressing a filtering logic for the character string.
The following lists the regular strings used in parsing an SQL statement:
1. regular expression in brackets: [ \\\\\\\\\\\\\\/f)
2. Regular expression of table name: [ a-zA-Z _, 0-9] +
3. Judging whether the SQL statement starts with SELECT or not:
\A(\s*|\()\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]
4. judging the regular expression of the start of the sub-query:
(?!\A)\(\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]
5. regular expression matching FROM keywords:
(\s+|[*])[fF][rR][oO][mM](\s+|(?=\())
6. regular expressions that match UPDATE keys:
\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 that match DELETE FROM keywords:
[dD][eE][lL][eE][tT][eE]\s+[fF][rR][oO][mM]\s+(?!(\s|[(]))
9. regular expressions matching WHERE keywords:
(\)|\s)[wW][hH][eE][rR][eE](\s+|(?=\())
10. regular expressions matching HAVING keys:
(\)|\s)[hH][aA][vV][iI][nN][gG](\s+|(?=\())
11. regular expressions that match ORDERBY keywords:
(\)|\s)[oO][rR][dD][eE][rR]\s+[bB][yY](\s+|(?=\())
12. the regular expression matching the GROUP BY keyword:
(\)|\s)[gG][rR][oO][uU][pP]\s+[bB][yY](\s+|(?=\())
13. regular expressions that match FOR UPDATE keys:
(\)|\s+)[fF][oO][rR]\s+[uU][pP][dD][aA][tT][eE](\s+|(?=\())
14. regular expressions matching UNION keywords:
(\)|\s+)[uU][nN][iI][oO][nN](\s+|(?=\())
15. regular expressions that match 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 LEFT JOIN keywords:
(\)|\s+)[lL][eE][fF][tT]\s+[jJ][oO][iI][nN](\s+|(?=\())
21. regular expressions matching the RIGHT JOIN key:
(\)|\s+)[rR][iI][gG][hH][tT]\s+[jJ][oO][iI][nN](\s+|(?=\())
22. regular expressions that match FULL JOIN keys:
(\)|\s+)[fF][uU][lL][lL]\s+[jJ][oO][iI][nN](\s+|(?=\())
23. regular expressions that match LEFT OUTER JOIN keywords:
(\)|\s+)[lL][eE][fF][tT]\s+[oO][uU][tT][eE][rR]\s+[jJ][oO][iI][nN](\s+|(?=\())
24. regular expressions that match the RIGHT OUTER JOIN key:
(\)|\s+)[rR][iI][gG][hH][tT]\s+[oO][uU][tT][eE][rR]\s+[jJ][oO][iI][nN](\s+|(? =\())
25. regular expressions that match FULL OUTER JOIN keys:
(\)|\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+|(?=\())
hereinafter, the data right control method provided by the embodiment of the present invention will be described and explained in detail through several specific embodiments.
Referring to fig. 1, fig. 1 is a schematic flow chart illustrating a data permission control method according to an embodiment of the present invention. In order to achieve the above object, an embodiment of the present invention provides a data authority control method, including:
step S110, SQL sentences are obtained.
The means for acquiring the SQL statement comprises the following steps: and the creation interceptor realizes an emptyInterreceptor interface provided by Hibernate and rewrites the SQL statement intercepted and generated by the onPrePareStatement method.
Step S120, the SQL statement with the defined data permission policy is segmented to obtain a plurality of SQL statement segments.
In this embodiment, a regular expression is used to analyze an SQL statement and segment the SQL statement with a defined data permission policy, where the used regular expression includes a regular expression that judges the start of a sub-query:
(?!\A)\(\s*[sS][eE][lL][eE][cC][tT][\s|?=\(|*]。
for example, an SQL statement is "select name, job, sal from em where job name ═ SCOTT ') and sal > (select sal from em where name ═ JAMES')", and is segmented into five segments after being analyzed by regular expressions: the first segment "select name, jobi, sal from em where jobi ("; the second segment "select jobi from em where jobi" the third segment ") and sal > ("; the fourth segment "select sal from em where jobi" the fifth segment ")". Wherein the first segment is the first element after segmentation.
Step S130, traverse the SQL statement segments one by one, and determine whether the SQL statement segment starting with select is the first element after segmentation.
If the SQL statement section starts with select but is not the first element after segmentation, the procedure returns to step S120, and if the SQL statement section starts with select and is the first element after segmentation, the procedure goes to step S140.
The first element after the segmentation represents that the regular expression is used for analysis and then is segmented to obtain a first section statement in a plurality of sections, and the judgment is carried out because the SQL statement section started by the second select possibly has a sub-query.
Step S140, analyzing the table name of the SQL statement segment, and screening out the SQL statement segment of which the table name defines the data authority strategy.
Firstly, searching and marking the starting position, the interval position and the ending position of a table in the SQL sentence segment by judging whether the table name of the SQL sentence segment defines a data authority strategy or not; wherein, the start position comprises the position of the from keyword, and the end position comprises the last index of the SQL statement segment; and combining the starting position, the interval position and the ending position to screen out the SQL sentence segments with the table names defining the data authority strategies.
And S150, replacing the table name of the SQL statement segment with the table name defining the data authority strategy to a select statement added with the authority condition to obtain a plurality of modified SQL statement segments. The where clause of this select statement is the data permission policy that is well defined for the current table.
Step S160, re-connecting the modified SQL statement segments to obtain the target SQL statement segment. And finally, the target SQL statement section is also sent to Hibernate as a data authority control strategy based on Hibernate.
Wherein, searching and marking the starting position, the interval position and the ending position of the table in the SQL sentence segment comprises the following steps: searching from keywords by using a regular expression; searching for separator keywords or separator characters by using a regular expression; separator keys include left, join, and/or on, and split characters include comma (,); finding a where keyword, a group by keyword, an order by keyword and/or a having keyword by using a regular expression; taking the position of the from keyword as the starting position of the table, taking the separator keyword and/or the separator character as the interval position of the table, and taking the position of the where, group by, order by and/or happy keyword as the cut-off position of the table; and when any keyword in the where, group by, order by and changing is not found, taking the last index of the SQL statement segment as the cut-off position of the table.
The regular expression used to find the from keyword is:
(\s+|[*])[fF][rR][oO][mM](\s+|(?=\())。
the purpose of searching for keywords such as where, group by, order by, and having is to find the cutoff positions of tables that may exist, the regular expressions used include:
regular expressions matching WHERE keywords:
(\)|\s)[wW][hH][eE][rR][eE](\s+|(?=\())
regular expressions matching HAVING keys:
(\)|\s)[hH][aA][vV][iI][nN][gG](\s+|(?=\())
regular expressions that match ORDERBY keywords:
(\)|\s)[oO][rR][dD][eE][rR]\s+[bB][yY](\s+|(?=\())
the regular expression matching the GROUP BY keyword:
(\) | \ s) [ gG ] [ rR ] [ oO ] [ uU ] [ pP ] \ s + [ bB ] [ yY ] (\ s + (.
After the from keyword is found, other regular expressions need to be searched instead, including:
regular expressions matching UNION keywords:
(\)|\s+)[uU][nN][iI][oO][nN](\s+|(?=\())
regular expressions that match 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 LEFT JOIN keywords:
(\)|\s+)[lL][eE][fF][tT]\s+[jJ][oO][iI][nN](\s+|(?=\())
regular expressions matching the RIGHT JOIN key:
(\)|\s+)[rR][iI][gG][hH][tT]\s+[jJ][oO][iI][nN](\s+|(?=\())
regular expressions that match FULL JOIN keys:
(\)|\s+)[fF][uU][lL][lL]\s+[jJ][oO][iI][nN](\s+|(?=\())
regular expression of LEFT OUTER JOIN key:
(\)|\s+)[lL][eE][fF][tT]\s+[oO][uU][tT][eE][rR]\s+[jJ][oO][iI][nN](\s+|(?=\())
regular expression of the 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 structural diagram of a data permission control device according to an embodiment of the present invention. The same portions of this embodiment as those of the above embodiments will not be described herein again. An embodiment of the present invention further provides a data right control apparatus, including:
an obtaining module 210, configured to obtain an SQL statement;
the partitioning module 220 is configured to partition the SQL statement with the defined data permission policy to obtain a plurality of SQL statement segments;
the judging module 230 is configured to traverse the SQL statement segments one by one, and judge whether the SQL statement segment starting with select is the first element after segmentation;
a loop module 240, configured to make the SQL statement segment that starts with select and is the first element enter back to the segmentation module 220 for secondary segmentation;
a screening module 250, configured to parse the table name of the SQL statement segment that starts with select and is not the first element, and screen out the SQL statement segment whose table name defines the data permission policy;
a replacing module 260, configured to replace the table name of the SQL statement segment whose table name defines the data permission policy with a select statement to which a permission condition is added, to obtain a plurality of modified SQL statement segments;
and the remolding module 270 is configured to reconnect the modified SQL statement segment to obtain a target SQL statement segment.
The screening module 250 comprises a search marking module 251 and a combination module 252, and the search marking module 251 is used for searching and marking the starting position, the interval position and the ending position of the table in the SQL sentence segment; wherein the start position comprises a position of a from keyword, and the end position comprises a last index of the SQL statement segment; the combination module 252 is configured to combine the start position, the interval position, and the stop position to screen out an SQL statement segment whose table name defines a data permission policy.
The look-up mark module 251 includes: the starting position searching module is used for searching from keywords by using a regular expression; the interval position searching module is used for searching the separator keywords or the separator characters by using the regular expression; the separator keywords comprise left, join and/or on, and the segmentation characters comprise commas; the cutoff position searching module is used for searching the where, group by, order by and/or changing keywords by using the regular expression; the position marking module is used for taking the position of the from keyword as the starting position of the table, taking the separator keyword and/or the separator character as the interval position of the table, and taking the position of the where, group by, order by and/or having keyword as the cut-off position of the table; and when any keyword in the where, the group by, the order by and the having is not found, taking the last index of the SQL statement segment as the cut-off position of the table.
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 further comprise a transceiver 340. It should be noted that the transceiver 340 is not limited to one in practical applications, 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 illustrative logical blocks, modules, and circuits described in connection with the disclosure. The processor 310 may also be a combination of computing functions, e.g., comprising one or more microprocessors, DSPs, and microprocessors, among others.
Memory 330 may be, but is not limited to, ROM or other type of static storage device that can store static information and instructions, RAM or other type of dynamic storage device that can store information and instructions, EEPROM, CD-ROM or other optical disk storage, optical disk storage (including compact disk, laser disk, optical disk, digital versatile disk, blu-ray disk, 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.
The memory 330 is used for storing application program codes for executing the scheme of the present application, and is controlled by the processor 310 to execute. Processor 310 is configured to execute application program code stored in memory 330 to implement any of the method embodiments described above.
Among them, 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), in-vehicle terminals (e.g., car navigation terminals), and the like, and fixed 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, enables the computer to perform the corresponding content of the aforementioned method embodiments. Compared with the prior art, the method and the system have the advantages that the SQL sentences generated by Hibernates are intercepted and modified to achieve data authority management, authority management is carried out on certain data, and users can only inquire and manage user information under the department where the users are located. The code invasion degree of the authority control strategy is low, the adding and removing modes of the control strategy are simple, and the original code does not need to be modified; the invention can reduce the complexity of data authority control development in WEB application, adds the control of data authority after the development is finished, and adjusts the data control authority at any time with little or even no need of modifying the applied code; the invention can enhance the stability and robustness of data authority control. After the data authority control scheme code completes the test, the data authority control in the subsequent project only needs to test and adjust the defined data strategy.
The foregoing is directed to the preferred embodiment of the present invention, and it is understood that various changes and modifications may be made by one skilled in the art without departing from the spirit of the invention, and it is intended that such changes and modifications be considered as within the scope of the invention.
Claims (10)
1. A method for controlling data permissions, comprising:
acquiring an SQL statement;
the method comprises the steps of dividing SQL sentences with defined data permission strategies to obtain a plurality of SQL sentence segments;
traversing the SQL sentence segments one by one, and judging whether the SQL sentence segment starting with select is the first element after segmentation;
if not, carrying out secondary segmentation on the SQL sentence segments;
if yes, analyzing the table name of the SQL sentence segment, and screening out the SQL sentence segment of which the table name defines the data authority strategy;
replacing the table name of the SQL statement segment of which the table name defines the data authority strategy with a select statement added with the authority condition to obtain a plurality of modified SQL statement segments;
and reconnecting the changed SQL sentence segments to obtain a target SQL sentence segment.
2. The method of claim 1, wherein the parsing the table name of the SQL statement segment and screening out the SQL statement segment whose table name defines the data permission policy comprises:
searching and marking the starting position, the interval position and the ending position of the table in the SQL sentence fragment; wherein the starting position comprises a position of a from keyword, and the ending position comprises a last index of the SQL statement segment;
and screening out the SQL sentence segments with the table names defining the data authority strategies by combining the starting position, the interval position and the ending position.
3. The method of claim 2, wherein the searching and marking the start position, the interval position and the end position of the table in the SQL statement segment comprises:
searching from keywords by using a regular expression;
searching for a separator keyword or a separator character by using a regular expression; the separator keywords comprise left, join and/or on, and the segmentation characters comprise commas;
finding a where keyword, a group by keyword, an order by keyword and/or a having keyword by using a regular expression;
taking the position of the from keyword as the starting position of the table, taking the separator keyword and/or the separator character as the interval position of the table, and taking the position of the where, group by, order by and/or happy keyword as the cut-off position of the table; and when any keyword in the where, the group by, the order by and the having is not found, taking the last index of the SQL statement segment as the cut-off position of the table.
4. The data permission control method according to claim 1, wherein the acquiring the SQL statement comprises: and the creation interceptor realizes an emptyInterreceptor interface provided by Hibernate and rewrites the SQL statement intercepted and generated by the onPrePareStatement method.
5. The data authority control method according to claim 1, wherein the SQL statement with the defined data authority policy is segmented to obtain a plurality of SQL statement segments in which the SQL statement with the defined data authority policy is segmented, and the regular expression used includes a regular expression for judging the start of the sub-query: (.
6. A data right control apparatus, comprising:
the acquisition module is used for acquiring SQL sentences;
the segmentation module is used for segmenting the SQL sentences with the defined data permission strategies to obtain a plurality of SQL sentence segments;
the judging module is used for traversing the SQL sentence segments one by one and judging whether the SQL sentence segment starting from select is the first element after segmentation;
the loop module is used for carrying out secondary segmentation on the SQL statement segment which starts from select and is the first element;
the screening module is used for analyzing the table name of the SQL sentence segment which begins with select and is not the first element, and screening the SQL sentence segment of which the table name defines the data authority strategy;
the replacing module is used for replacing the table name of the SQL statement section of which the table name defines the data authority strategy as a select statement added with the authority condition to obtain a plurality of modified SQL statement sections;
and the remolding module is used for reconnecting the changed SQL sentence segments to obtain the target SQL sentence segment.
7. The data permission control device according to claim 6, wherein the filtering module specifically includes:
the searching and marking module is used for searching and marking the starting position, the interval position and the ending position of the table in the SQL sentence fragment; wherein the starting position comprises a position of a from keyword, and the ending position comprises a last index of the SQL statement segment;
and the combination module is used for combining the starting position, the interval position and the ending position to screen out the SQL sentence segment of which the table name defines the data authority strategy.
8. The apparatus of claim 7, wherein the lookup table module comprises:
the starting position searching module is used for searching from keywords by using a regular expression;
the interval position searching module is used for searching the separator keywords or the separator characters by using the regular expression; the separator keywords comprise left, join and/or on, and the segmentation characters comprise commas;
the cutoff position searching module is used for searching the where, group by, order by and/or changing keywords by using the regular expression;
the position marking module is used for taking the position of the from keyword as the starting position of the table, taking the separator keyword and/or the separator character as the interval position of the table, and taking the position of the where, group by, order by and/or having keyword as the cut-off position of the table; and when any keyword in the where, the group by, the order by and the having is not found, taking the last index of the SQL statement segment as the cut-off position of the table.
9. An electronic device, comprising:
a processor, a memory, and a bus;
the bus is used for connecting the processor and the memory;
the memory is used for storing operation instructions;
the processor is used for executing the data authority control method of any one of the claims 1-5 by calling the operation instruction.
10. A computer storage medium for storing computer instructions which, when run on a computer, cause the computer to perform the data right control method of any of claims 1-5.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202011318043.6A CN112463792A (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 CN112463792A (en) | 2020-11-20 | 2020-11-20 | Data authority control method and device |
Publications (1)
Publication Number | Publication Date |
---|---|
CN112463792A true CN112463792A (en) | 2021-03-09 |
Family
ID=74800050
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CN202011318043.6A Pending CN112463792A (en) | 2020-11-20 | 2020-11-20 | Data authority control method and device |
Country Status (1)
Country | Link |
---|---|
CN (1) | CN112463792A (en) |
Cited By (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN116578583A (en) * | 2023-07-12 | 2023-08-11 | 太平金融科技服务(上海)有限公司 | Abnormal statement identification method, device, equipment and storage medium |
CN116796306A (en) * | 2023-08-15 | 2023-09-22 | 浩鲸云计算科技股份有限公司 | Method for controlling authority of notebook table under same tenant |
Citations (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20120185461A1 (en) * | 2004-11-12 | 2012-07-19 | International Business Machines Corporation | Method, system and program product for rewriting structured query language (sql) statements |
CN104484621A (en) * | 2014-12-31 | 2015-04-01 | 中博信息技术研究院有限公司 | Data authority control method based on SQL (Structured Query Language) |
CN109815284A (en) * | 2019-01-04 | 2019-05-28 | 平安科技(深圳)有限公司 | A kind of method and apparatus of data processing |
-
2020
- 2020-11-20 CN CN202011318043.6A patent/CN112463792A/en active Pending
Patent Citations (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20120185461A1 (en) * | 2004-11-12 | 2012-07-19 | International Business Machines Corporation | Method, system and program product for rewriting structured query language (sql) statements |
CN104484621A (en) * | 2014-12-31 | 2015-04-01 | 中博信息技术研究院有限公司 | Data authority control method based on SQL (Structured Query Language) |
CN109815284A (en) * | 2019-01-04 | 2019-05-28 | 平安科技(深圳)有限公司 | A kind of method and apparatus of data processing |
Cited By (4)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN116578583A (en) * | 2023-07-12 | 2023-08-11 | 太平金融科技服务(上海)有限公司 | Abnormal statement identification method, device, equipment and storage medium |
CN116578583B (en) * | 2023-07-12 | 2023-10-03 | 太平金融科技服务(上海)有限公司 | Abnormal statement identification method, device, equipment and storage medium |
CN116796306A (en) * | 2023-08-15 | 2023-09-22 | 浩鲸云计算科技股份有限公司 | Method for controlling authority of notebook table under same tenant |
CN116796306B (en) * | 2023-08-15 | 2023-11-14 | 浩鲸云计算科技股份有限公司 | Method for controlling authority of notebook table under same tenant |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US8601474B2 (en) | Resuming execution of an execution plan in a virtual machine | |
US7533136B2 (en) | Efficient implementation of multiple work areas in a file system like repository that supports file versioning | |
US10459889B2 (en) | Multi-user database execution plan caching | |
US8577891B2 (en) | Methods for indexing and searching based on language locale | |
US11687546B2 (en) | Executing conditions with negation operators in analytical databases | |
US20070061294A1 (en) | Source code file search | |
US9411526B1 (en) | Filesystem management | |
US8442971B2 (en) | Execution plans with different driver sources in multiple threads | |
CN108563694B (en) | SQL execution method and device for logic deletion, computer equipment and storage medium | |
KR102368775B1 (en) | Method, apparatus, device and storage medium for managing index | |
US11477179B2 (en) | Searching content associated with multiple applications | |
CN112463792A (en) | Data authority control method and device | |
CN113051268A (en) | Data query method, data query device, electronic equipment and storage medium | |
CN110334545B (en) | SQL-based permission control method and device and electronic equipment | |
CN107145549B (en) | Database cache control method and system | |
CN113010476A (en) | Metadata searching method, device and equipment and computer readable storage medium | |
US11080332B1 (en) | Flexible indexing for graph databases | |
CN111125216B (en) | Method and device for importing data into Phoenix | |
CN112416966B (en) | Impromptu query method, impromptu query device, computer device and storage medium | |
KR102202792B1 (en) | Method and device for performing multi-caching on data sources of same or different types by using cluster-based processing system | |
US9020995B2 (en) | Hybrid relational, directory, and content query facility | |
CN114443699A (en) | Information query method and device, computer equipment and computer readable storage medium | |
CN112650713A (en) | File system operation method, device, equipment and storage medium | |
CN116450609B (en) | Method and equipment for modeling on heterogeneous data source based on unified grammar | |
US20230046298A1 (en) | Search query refinement using generated keyword triggers |
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 |