CN112416966B - Impromptu query method, impromptu query device, computer device and storage medium - Google Patents
Impromptu query method, impromptu query device, computer device and storage medium Download PDFInfo
- Publication number
- CN112416966B CN112416966B CN202011439737.5A CN202011439737A CN112416966B CN 112416966 B CN112416966 B CN 112416966B CN 202011439737 A CN202011439737 A CN 202011439737A CN 112416966 B CN112416966 B CN 112416966B
- Authority
- CN
- China
- Prior art keywords
- user
- authority
- view
- information
- query
- 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
Links
- 238000000034 method Methods 0.000 title claims abstract description 42
- 230000035945 sensitivity Effects 0.000 claims description 32
- 238000004590 computer program Methods 0.000 claims description 26
- 238000012217 deletion Methods 0.000 claims description 7
- 230000037430 deletion Effects 0.000 claims description 7
- 238000010586 diagram Methods 0.000 description 11
- 230000004048 modification Effects 0.000 description 5
- 238000012986 modification Methods 0.000 description 5
- 238000005516 engineering process Methods 0.000 description 2
- 230000003068 static effect Effects 0.000 description 2
- 230000010485 coping Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
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/24—Querying
- G06F16/242—Query formulation
-
- 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/23—Updating
-
- 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/248—Presentation of query results
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application relates to an ad hoc query method, an ad hoc query device, computer equipment and a storage medium. The method comprises the following steps: after receiving the query statement of the user, analyzing the query statement to obtain the table name of the queried data table; determining a matching view of the query data table from the views subordinate to the user; the view belonging to the user is generated according to the data information and the user information, and each view is matched with different data tables respectively; replacing the table names in the query statement with view names of the matched views to obtain an updated query statement; and executing the updated query statement, and returning the query acquired matching view to the user. By adopting the method, the query efficiency and the query accuracy can be improved.
Description
Technical Field
The present invention relates to the field of big data query technologies, and in particular, to an impromptu query method, apparatus, computer device, and storage medium.
Background
With the development of big data technology, the dependence on big data in analysis and decision making of various industries is more obvious. In addition to conventional report forms and data statistics, an impromptu query platform is generally indispensable for flexibly coping with business requirements in daily work. The impromptu inquiry is an inquiry mode that a user flexibly selects inquiry conditions according to own requirements, and the system reacts immediately to quickly acquire inquiry results. For companies which rely on big data, the data is the wealth and the pulse of the company, so that the query at the present brings flexibility and convenience to analysis work, and meanwhile, the safety of the data also draws high importance.
To ensure data security, it is common to limit queries of employees at different levels of a company through division of rights. However, the conventional way of implementing authority limitation is mainly limited according to the matching of fields such as Select, from, where in SQL ((Structured Query Language, structured query language) query statement and the authority of a user, however, the method needs to invasively modify the query statement after the authority matching to obtain the query statement conforming to the authority of the user, which is easy to make mistakes, especially multi-table joint query and complex query statement, resulting in reduced query efficiency and accuracy.
Disclosure of Invention
In view of the foregoing, it is desirable to provide a method that can improve query efficiency and query accuracy. An impulse query method, apparatus, computer device, and storage medium.
An ad hoc query method, the method comprising:
after receiving a query statement of a user, analyzing the query statement to obtain a table name of a queried data table;
determining a matching view of the queried data table from a view subordinate to the user; the view belonging to the user is generated according to the data information and the user information, and each view is matched with different data tables respectively;
Replacing the table names in the query statement with the view names of the matched views to obtain an updated query statement;
and executing the updated query statement, and inquiring to acquire the matching view and returning the matching view to the user.
In one embodiment, the data information includes library-table-field metadata and a field sensitivity level; the user information comprises user list authority, table authority information, user dimension authority and user sensitivity level; generating a view from the data information and the user information, comprising:
determining a column which can be checked by a user according to the library-table-field metadata, the field sensitivity level, the user column authority and the user sensitivity level, and generating a data selection statement according to the checked column;
generating a condition tree for limiting the query range according to the user dimension authority, the user list authority and the table authority information;
and acquiring metadata of each data table in the database according to the data selection statement and the condition tree, and creating a view according to the metadata.
In one embodiment, the generating the condition tree for limiting the query scope according to the user dimension authority, the user column authority and the table authority information includes:
And performing traversal recursion according to the user dimension authority, the user list authority and the table authority information to generate a condition tree for limiting the query range.
In one embodiment, the generating the condition tree for limiting the query scope according to the user dimension authority, the user column authority and the table authority information includes:
inquiring all authority sub-nodes according to the user dimension authority, the user list authority and the table authority information;
and based on the right child node backtracking to the right root node and reversing, generating a condition tree for limiting the query range.
In one embodiment, the method further comprises:
when changed library-table-field metadata is received, comparing the changed library-table-field metadata with the original library-table-field metadata;
when the comparison determines that the update exists, updating the view corresponding to the updated database-table-field metadata;
when the comparison determines that the new addition exists, creating a view corresponding to the newly added library-table-field metadata;
when the comparison determines that deletion exists, deleting the view corresponding to the deleted library-table-field metadata.
In one embodiment, the method further comprises:
When the table authority information in the data information is changed, comparing the changed table authority information with the original table authority information;
when the comparison determines that the original table authority information has updated sensitive information, updating the sensitive information in a data table corresponding to the sensitive information and updating the view of the data table;
when the comparison determines that the original table authority information has newly added sensitive information, the newly added sensitive information is inserted into a data table corresponding to the sensitive information, and the view of the data table is updated.
In one embodiment, the method further comprises:
when the user authority information in the data information is updated, judging whether the user corresponding to the updated user authority information is an existing user or not; the user authority information comprises any one or more of user column authority, user dimension authority and user sensitivity level;
if the user is not the existing user, creating a new user and a corresponding view according to the user authority information;
if the user is the existing user, comparing the updated user authority information with the original user authority information;
when the comparison determines to change the user authority information of the existing user, updating the original user authority of the existing user and recreating the view of the existing user according to the updated user authority information;
When the comparison is determined to newly add the user authority information of the existing user, the newly added user authority information is inserted into the existing user, and the view of the existing user is built again;
and deleting the view corresponding to the existing user when the comparison determines that the user authority information of the existing user is deleted.
An ad hoc query device, the device comprising:
the receiving module is used for receiving the query statement of the user and then analyzing the query statement to acquire the table name of the queried data table;
a determining module, configured to determine a matching view of the query data table from views subordinate to the user; the view belonging to the user is generated according to the data information and the user information, and each view is matched with different data tables respectively;
a replacing module, configured to replace the table name in the query statement with a view name of the matching view, to obtain an updated query statement;
and the query module is used for executing the updated query statement, querying and acquiring the matching view and returning the matching view to the user.
A computer device comprising a memory storing a computer program and a processor implementing the steps of any one of the ad hoc query methods described above when the computer program is executed.
A computer readable storage medium having stored thereon a computer program which, when executed by a processor, implements the steps of the ad hoc querying method of any of the above.
The method, the device, the computer equipment and the storage medium for the impromptu query are used for analyzing the query statement after receiving the query statement of the user to obtain the table name of the queried data table; then determining a matching view of the query data table from views belonging to the user, wherein the views belonging to the user are generated according to the data information and the user information, and each view is respectively matched with different data tables; and then, replacing the table names in the query statement with the view names of the matching views, obtaining and executing the updated query statement, so that the query obtains the matching views and returns the matching views to the user. The method changes the query of the table by the user into the query of the view to which the table belongs by establishing the view to which the user belongs, and changes the traditional table for multi-party query into the prompt query by using the views of the user, thereby improving the query efficiency. Moreover, the query of the view is realized by simply replacing the name, invasive modification of the query statement is not needed, errors caused by the invasive modification are avoided, and the accuracy of the query is further improved while the efficiency is improved.
Drawings
FIG. 1 is a diagram of an application environment for an ad hoc query method in one embodiment;
FIG. 2 is a flow diagram of an ad hoc query method in one embodiment;
FIG. 3 is a flow diagram of view generation in one embodiment;
FIG. 4 is a schematic diagram of a user's regional dimension rights correspondence condition tree in one embodiment;
FIG. 5 is a flow diagram of a change view in one embodiment;
FIG. 6 is a flow chart of a change view in another embodiment;
FIG. 7 is a flow diagram of a change view in yet another embodiment;
FIG. 8 is a block diagram of an ad hoc querying device in one embodiment;
fig. 9 is an internal structural diagram of a computer device in one embodiment.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application will be further described in detail with reference to the accompanying drawings and examples. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the present application.
The ad hoc query method provided by the application can be applied to an application environment shown in fig. 1. Wherein the terminal 102 communicates with the server 104 via a network. After receiving the query statement sent by the user through the terminal 102, the server 104 analyzes the query statement to obtain the table name of the queried data table; server 104 determines a matching view of the query data table from the views subordinate to the user; the view belonging to the user is generated according to the data information and the user information, and each view is matched with different data tables respectively; the server 104 replaces the table names in the query statement with the view names of the matched views to obtain an updated query statement; the server 104 executes the updated query statement, and the query obtains the matching view and returns the matching view to the user. The terminal 102 may be, but not limited to, various personal computers, notebook computers, smartphones, tablet computers, and portable wearable devices, and the server 104 may be implemented by a stand-alone server or a server cluster composed of a plurality of servers.
In one embodiment, as shown in fig. 2, an ad hoc query method is provided, and the method is applied to the server in fig. 1 for illustration, and includes the following steps:
step S202, after receiving the query statement of the user, analyzing the query statement to obtain the table name of the queried data table.
The query statement is an SQL statement which is used for the user to perform the impromptu query through the user initiated by the terminal. The table names of the queried data tables are carried in the query statement for specifying the queried data tables.
Specifically, when the user has a query requirement, a query instruction is issued by performing a corresponding query operation on a query interface of the terminal, for example, the user selects the data table to be queried at the time from the data tables provided by the terminal interface. And the terminal generates a corresponding query statement according to the query operation of the user at the terminal and sends the query statement to the server. Or, the user may directly input a corresponding query sentence in a query input box corresponding to the query interface of the terminal, and the terminal obtains the query sentence input by the user and sends the query sentence to the server. After receiving the query statement of the user sent by the terminal, the server analyzes the query statement and obtains the table name of the queried data through analysis. Since SQL statements typically have a fixed syntax structure, table names are extracted from query statements at parsing time based on the nature of the syntax structure of the corresponding SQL statement.
Step S204, determining a matching view of the queried data table from the views subordinate to the user; the views belonging to the user are generated according to the data information and the user information, and the views are respectively matched with different data tables.
Wherein, the view is a logic concept in the data warehouse structure, and no materialization and data loading operation is performed, and only inquiry can be performed. Creation of a view may be understood as saving a piece of metadata, which is data used to describe the data. The data information includes database-table-field metadata (including metadata of a database, metadata of a data table, and metadata of a field), and a field sensitivity level. The user information refers to authority information of the user and the like, and comprises user list authority, list authority information, user dimension authority and user sensitivity level.
Specifically, the server creates views belonging to users corresponding to the user information, one view corresponding to each data table, in advance based on the data information and the user information. For example, a user corresponds to ten data tables, and ten views belonging to the user. Further, after the server parses the query sentence to obtain the table name of the queried data table, the view belonging to the user is acquired. Views belonging to a user may be added with a user identification or mapped to a user to identify the user to which the view corresponds. Then, the server matches the view corresponding to the queried data table from the views subordinate to the user, and obtains a matched view. The matching may be performed by identifying a match or metadata. For example, the view corresponding to the data table carries the table name of the data table, or the metadata of the queried data table is compared with the metadata stored in the view, and the view identical to the metadata of the queried data table is determined as the matching view.
Step S206, replacing the table names in the query statement with the view names of the matched views to obtain the updated query statement.
Specifically, after the server gets a matching view of the queried data table, the server replaces the table name in the query statement with the view name of the matching view. That is, it corresponds to deleting a table name in the query term, and then adding a view name of the matching view to a position corresponding to the table name. The query statement with the replaced name is an updated query statement.
Step S208, executing the updated query statement, and returning the query acquisition matching view to the user.
Specifically, the server executes the updated query statement, and the view corresponding to the queried data table can be queried through executing the updated query statement and returned to the user, so that the query task is completed.
According to the impromptu query method, after receiving the query statement of the user, the query statement is analyzed to obtain the table name of the queried data table; then determining a matching view of the query data table from views belonging to the user, wherein the views belonging to the user are generated according to the data information and the user information, and each view is respectively matched with different data tables; and then, replacing the table names in the query statement with the view names of the matching views, obtaining and executing the updated query statement, so that the query obtains the matching views and returns the matching views to the user. The method changes the query of the table by the user into the query of the view to which the table belongs by establishing the view to which the user belongs, and changes the traditional table for multi-party query into the prompt query by using the views of the user, thereby improving the query efficiency. Moreover, the query of the view is realized by simply replacing the name, invasive modification of the query statement is not needed, errors caused by the invasive modification are avoided, and the accuracy of the query is further improved while the efficiency is improved.
In one embodiment, as shown in FIG. 3, a flow diagram of view generation is provided. A step of generating a view from the data information and the user information, comprising: determining a column which can be checked by a user according to the database-table-field metadata, the field sensitivity level, the user column authority and the user sensitivity level, and generating a data selection statement according to the checked column; generating a condition tree for limiting the query range according to the user dimension authority, the user list authority and the table authority information; and acquiring metadata of each data table in the database according to the data selection statement and the condition tree, and creating a view according to the metadata.
Specifically, referring to FIG. 3, the server determines columns that can be viewed within the scope of the user's permissions based on the library-table-field metadata, the field sensitivity level, the user column permissions, and the user sensitivity level. The comparison of the field sensitivity level and the user sensitivity level can exclude fields which cannot be checked by the user, and the user column authority can exclude data columns which cannot be checked by the user. Then, the server generates a corresponding data selection statement (select statement) according to the finally determined column which can be viewed by the user, namely, the generated select statement can only acquire metadata corresponding to the data in the user query authority range from the library-table-field metadata after execution.
Meanwhile, the server generates a conditional statement (where statement) according to the user dimension authority, the authority information of the table (limited by the row authority of the user), and the column authority of the user, and the conditional statement generated in this embodiment is in the form of a tree. That is, a conditional tree is generated from the user dimension authority, the authority information of the table (limited to the row authority of the user), and the conditional statement in the form of a column authority generation tree of the user. The rights can be better isolated in the form of a tree. After the data selection statement and the condition tree are generated, the data selection statement and the condition tree are combined to obtain a creation view statement for creating the corresponding view of each data table. And generating views of the data tables belonging to the user by executing the metadata corresponding to the data table acquired by the view creation statement.
In one embodiment, generating a condition tree for limiting query scope based on user dimension rights, user column rights, and table rights information includes: and traversing and recursing according to the user dimension authority, the user list authority and the table authority information to generate a condition tree for limiting the query range.
In another embodiment, generating a condition tree for limiting query scope according to user dimension rights, user column rights, and table rights information includes: inquiring all authority sub-nodes according to the user dimension authority, the user list authority and the table authority information; and based on the inversion after the authority child node backtracks to the authority root node, generating a condition tree for limiting the query range.
Specifically, two ways of generating the condition tree exist, wherein the first way is to perform traversal recursion layer by layer according to user dimension authority, user column authority and table authority information, namely, from one node, the tree branch is traversed downwards, and then the other node with the same level returns to the same level to traverse downwards continuously to form the other tree branch. Another way is to find all the child nodes according to the user dimension rights, the user column rights and the table rights information (the child node of this embodiment can be understood as the last node). And then backtracking the child node to the root node, and reversing to obtain the root node. As shown in fig. 4, a schematic diagram of a condition tree corresponding to regional dimension rights of a user is provided. Taking fig. 4 as an example, the trace back can be understood as finding the child nodes of "green grassland street", "Wu Houou", "Mianyang city", "Leshan city", "Chongqing city" and "Wuhan city" first and then tracing back. For example, "Wuhan City" backtrack can be found in "Hubei province". And (4) reversing the linked list after all the child nodes are traced, and obtaining the tree shown in fig. 4. It is understood that the root node is seen from left to right (top to bottom) to the tree child node after inversion, and the child node is seen from left to right (top to bottom) to the root node before inversion.
In the embodiment, the view corresponding to the user is created through the data information and the user information, so that the subsequent impromptu inquiry is conveniently carried out through the view, and the inquiry efficiency and accuracy are improved. Moreover, the conditional statement is generated in a tree form, so that the authority can be better isolated.
In one embodiment, further comprising: when changed library-table-field metadata is received, comparing the changed library-table-field metadata with the original library-table-field metadata; when the comparison determines that the update exists, updating the view corresponding to the updated database-table-field metadata; when the comparison determines that the new addition exists, creating a view corresponding to the newly added library-table-field metadata; when the comparison determines that deletion exists, deleting the view corresponding to the deleted library-table-field metadata.
Specifically, the authority is not unchanged, and after the data information and the user information are changed, the view corresponding to the user should be changed and processed correspondingly in time in order to ensure the accuracy of the view.
Referring to fig. 5, when metadata of a library-table-field is changed, the changed library-table-field metadata is compared with original library-table-field metadata, which is metadata currently stored locally. By comparison, if the data corresponding to the changed metadata exist in the data corresponding to the original metadata, the change is determined to be data update, and the updated view corresponding to the database-table-field metadata is updated. If the data of the changed metadata does not exist in the data corresponding to the original metadata, determining that the change is new data, and creating a view corresponding to the newly added database-table-field metadata. If the data of which the changed metadata does not exist exists in the original metadata, the change is determined to be data deletion, and the deleted view corresponding to the database-table-field metadata is deleted. In this embodiment, the view accuracy is ensured by changing the view according to the metadata information.
In one embodiment, further comprising: when the table authority information in the data information is changed, comparing the changed table authority information with the original table authority information; when the comparison determines that the updated sensitive information exists in the original table authority information, updating the sensitive information in a data table corresponding to the sensitive information and updating the view of the data table; when the comparison determines that the original table authority information has newly added sensitive information, the newly added sensitive information is inserted into the data table corresponding to the sensitive information, and the view of the data table is updated.
Specifically, referring to fig. 6, when the table authority information is changed, sensitive fields in the changed data table are acquired, and the changed table authority information is compared with the original table authority information. When the changed table authority information exists and the original table authority information also exists, the sensitive information is updated according to the fact that the change is determined, and then the sensitive information is updated in the data table corresponding to the sensitive information and the view of the data table is updated. And when the changed table authority exists and the original table authority information does not exist, determining that the change is newly added with the sensitive information, inserting the newly added sensitive information into the data table corresponding to the sensitive information and updating the view of the data table. In this embodiment, the view accuracy is ensured by changing the view corresponding to the change of the table authority information.
In one embodiment, further comprising: when the user authority information in the data information is updated, judging whether the user corresponding to the updated user authority information is an existing user or not; the user authority information comprises any one or more of user column authority, user dimension authority and user sensitivity level; if the user is not the existing user, creating a new user and a corresponding view according to the user authority information; if the user is the existing user, comparing the updated user authority information with the original user authority information; when the comparison determines that the user authority information of the existing user is changed, updating the original user authority of the existing user and re-creating the view of the existing user according to the updated user authority information; when the comparison determines that the user authority information of the existing user is newly added, the newly added user authority information is inserted into the existing user, and the view of the existing user is newly built; and deleting the view corresponding to the existing user when the comparison determines that the user authority information of the existing user is deleted.
Specifically, referring to fig. 7, when user authority information in data information is updated, it is determined whether a user corresponding to the updated user authority information already exists, that is, whether the corresponding user is an existing user. If the user is not the existing user, a new user is firstly created according to the user authority information, and a corresponding view is created for the user after the user creation is completed.
And when the user is the existing user, the changed user authority information is compared with the original authority information of the user. And if the rights existing in the changed user right information are also in the original right information of the user through comparison, the change is the right update, the original user right of the existing user is further updated locally, and the view of the existing user is re-created according to the updated user right information. If the rights of the changed user rights information do not exist in the original rights information of the user, the change is the new user rights information of the existing user, the new user rights information is further inserted into the existing user, and the view of the existing user is newly built. And the rights which do not exist in the changed user rights information exist in the original rights information of the user, the change is to delete the user rights information of the existing user, and after the library table information corresponding to the changed rights is further selected, the corresponding view is deleted from the views of the user. In this embodiment, the view accuracy is ensured by changing the view corresponding to the user authority.
It should be understood that, although the steps in the flowchart of fig. 1 are shown in sequence as indicated by the arrows, the steps are not necessarily performed in sequence as indicated by the arrows. The steps are not strictly limited to the order of execution unless explicitly recited herein, and the steps may be executed in other orders. Moreover, at least a portion of the steps in fig. 1 may include a plurality of steps or stages, which are not necessarily performed at the same time, but may be performed at different times, and the order of the steps or stages is not necessarily sequential, but may be performed in rotation or alternatively with at least a portion of the steps or stages in other steps or other steps.
In one embodiment, as shown in fig. 8, there is provided an impromptu querying device, including: a receiving module 802, a determining module 804, a replacing module 806, and a querying module 808, wherein:
a receiving module 802, configured to parse the query sentence after receiving the query sentence of the user, and obtain a table name of the queried data table;
a determining module 804, configured to determine a matching view of the query data table from the views subordinate to the user; the view belonging to the user is generated according to the data information and the user information, and each view is matched with different data tables respectively;
a replacing module 806, configured to replace a table name in the query statement with a view name of the matching view, to obtain an updated query statement;
and a query module 808, configured to execute the updated query statement, and query the obtained matching view and return the matching view to the user.
In one embodiment, the ad hoc query device further comprises a creation module for determining a column viewable by the user according to the library-table-field metadata, the field sensitivity level, the user column authority and the user sensitivity level, and generating a data selection statement according to the viewable column; generating a condition tree for limiting the query range according to the user dimension authority, the user list authority and the table authority information; and acquiring metadata of each data table in the database according to the data selection statement and the condition tree, and creating a view according to the metadata.
In one embodiment, the creating module is further configured to perform traversal recursion according to the user dimension authority, the user column authority, and the table authority information, and generate a condition tree for limiting the query scope.
In one embodiment, the creating module is further configured to query all rights subnodes according to the user dimension rights, the user column rights, and the table rights information; and based on the inversion after the authority child node backtracks to the authority root node, generating a condition tree for limiting the query range.
In one embodiment, the ad hoc query device further comprises an updating module for comparing the changed library-table-field metadata with the original library-table-field metadata when the changed library-table-field metadata is received; when the comparison determines that the update exists, updating the view corresponding to the updated database-table-field metadata; when the comparison determines that the new addition exists, creating a view corresponding to the newly added library-table-field metadata; when the comparison determines that deletion exists, deleting the view corresponding to the deleted library-table-field metadata.
In one embodiment, the updating module is further configured to compare the changed table authority information with the original table authority information when the table authority information in the data information is changed; when the comparison determines that the updated sensitive information exists in the original table authority information, updating the sensitive information in a data table corresponding to the sensitive information and updating the view of the data table; when the comparison determines that the original table authority information has newly added sensitive information, the newly added sensitive information is inserted into the data table corresponding to the sensitive information, and the view of the data table is updated.
In one embodiment, the updating module is further configured to determine, when the user permission information in the data information is updated, whether the user corresponding to the updated user permission information is an existing user; the user authority information comprises any one or more of user column authority, user dimension authority and user sensitivity level; if the user is not the existing user, creating a new user and a corresponding view according to the user authority information; if the user is the existing user, comparing the updated user authority information with the original user authority information; when the comparison determines that the user authority information of the existing user is changed, updating the original user authority of the existing user and re-creating the view of the existing user according to the updated user authority information; when the comparison determines that the user authority information of the existing user is newly added, the newly added user authority information is inserted into the existing user, and the view of the existing user is newly built; and deleting the view corresponding to the existing user when the comparison determines that the user authority information of the existing user is deleted.
The specific limitation of the ad hoc query device may be referred to above as limitation of the ad hoc query method, and will not be described here. The modules in the ad hoc query device may be implemented in whole or in part by software, hardware, or a combination thereof. The above modules may be embedded in hardware or may be independent of a processor in the computer device, or may be stored in software in a memory in the computer device, so that the processor may call and execute operations corresponding to the above modules.
In one embodiment, a computer device is provided, which may be a server, and the internal structure of which may be as shown in fig. 9. The computer device includes a processor, a memory, and a network interface connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, computer programs, and a database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The database of the computer device is used for storing data such as views. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program is executed by a processor to implement an ad hoc query method.
It will be appreciated by those skilled in the art that the structure shown in fig. 9 is merely a block diagram of a portion of the structure associated with the present application and is not limiting of the computer device to which the present application applies, and that a particular computer device may include more or fewer components than shown, or may combine some of the components, or have a different arrangement of components.
In one embodiment, a computer device is provided comprising a memory and a processor, the memory having stored therein a computer program, the processor when executing the computer program performing the steps of:
after receiving the query statement of the user, analyzing the query statement to obtain the table name of the queried data table;
determining a matching view of the query data table from the views subordinate to the user; the view belonging to the user is generated according to the data information and the user information, and each view is matched with different data tables respectively;
replacing the table names in the query statement with view names of the matched views to obtain an updated query statement;
and executing the updated query statement, and returning the query acquired matching view to the user.
In one embodiment, the processor when executing the computer program further performs the steps of: determining a column which can be checked by a user according to the database-table-field metadata, the field sensitivity level, the user column authority and the user sensitivity level, and generating a data selection statement according to the checked column; generating a condition tree for limiting the query range according to the user dimension authority, the user list authority and the table authority information; and acquiring metadata of each data table in the database according to the data selection statement and the condition tree, and creating a view according to the metadata.
In one embodiment, the processor when executing the computer program further performs the steps of: and traversing and recursing according to the user dimension authority, the user list authority and the table authority information to generate a condition tree for limiting the query range.
In one embodiment, the processor when executing the computer program further performs the steps of: inquiring all authority sub-nodes according to the user dimension authority, the user list authority and the table authority information; and based on the inversion after the authority child node backtracks to the authority root node, generating a condition tree for limiting the query range.
In one embodiment, the processor when executing the computer program further performs the steps of: when changed library-table-field metadata is received, comparing the changed library-table-field metadata with the original library-table-field metadata; when the comparison determines that the update exists, updating the view corresponding to the updated database-table-field metadata; when the comparison determines that the new addition exists, creating a view corresponding to the newly added library-table-field metadata; when the comparison determines that deletion exists, deleting the view corresponding to the deleted library-table-field metadata.
In one embodiment, the processor when executing the computer program further performs the steps of: when the table authority information in the data information is changed, comparing the changed table authority information with the original table authority information; when the comparison determines that the updated sensitive information exists in the original table authority information, updating the sensitive information in a data table corresponding to the sensitive information and updating the view of the data table; when the comparison determines that the original table authority information has newly added sensitive information, the newly added sensitive information is inserted into the data table corresponding to the sensitive information, and the view of the data table is updated.
In one embodiment, the processor when executing the computer program further performs the steps of: when the user authority information in the data information is updated, judging whether the user corresponding to the updated user authority information is an existing user or not; the user authority information comprises any one or more of user column authority, user dimension authority and user sensitivity level; if the user is not the existing user, creating a new user and a corresponding view according to the user authority information; if the user is the existing user, comparing the updated user authority information with the original user authority information; when the comparison determines that the user authority information of the existing user is changed, updating the original user authority of the existing user and re-creating the view of the existing user according to the updated user authority information; when the comparison determines that the user authority information of the existing user is newly added, the newly added user authority information is inserted into the existing user, and the view of the existing user is newly built; and deleting the view corresponding to the existing user when the comparison determines that the user authority information of the existing user is deleted.
In one embodiment, a computer readable storage medium is provided having a computer program stored thereon, which when executed by a processor, performs the steps of:
After receiving the query statement of the user, analyzing the query statement to obtain the table name of the queried data table;
determining a matching view of the query data table from the views subordinate to the user; the view belonging to the user is generated according to the data information and the user information, and each view is matched with different data tables respectively;
replacing the table names in the query statement with view names of the matched views to obtain an updated query statement;
and executing the updated query statement, and returning the query acquired matching view to the user.
In one embodiment, the computer program when executed by the processor further performs the steps of: determining a column which can be checked by a user according to the database-table-field metadata, the field sensitivity level, the user column authority and the user sensitivity level, and generating a data selection statement according to the checked column; generating a condition tree for limiting the query range according to the user dimension authority, the user list authority and the table authority information; and acquiring metadata of each data table in the database according to the data selection statement and the condition tree, and creating a view according to the metadata.
In one embodiment, the computer program when executed by the processor further performs the steps of: and traversing and recursing according to the user dimension authority, the user list authority and the table authority information to generate a condition tree for limiting the query range.
In one embodiment, the computer program when executed by the processor further performs the steps of: inquiring all authority sub-nodes according to the user dimension authority, the user list authority and the table authority information; and based on the inversion after the authority child node backtracks to the authority root node, generating a condition tree for limiting the query range.
In one embodiment, the computer program when executed by the processor further performs the steps of: when changed library-table-field metadata is received, comparing the changed library-table-field metadata with the original library-table-field metadata; when the comparison determines that the update exists, updating the view corresponding to the updated database-table-field metadata; when the comparison determines that the new addition exists, creating a view corresponding to the newly added library-table-field metadata; when the comparison determines that deletion exists, deleting the view corresponding to the deleted library-table-field metadata.
In one embodiment, the computer program when executed by the processor further performs the steps of: when the table authority information in the data information is changed, comparing the changed table authority information with the original table authority information; when the comparison determines that the updated sensitive information exists in the original table authority information, updating the sensitive information in a data table corresponding to the sensitive information and updating the view of the data table; when the comparison determines that the original table authority information has newly added sensitive information, the newly added sensitive information is inserted into the data table corresponding to the sensitive information, and the view of the data table is updated.
In one embodiment, the computer program when executed by the processor further performs the steps of: when the user authority information in the data information is updated, judging whether the user corresponding to the updated user authority information is an existing user or not; the user authority information comprises any one or more of user column authority, user dimension authority and user sensitivity level; if the user is not the existing user, creating a new user and a corresponding view according to the user authority information; if the user is the existing user, comparing the updated user authority information with the original user authority information; when the comparison determines that the user authority information of the existing user is changed, updating the original user authority of the existing user and re-creating the view of the existing user according to the updated user authority information; when the comparison determines that the user authority information of the existing user is newly added, the newly added user authority information is inserted into the existing user, and the view of the existing user is newly built; and deleting the view corresponding to the existing user when the comparison determines that the user authority information of the existing user is deleted.
Those skilled in the art will appreciate that implementing all or part of the above described methods may be accomplished by way of a computer program stored on a non-transitory computer readable storage medium, which when executed, may comprise the steps of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in embodiments provided herein may include at least one of non-volatile and volatile memory. The nonvolatile Memory may include Read-Only Memory (ROM), magnetic tape, floppy disk, flash Memory, optical Memory, or the like. Volatile memory can include random access memory (Random Access Memory, RAM) or external cache memory. By way of illustration, and not limitation, RAM can be in the form of a variety of forms, such as static random access memory (Static Random Access Memory, SRAM) or dynamic random access memory (Dynamic Random Access Memory, DRAM), and the like.
The technical features of the above embodiments may be arbitrarily combined, and all possible combinations of the technical features in the above embodiments are not described for brevity of description, however, as long as there is no contradiction between the combinations of the technical features, they should be considered as the scope of the description.
The above examples merely represent a few embodiments of the present application, which are described in more detail and are not to be construed as limiting the scope of the invention. It should be noted that it would be apparent to those skilled in the art that various modifications and improvements could be made without departing from the spirit of the present application, which would be within the scope of the present application. Accordingly, the scope of protection of the present application is to be determined by the claims appended hereto.
Claims (10)
1. An ad hoc query method, the method comprising:
after receiving a query statement of a user, analyzing the query statement to obtain a table name of a queried data table;
determining a matching view of the queried data table from a view subordinate to the user; the view belonging to the user is generated according to the data information and the user information, and each view is matched with different data tables respectively;
Replacing the table names in the query statement with the view names of the matched views to obtain an updated query statement;
executing the updated query statement, querying to acquire the matching view and returning the matching view to the user;
wherein a view corresponds to a data table, the data information including library-table-field metadata and field sensitivity levels; the user information comprises user list authority, table authority information, user dimension authority and user sensitivity level; generating a view from the data information and the user information, comprising:
determining a column which can be checked by a user according to the library-table-field metadata, the field sensitivity level, the user column authority and the user sensitivity level, and generating a data selection statement according to the checked column;
generating a condition tree for limiting the query range according to the user dimension authority, the user list authority and the table authority information;
and acquiring metadata of each data table in the database according to the data selection statement and the condition tree, and creating a view according to the metadata.
2. The method of claim 1, wherein the generating a condition tree for limiting a query scope according to the user dimension authority, the user column authority, and the table authority information comprises:
And performing traversal recursion according to the user dimension authority, the user list authority and the table authority information to generate a condition tree for limiting the query range.
3. The method of claim 1, wherein the generating a condition tree for limiting a query scope according to the user dimension authority, the user column authority, and the table authority information comprises:
inquiring all authority sub-nodes according to the user dimension authority, the user list authority and the table authority information;
and based on the right child node backtracking to the right root node and reversing, generating a condition tree for limiting the query range.
4. The method of claim 1, wherein the obtaining metadata for each data table in the database based on the data selection statement and the condition tree comprises:
combining according to the data selection statement and the condition tree to obtain a creation view statement for creating a view corresponding to each data table;
and executing the view creating statement to obtain the metadata of each data table in the database.
5. The method according to claim 1, wherein the method further comprises:
When changed library-table-field metadata is received, comparing the changed library-table-field metadata with the original library-table-field metadata;
when the comparison determines that the update exists, updating the view corresponding to the updated database-table-field metadata;
when the comparison determines that the new addition exists, creating a view corresponding to the newly added library-table-field metadata;
when the comparison determines that deletion exists, deleting the view corresponding to the deleted library-table-field metadata.
6. The method according to claim 1, wherein the method further comprises:
when the table authority information in the data information is changed, comparing the changed table authority information with the original table authority information;
when the comparison determines that the original table authority information has updated sensitive information, updating the sensitive information in a data table corresponding to the sensitive information and updating the view of the data table;
when the comparison determines that the original table authority information has newly added sensitive information, the newly added sensitive information is inserted into a data table corresponding to the sensitive information, and the view of the data table is updated.
7. The method according to claim 1, wherein the method further comprises:
When the user authority information in the data information is updated, judging whether the user corresponding to the updated user authority information is an existing user or not; the user authority information comprises any one or more of user column authority, user dimension authority and user sensitivity level;
if the user is not the existing user, creating a new user and a corresponding view according to the user authority information;
if the user is the existing user, comparing the updated user authority information with the original user authority information;
when the comparison determines to change the user authority information of the existing user, updating the original user authority of the existing user and recreating the view of the existing user according to the updated user authority information;
when the comparison is determined to newly add the user authority information of the existing user, the newly added user authority information is inserted into the existing user, and the view of the existing user is built again;
and deleting the view corresponding to the existing user when the comparison determines that the user authority information of the existing user is deleted.
8. An ad hoc query device, the device comprising:
the receiving module is used for receiving the query statement of the user and then analyzing the query statement to acquire the table name of the queried data table;
A determining module, configured to determine a matching view of the query data table from views subordinate to the user; the view belonging to the user is generated according to the data information and the user information, and each view is matched with different data tables respectively;
a replacing module, configured to replace the table name in the query statement with a view name of the matching view, to obtain an updated query statement;
the query module is used for executing the updated query statement, querying and acquiring the matching view and returning the matching view to the user;
wherein a view corresponds to a data table, the data information including library-table-field metadata and field sensitivity levels; the user information comprises user list authority, table authority information, user dimension authority and user sensitivity level;
the creation module is used for determining a column which can be checked by a user according to the library-table-field metadata, the field sensitivity level, the user column authority and the user sensitivity level, and generating data selection sentences according to the checked column; generating a condition tree for limiting the query range according to the user dimension authority, the user list authority and the table authority information; and acquiring metadata of each data table in the database according to the data selection statement and the condition tree, and creating a view according to the metadata.
9. A computer device comprising a memory and a processor, the memory storing a computer program, characterized in that the processor implements the steps of the method of any of claims 1 to 7 when the computer program is executed.
10. A computer readable storage medium, on which a computer program is stored, characterized in that the computer program, when being executed by a processor, implements the steps of the method of any of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011439737.5A CN112416966B (en) | 2020-12-11 | 2020-12-11 | Impromptu query method, impromptu query device, computer device and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011439737.5A CN112416966B (en) | 2020-12-11 | 2020-12-11 | Impromptu query method, impromptu query device, computer device and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112416966A CN112416966A (en) | 2021-02-26 |
| CN112416966B true CN112416966B (en) | 2024-01-26 |
Family
ID=74775044
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011439737.5A Active CN112416966B (en) | 2020-12-11 | 2020-12-11 | Impromptu query method, impromptu query device, computer device and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112416966B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN117708879A (en) * | 2023-12-13 | 2024-03-15 | 北京镜舟科技有限公司 | Information authority control method, system, terminal and storage medium |
Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6058391A (en) * | 1997-12-17 | 2000-05-02 | Mci Communications Corporation | Enhanced user view/update capability for managing data from relational tables |
| US6253203B1 (en) * | 1998-10-02 | 2001-06-26 | Ncr Corporation | Privacy-enhanced database |
| CN101582881A (en) * | 2008-05-14 | 2009-11-18 | 华为技术有限公司 | Method and device for controlling access |
| CN101794312A (en) * | 2010-03-08 | 2010-08-04 | 上海交通大学 | XML (Extensive Makeup Language) access control method based on security view |
| CN102402615A (en) * | 2011-12-22 | 2012-04-04 | 哈尔滨工程大学 | Method for tracking source information based on structured query language (SQL) sentences |
| CN103530538A (en) * | 2012-07-03 | 2014-01-22 | 沈阳高精数控技术有限公司 | XML safety view querying method based on Schema |
| CN107844711A (en) * | 2017-10-16 | 2018-03-27 | 平安科技（深圳）有限公司 | Data manipulation authority partition method, application server and computer-readable recording medium |
| CN108537062A (en) * | 2018-04-24 | 2018-09-14 | 山东华软金盾软件股份有限公司 | A kind of method of database data dynamic encryption |
| CN110046950A (en) * | 2018-12-25 | 2019-07-23 | 阿里巴巴集团控股有限公司 | Method for processing business, device and equipment |
| CN110968580A (en) * | 2018-09-30 | 2020-04-07 | 北京国双科技有限公司 | Method and device for creating data storage structure |
| CN111414591A (en) * | 2020-03-02 | 2020-07-14 | 中国建设银行股份有限公司 | Workflow management method and device |
| CN111488360A (en) * | 2020-04-14 | 2020-08-04 | 上海达梦数据库有限公司 | Method, device, equipment and storage medium for implementing row-level security |
Family Cites Families (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20100262625A1 (en) * | 2009-04-08 | 2010-10-14 | Glenn Robert Pittenger | Method and system for fine-granularity access control for database entities |
| US9069987B2 (en) * | 2013-06-21 | 2015-06-30 | International Business Machines Corporation | Secure data access using SQL query rewrites |
-
2020
- 2020-12-11 CN CN202011439737.5A patent/CN112416966B/en active Active
Patent Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6058391A (en) * | 1997-12-17 | 2000-05-02 | Mci Communications Corporation | Enhanced user view/update capability for managing data from relational tables |
| US6253203B1 (en) * | 1998-10-02 | 2001-06-26 | Ncr Corporation | Privacy-enhanced database |
| CN101582881A (en) * | 2008-05-14 | 2009-11-18 | 华为技术有限公司 | Method and device for controlling access |
| CN101794312A (en) * | 2010-03-08 | 2010-08-04 | 上海交通大学 | XML (Extensive Makeup Language) access control method based on security view |
| CN102402615A (en) * | 2011-12-22 | 2012-04-04 | 哈尔滨工程大学 | Method for tracking source information based on structured query language (SQL) sentences |
| CN103530538A (en) * | 2012-07-03 | 2014-01-22 | 沈阳高精数控技术有限公司 | XML safety view querying method based on Schema |
| CN107844711A (en) * | 2017-10-16 | 2018-03-27 | 平安科技（深圳）有限公司 | Data manipulation authority partition method, application server and computer-readable recording medium |
| CN108537062A (en) * | 2018-04-24 | 2018-09-14 | 山东华软金盾软件股份有限公司 | A kind of method of database data dynamic encryption |
| CN110968580A (en) * | 2018-09-30 | 2020-04-07 | 北京国双科技有限公司 | Method and device for creating data storage structure |
| CN110046950A (en) * | 2018-12-25 | 2019-07-23 | 阿里巴巴集团控股有限公司 | Method for processing business, device and equipment |
| CN111414591A (en) * | 2020-03-02 | 2020-07-14 | 中国建设银行股份有限公司 | Workflow management method and device |
| CN111488360A (en) * | 2020-04-14 | 2020-08-04 | 上海达梦数据库有限公司 | Method, device, equipment and storage medium for implementing row-level security |
Non-Patent Citations (2)
| Title |
|---|
| Research of a QPC-based heterogeneous database query method;Mei Li 等;《2010 3rd International Conference on Advanced Computer Theory and Engineering(ICACTE)》;V5-347-V5-350 * |
| 提高计算机数据库安全保密水平的技术措施;赵芳;《信息与电脑(理论版)》(第24期);185-186 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112416966A (en) | 2021-02-26 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Souibgui et al. | Data quality in ETL process: A preliminary study | |
| CN107402988B (en) | Distributed NewSQL database system and semi-structured data query method | |
| CN110908997B (en) | Data blood relationship construction method and device, server and readable storage medium | |
| US10657111B2 (en) | Computer-implemented method for storing unlimited amount of data as a mind map in relational database systems | |
| US7836100B2 (en) | Calculating and storing data structures including using calculated columns associated with a database system | |
| US20210256023A1 (en) | Subquery predicate generation to reduce processing in a multi-table join | |
| CN110019384B (en) | Method for acquiring blood edge data, method and device for providing blood edge data | |
| US11449477B2 (en) | Systems and methods for context-independent database search paths | |
| US9218394B2 (en) | Reading rows from memory prior to reading rows from secondary storage | |
| WO2021179722A1 (en) | Sql statement parsing method and system, and computer device and storage medium | |
| CN112667630B (en) | Method, device, equipment and storage medium for integrating business report | |
| CN109710220B (en) | Relational database query method, relational database query device, relational database query equipment and storage medium | |
| CN108563694B (en) | SQL execution method and device for logic deletion, computer equipment and storage medium | |
| CN112579610A (en) | Multi-data source structure analysis method, system, terminal device and storage medium | |
| CN112434027A (en) | Indexing method and device for multi-dimensional data, computer equipment and storage medium | |
| CN114253995B (en) | Data tracing method, device, equipment and computer readable storage medium | |
| CN112416966B (en) | Impromptu query method, impromptu query device, computer device and storage medium | |
| CN109213775B (en) | Search method, search device, computer equipment and storage medium | |
| CN111125216B (en) | Method and device for importing data into Phoenix | |
| CN115114325B (en) | Data query method and device, electronic equipment and storage medium | |
| CN116795859A (en) | Data analysis method, device, computer equipment and storage medium | |
| CN111949684B (en) | SQL sentence execution method, device, equipment and storage medium | |
| CN113868138A (en) | Method, system, equipment and storage medium for acquiring test data | |
| CN114281842A (en) | Method and device for sub-table query of database | |
| US20240193135A1 (en) | Systems, methods, and media for accessing derivative properties from a post relational database utilizing a logical schema instruction that includes a base object identifier |
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 |