CN112416966A - Ad hoc query method, apparatus, computer device and storage medium - Google Patents
Ad hoc query method, apparatus, computer device and storage medium Download PDFInfo
- Publication number
- CN112416966A CN112416966A CN202011439737.5A CN202011439737A CN112416966A CN 112416966 A CN112416966 A CN 112416966A CN 202011439737 A CN202011439737 A CN 202011439737A CN 112416966 A CN112416966 A CN 112416966A
- Authority
- CN
- China
- Prior art keywords
- user
- authority
- information
- view
- 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.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 42
- 230000035945 sensitivity Effects 0.000 claims description 28
- 238000004590 computer program Methods 0.000 claims description 25
- 230000008859 change Effects 0.000 claims description 11
- 238000012217 deletion Methods 0.000 claims description 7
- 230000037430 deletion Effects 0.000 claims description 7
- 238000010586 diagram Methods 0.000 description 10
- 230000004048 modification Effects 0.000 description 4
- 238000012986 modification Methods 0.000 description 4
- 238000005516 engineering process Methods 0.000 description 2
- 230000008569 process Effects 0.000 description 2
- 230000003287 optical effect Effects 0.000 description 1
- 230000009467 reduction Effects 0.000 description 1
- 230000003068 static effect Effects 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/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
Abstract
The application relates to an ad hoc query method, an ad hoc query device, a computer device and a storage medium. The method comprises the following steps: receiving a query statement of a user, analyzing the query statement, and acquiring a table name of a queried data table; determining a matching view of the query data table from the views belonging 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; replacing the table name in the query statement with the view name of the matched view to obtain an updated query statement; and executing the updated query statement, and querying to obtain a matching view and returning the matching view to the user. By adopting the method, the query efficiency and the query accuracy can be improved.
Description
Technical Field
The present application relates to the field of big data query technologies, and in particular, to an ad hoc 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 of various industries is more obvious. In addition to conventional reporting and data statistics, an ad hoc query platform is generally indispensable in daily work to flexibly meet business requirements. The ad hoc query is a query mode that a user flexibly selects query conditions according to own requirements, a system reacts instantly and obtains query results quickly. For the companies which rely on big data at present, the data is the wealth and the life pulse of the companies, so that the safety of the data is also highly emphasized while the convenience and the flexibility are brought to the analysis work by the instant query.
In order to ensure the security of data, the inquiry of employees at different levels of a company is limited by dividing the authority. However, in this way, after permission matching, invasive modification needs to be performed on the Query statement to obtain a Query statement that meets the permission of the user, which is prone to errors, especially in multi-table joint Query and complex Query statements, resulting in reduction of efficiency and accuracy of Query.
Disclosure of Invention
In view of the above, it is necessary to provide a method capable of improving the efficiency and accuracy of query. An ad hoc query method, apparatus, computer device, and storage medium.
An ad hoc query method, the method comprising:
receiving a query statement of a user, analyzing the query statement, and acquiring a table name of a queried data table;
determining a matching view of the queried data table from views belonging to the user; the views belonging to the user are generated according to data information and user information, and the views are respectively matched with different data tables;
replacing the table name in the query statement with the view name of the matched view to obtain an updated query statement;
and executing the updated query statement, querying to obtain the matched view, and returning the matched 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 column authority, table authority information, user dimension authority and user sensitivity level; generating a view according to the data information and the user information, comprising:
determining columns which can be checked by a user according to the database-table-field metadata, the field sensitivity level, the user column permission and the user sensitivity level, and generating data selection statements according to the columns which can be checked;
generating a condition tree for limiting the query range according to the user dimension authority, the user column authority and the table authority information;
and acquiring metadata of each data table in a database according to the data selection statement and the condition tree, and creating a view according to the metadata.
In one embodiment, the generating a condition tree for limiting a query range 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 column authority and the table authority information to generate a condition tree for limiting the query range.
In one embodiment, the generating a condition tree for limiting a query range according to the user dimension authority, the user column authority, and the table authority information includes:
inquiring all authority child nodes according to the user dimension authority, the user column authority and the table authority information;
and reversely turning after the permission child node backtracks to the permission root node, and generating a condition tree for limiting the query range.
In one embodiment, the method further comprises:
when receiving the altered library-table-field metadata, comparing the altered library-table-field metadata with the original library-table-field metadata;
when the comparison determines that the updating exists, updating the view corresponding to the updated database-table-field metadata;
when the comparison determines that new addition exists, creating a view corresponding to the metadata of the newly added library-table-field;
and when the comparison determines that deletion exists, deleting the view corresponding to the deleted database-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 original table authority information is compared and determined to have updated sensitive information, updating the sensitive information in a data table corresponding to the sensitive information and updating a view of the data table;
and when the original table authority information is compared and determined to have newly added sensitive information, inserting the newly added sensitive information into a data table corresponding to the sensitive information and updating the view of the data table.
In one embodiment, the method further comprises:
when the user authority information in the data information is updated, judging whether a user corresponding to the updated user authority 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, a new user and a corresponding view are created according to the user permission information;
if the user exists, the updated user authority information is compared with the original user authority information;
when the comparison is determined 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 result shows that the user authority information of the existing user is newly added, inserting the newly added user authority information into the existing user and re-establishing the view of the existing user;
and when the comparison is determined to delete the user authority information of the existing user, deleting the view corresponding to the existing user.
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 determination module for determining a matching view of the lookup data table from views belonging to the user; the views belonging to the user are generated according to data information and user information, and the views are respectively matched with different data tables;
a replacing module, configured to replace the table name in the query statement with the view name of the matching view, so as to obtain an updated query statement;
and the query module is used for executing the updated query statement, querying and acquiring the matched view and returning the matched view to the user.
A computer device comprising a memory storing a computer program and a processor implementing the steps of any of the above ad hoc query methods when the processor executes the computer program.
A computer-readable storage medium, on which a computer program is stored which, when being executed by a processor, carries out the steps of the ad hoc query method according to any one of the preceding claims.
According to the ad hoc query method, the ad hoc query device, the computer equipment and the storage medium, 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 the view belonging to the user, wherein the view belonging to the user is 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 name in the query statement with the view name of the matched view to obtain the updated query statement and execute the updated query statement, so as to query and obtain the matched view and return the matched view to the user. The method changes the query of the user to the view to which the user belongs by establishing the view belonging to the user, and changes the traditional one-table multi-party query into the respective view for the ad hoc query, thereby improving the query efficiency. Moreover, the view query only needs to simply replace the name, and the query statement does not need to be modified invasively, so that errors caused by invasive modification are avoided, the efficiency is improved, and the query accuracy is further improved.
Drawings
FIG. 1 is a diagram of an application environment for the ad hoc query method in one embodiment;
FIG. 2 is a flow diagram that illustrates a method for ad hoc query in one embodiment;
FIG. 3 is a schematic flow chart of view generation in one embodiment;
FIG. 4 is a diagram illustrating a conditional tree corresponding to regional dimension permissions of a user in one embodiment;
FIG. 5 is a flow diagram illustrating an alternate view in one embodiment;
FIG. 6 is a flow chart illustrating an alternate view in another embodiment;
FIG. 7 is a flow chart illustrating an alternate view in accordance with yet another embodiment;
FIG. 8 is a block diagram that illustrates the architecture of an ad hoc query device, according to an embodiment;
FIG. 9 is a diagram illustrating an internal structure of a computer device according to an embodiment.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
The ad hoc query method provided by the application can be applied to the application environment shown in fig. 1. Wherein the terminal 102 communicates with the server 104 via a network. The server 104 receives the query statement sent by the user through the terminal 102 and then analyzes the query statement to obtain the table name of the queried data table; server 104 determines a matching view of the lookup data table from views belonging 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; the server 104 replaces the table name in the query statement with the view name of the matched view to obtain an updated query statement; the server 104 executes the updated query statement, queries to obtain 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, smart phones, tablet computers, and portable wearable devices, and the server 104 may be implemented by an independent server or a server cluster formed by a plurality of servers.
In one embodiment, as shown in fig. 2, an ad hoc query method is provided, which is described by taking the method as an example applied to the server in fig. 1, and includes the following steps:
step S202, after receiving the query statement of the user, analyzing the query statement to acquire the table name of the queried data table.
The query statement is an SQL statement initiated by a user through a terminal and used for carrying out ad hoc query by the user. The table name of the queried data table is carried in a query statement for specifying the queried data table.
Specifically, when a 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 a data table queried this time from data tables provided by the terminal interface. And the terminal generates a corresponding query statement according to the query operation of the user on the terminal and sends the query statement to the server. Or, the user may directly input a corresponding query statement in a query input box corresponding to a query interface of the terminal, and the terminal acquires the query statement input by the user and then sends the query statement to the server. And when the server receives the query statement of the user sent by the terminal, analyzing the query statement, and acquiring the table name of the queried data through analysis. Since SQL statements generally have a fixed syntactic structure, the table names are extracted from the query statement at parsing time according to the characteristics of the syntactic structure corresponding to the SQL statement.
Step S204, determining a matching view of the queried data table from the view belonging 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.
The view is a logic concept in a data warehouse structure, does not perform materialization and data loading operation, and only can be queried. The creation of a view may be understood as holding a piece of metadata, which is data that describes the data. The data information includes library-table-field metadata (including metadata for databases, metadata for data tables, and metadata for fields), field sensitivity levels. The user information refers to the authority information of the user and the like, and comprises user column authority, table authority information, user dimension authority and user sensitivity level.
Specifically, the server creates views belonging to users corresponding to the user information in advance according to the data information and the user information, and one view corresponds to one data table. For example, a user may have ten data tables and ten views belonging to the user. Furthermore, after the server analyzes the query statement to obtain the table name of the queried data table, the view belonging to the user is obtained. Views belonging to a user may be added with a user identification or mapped with a user to identify the user corresponding to the view. Then, the server matches the view corresponding to the queried data table from the view belonging to the user, and obtains a matching view. The matching may be by identification matching or metadata matching. 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 to be the matching view.
Step S206, the table name in the query statement is replaced by the view name of the matched view, and the updated query statement is obtained.
Specifically, after the server obtains the 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, after the table name in the query statement is deleted, the view name of the matching view is added to the position corresponding to the table name. And the query statement after name replacement is the updated query statement.
And step S208, executing the updated query statement, querying to obtain a matched view, and returning the matched view to the user.
Specifically, the server executes the updated query statement, and the view corresponding to the queried data table can be queried and returned to the user by executing the updated query statement, so that the query task is completed.
In the ad hoc query method, after receiving a query statement of a user, the query statement is analyzed to obtain a table name of a queried data table; then determining a matching view of the query data table from the view belonging to the user, wherein the view belonging to the user is 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 name in the query statement with the view name of the matched view to obtain the updated query statement and execute the updated query statement, so as to query and obtain the matched view and return the matched view to the user. The method changes the query of the user to the view to which the user belongs by establishing the view belonging to the user, and changes the traditional one-table multi-party query into the respective view for the ad hoc query, thereby improving the query efficiency. Moreover, the view query only needs to simply replace the name, and the query statement does not need to be modified invasively, so that errors caused by invasive modification are avoided, the efficiency is improved, and the query accuracy is further improved.
In one embodiment, as shown in FIG. 3, a flow diagram for view generation is provided. The step of generating the view according to the data information and the user information comprises the following steps: determining columns 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 data selection statements according to the columns which can be checked; generating a condition tree for limiting the query range according to the user dimension authority, the user column 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 the columns that can be viewed within the user's privilege level based on the library-table-field metadata, the field sensitivity level, the user's column privileges, and the user sensitivity level. The comparison between the field sensitivity level and the user sensitivity level can exclude fields which cannot be viewed by the user, and the user column permission can exclude data columns which cannot be viewed 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 being executed.
Meanwhile, the server generates a conditional statement (where statement) according to the user dimension authority, the authority information of the table (limited to the row authority of the user) and the column authority of the user, and the generated conditional statement is in a tree form in this embodiment. That is, the generated conditional tree is obtained from the user dimension authority, the authority information of the table (the authority of the row of the user is limited) and the column authority of the user to generate the conditional statement in the form of the tree. The authority can be better isolated in a tree form. And after the data selection statement and the conditional tree are generated, combining the data selection statement and the conditional tree to obtain a view creation statement for creating a view corresponding to each data table. And executing the view creating statement to acquire metadata corresponding to the data tables to generate views of the data tables belonging to the user.
In one embodiment, generating a condition tree for limiting the query scope according to the user dimension authority, the user column authority and the table authority information comprises: and traversing and recursing according to the user dimension authority, the user column 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 the 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 column authority and the table authority information; and reversing after backtracking to the authority root node based on the authority child node, and generating a condition tree for limiting the query range.
Specifically, there are two ways of generating the conditional tree, the first way is to perform traversal recursion layer by layer according to the user dimension authority, the user column authority and the table authority information, that is, after starting from one node, traversing downwards to form a tree branch, returning to another node of the same level, and continuously traversing downwards to form another tree branch. Another way is to find all child nodes (a child node in this embodiment may be understood as the last node) according to the user dimension authority, the user column authority, and the table authority information. And then, backtracking from the child nodes to the root node and then reversing to obtain the root node. As shown in fig. 4, a schematic diagram of a conditional tree corresponding to regional dimension permissions of a user is provided. Taking fig. 4 as an example, the backtracking can be understood as backtracking after finding the child nodes "green grassland street", "wuhou district", "mianyang city", "le shan city", "Chongqing city" and "wuhan city". For example, the backtracking of "Wuhan City" can lead to "Hubei province". And after all child nodes finish backtracking, performing linked list inversion to obtain the tree shown in fig. 4. It is understood that after inversion, from left to right (top to bottom) is the root node to the tree child nodes, and before inversion, from left to right (top to bottom) is the child node to the root node.
In the embodiment, the view corresponding to the user is created through the data information and the user information, so that the follow-up ad hoc query can be conveniently carried out through the view, and the query efficiency and accuracy are improved. Moreover, the conditional statements are generated in a tree form, and the authority can be better isolated.
In one embodiment, further comprising: when receiving the altered library-table-field metadata, comparing the altered library-table-field metadata with the original library-table-field metadata; when the comparison determines that the updating exists, updating the view corresponding to the updated database-table-field metadata; when the comparison determines that new addition exists, creating a view corresponding to the metadata of the newly added library-table-field; and when the comparison determines that deletion exists, deleting the view corresponding to the deleted database-table-field metadata.
Specifically, the authority cannot be changed, and when the data information and the user information are changed, the view corresponding to the user should be changed and processed 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 the metadata currently stored locally. By comparison, if the data corresponding to the changed metadata exists in the data corresponding to the original metadata, the change is determined to be data update, and the view corresponding to the updated database-table-field metadata is updated. And if the data of the changed metadata does not exist in the data corresponding to the original metadata, determining that the change is data addition, and creating a view corresponding to the newly added library-table-field metadata. And if the data which does not exist in the changed metadata exists in the original metadata, determining that the change is data deletion, and deleting the view corresponding to the deleted database-table-field metadata. In the embodiment, the view accuracy is ensured by changing the view corresponding to the change of 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 original table authority information is compared and determined to have updated sensitive information, updating the sensitive information in a data table corresponding to the sensitive information and updating a view of the data table; and when the original table authority information is compared and determined to have the newly added 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.
Specifically, referring to fig. 6, when the table authority information is changed, the sensitive field in the changed data table is obtained, and the changed table authority information is compared with the original table authority information. And when the changed table authority information exists and the original table authority information also exists, determining that the change is the update of the sensitive information, and updating the sensitive information in the data table corresponding to the sensitive information and updating the view of the data table. And when the changed table authority exists and the original table authority information does not exist, determining that the change is newly added 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 the embodiment, the view accuracy is ensured by changing the corresponding changed view according to the table authority information.
In one embodiment, further comprising: when the user authority information in the data information is updated, judging whether a user corresponding to the updated user authority 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, a new user and a corresponding view are created according to the user permission information; if the user exists, the updated user authority information is compared with the original user authority information; when the comparison confirms that the user authority information of the existing user is changed, the original user authority of the existing user is updated, and the view of the existing user is created again according to the updated user authority information; when the comparison result shows that the user authority information of the existing user is newly added, inserting the newly added user authority information into the existing user and re-establishing the view of the existing user; and when the comparison result shows that the user authority information of the existing user is deleted, deleting the view corresponding to the existing user.
Specifically, referring to fig. 7, when the user authority information in the 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 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, comparing the changed user authority information with the original authority information of the user. And if the permission of the changed user permission information also exists in the original permission information of the user through comparison, the change is the permission update, the original user permission of the existing user is further updated locally, and the view of the existing user is created again according to the updated user permission information. If the authority of the changed user authority information does not exist in the original authority information of the user, the change is that the user authority information of the existing user is newly added, the newly added user authority information is further inserted into the existing user, and the view of the existing user is newly built. And if the authority of which the changed user authority information does not exist exists in the original authority information of the user, the change means that the user authority information of the existing user is deleted, and after library table information corresponding to the changed authority is further selected, the corresponding view is deleted from the view of the user. In the embodiment, the view accuracy is ensured by changing the corresponding changed view according to the user permission.
It should be understood that, although the steps in the flowchart of fig. 1 are shown in order as indicated by the arrows, the steps are not necessarily performed in order as indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least a portion of the steps in fig. 1 may include multiple steps or multiple stages, which are not necessarily performed at the same time, but may be performed at different times, which are not necessarily performed in sequence, but may be performed in turn or alternately with other steps or at least a portion of the other steps or stages.
In one embodiment, as shown in fig. 8, there is provided an ad hoc query 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 receive a query statement of a user, analyze the query statement, and obtain a table name of a queried data table;
a determining module 804 for determining a matching view of the lookup data table from views belonging 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;
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 the query module 808 is configured to execute the updated query statement, query to obtain the matching view, and return the matching view to the user.
In one embodiment, the ad hoc query device further comprises a creating module, configured to determine columns that can be viewed by a user according to the library-table-field metadata, the field sensitivity level, the user column permission, and the user sensitivity level, and generate data selection statements according to the columns that can be viewed; generating a condition tree for limiting the query range according to the user dimension authority, the user column 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 range.
In one embodiment, the creating module is further configured to query all the authority child nodes according to the user dimension authority, the user column authority and the table authority information; and reversing after backtracking to the authority root node based on the authority child node, and generating a condition tree for limiting the query range.
In one embodiment, the ad hoc query device further comprises an update 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 updating exists, updating the view corresponding to the updated database-table-field metadata; when the comparison determines that new addition exists, creating a view corresponding to the metadata of the newly added library-table-field; and when the comparison determines that deletion exists, deleting the view corresponding to the deleted database-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 original table authority information is compared and determined to have updated sensitive information, updating the sensitive information in a data table corresponding to the sensitive information and updating a view of the data table; and when the original table authority information is compared and determined to have the newly added 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 one embodiment, the updating module is further configured to determine whether a user corresponding to the updated user permission information is an existing user when the user permission information in the data information is updated; 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, a new user and a corresponding view are created according to the user permission information; if the user exists, the updated user authority information is compared with the original user authority information; when the comparison confirms that the user authority information of the existing user is changed, the original user authority of the existing user is updated, and the view of the existing user is created again according to the updated user authority information; when the comparison result shows that the user authority information of the existing user is newly added, inserting the newly added user authority information into the existing user and re-establishing the view of the existing user; and when the comparison result shows that the user authority information of the existing user is deleted, deleting the view corresponding to the existing user.
For the specific limitations of the ad hoc query device, reference may be made to the above limitations of the ad hoc query method, which are not described herein again. The modules in the ad hoc inquiry apparatus may be implemented in whole or in part by software, hardware, and a combination thereof. The modules can be embedded in a hardware form or independent from a processor in the computer device, and can also be stored in a memory in the computer device in a software form, so that the processor can call and execute operations corresponding to the modules.
In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram 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 comprises a nonvolatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of an operating system and computer programs in the non-volatile storage medium. 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.
Those skilled in the art will appreciate that the architecture shown in fig. 9 is merely a block diagram of some of the structures associated with the disclosed aspects and is not intended to limit the computing devices to which the disclosed aspects apply, as particular computing devices may include more or less components than those shown, or may combine certain 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 a computer program stored therein, the processor implementing the following steps when executing the computer program:
receiving a query statement of a user, analyzing the query statement, and acquiring a table name of a queried data table;
determining a matching view of the query data table from the views belonging 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;
replacing the table name in the query statement with the view name of the matched view to obtain an updated query statement;
and executing the updated query statement, and querying to obtain a matching view and returning the matching view to the user.
In one embodiment, the processor, when executing the computer program, further performs the steps of: determining columns 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 data selection statements according to the columns which can be checked; generating a condition tree for limiting the query range according to the user dimension authority, the user column 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 column 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 column authority and the table authority information; and reversing after backtracking to the authority root node based on the authority child node, and 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 receiving the altered library-table-field metadata, comparing the altered library-table-field metadata with the original library-table-field metadata; when the comparison determines that the updating exists, updating the view corresponding to the updated database-table-field metadata; when the comparison determines that new addition exists, creating a view corresponding to the metadata of the newly added library-table-field; and when the comparison determines that deletion exists, deleting the view corresponding to the deleted database-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 original table authority information is compared and determined to have updated sensitive information, updating the sensitive information in a data table corresponding to the sensitive information and updating a view of the data table; and when the original table authority information is compared and determined to have the newly added 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 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 a user corresponding to the updated user authority 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, a new user and a corresponding view are created according to the user permission information; if the user exists, the updated user authority information is compared with the original user authority information; when the comparison confirms that the user authority information of the existing user is changed, the original user authority of the existing user is updated, and the view of the existing user is created again according to the updated user authority information; when the comparison result shows that the user authority information of the existing user is newly added, inserting the newly added user authority information into the existing user and re-establishing the view of the existing user; and when the comparison result shows that the user authority information of the existing user is deleted, deleting the view corresponding to the existing user.
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:
receiving a query statement of a user, analyzing the query statement, and acquiring a table name of a queried data table;
determining a matching view of the query data table from the views belonging 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;
replacing the table name in the query statement with the view name of the matched view to obtain an updated query statement;
and executing the updated query statement, and querying to obtain a matching view and returning the matching view to the user.
In one embodiment, the computer program when executed by the processor further performs the steps of: determining columns 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 data selection statements according to the columns which can be checked; generating a condition tree for limiting the query range according to the user dimension authority, the user column 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 column 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 column authority and the table authority information; and reversing after backtracking to the authority root node based on the authority child node, and 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 receiving the altered library-table-field metadata, comparing the altered library-table-field metadata with the original library-table-field metadata; when the comparison determines that the updating exists, updating the view corresponding to the updated database-table-field metadata; when the comparison determines that new addition exists, creating a view corresponding to the metadata of the newly added library-table-field; and when the comparison determines that deletion exists, deleting the view corresponding to the deleted database-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 original table authority information is compared and determined to have updated sensitive information, updating the sensitive information in a data table corresponding to the sensitive information and updating a view of the data table; and when the original table authority information is compared and determined to have the newly added 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 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 a user corresponding to the updated user authority 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, a new user and a corresponding view are created according to the user permission information; if the user exists, the updated user authority information is compared with the original user authority information; when the comparison confirms that the user authority information of the existing user is changed, the original user authority of the existing user is updated, and the view of the existing user is created again according to the updated user authority information; when the comparison result shows that the user authority information of the existing user is newly added, inserting the newly added user authority information into the existing user and re-establishing the view of the existing user; and when the comparison result shows that the user authority information of the existing user is deleted, deleting the view corresponding to the existing user.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by hardware instructions of a computer program, which can be stored in a non-volatile computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. Any reference to memory, storage, database or other medium used in the embodiments provided herein can include at least one of non-volatile and volatile memory. Non-volatile Memory may include Read-Only Memory (ROM), magnetic tape, floppy disk, flash Memory, optical storage, or the like. Volatile Memory can include Random Access Memory (RAM) or external cache Memory. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM), among others.
The technical features of the above embodiments can be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the above embodiments are not described, but should be considered as the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the invention. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present patent shall be subject to the appended claims.
Claims (10)
1. An ad hoc query method, comprising:
receiving a query statement of a user, analyzing the query statement, and acquiring a table name of a queried data table;
determining a matching view of the queried data table from views belonging to the user; the views belonging to the user are generated according to data information and user information, and the views are respectively matched with different data tables;
replacing the table name in the query statement with the view name of the matched view to obtain an updated query statement;
and executing the updated query statement, querying to obtain the matched view, and returning the matched view to the user.
2. The method of claim 1, wherein the data information includes library-table-field metadata and field sensitivity levels; the user information comprises user column authority, table authority information, user dimension authority and user sensitivity level; generating a view according to the data information and the user information, comprising:
determining columns which can be checked by a user according to the database-table-field metadata, the field sensitivity level, the user column permission and the user sensitivity level, and generating data selection statements according to the columns which can be checked;
generating a condition tree for limiting the query range according to the user dimension authority, the user column authority and the table authority information;
and acquiring metadata of each data table in a database according to the data selection statement and the condition tree, and creating a view according to the metadata.
3. The method of claim 2, wherein 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 column authority and the table authority information to generate a condition tree for limiting the query range.
4. The method of claim 2, wherein 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 child nodes according to the user dimension authority, the user column authority and the table authority information;
and reversely turning after the permission child node backtracks to the permission root node, and generating a condition tree for limiting the query range.
5. The method according to claim 1 or 2, characterized in that the method further comprises:
when receiving the altered library-table-field metadata, comparing the altered library-table-field metadata with the original library-table-field metadata;
when the comparison determines that the updating exists, updating the view corresponding to the updated database-table-field metadata;
when the comparison determines that new addition exists, creating a view corresponding to the metadata of the newly added library-table-field;
and when the comparison determines that deletion exists, deleting the view corresponding to the deleted database-table-field metadata.
6. The method according to claim 1 or 2, characterized in that 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 original table authority information is compared and determined to have updated sensitive information, updating the sensitive information in a data table corresponding to the sensitive information and updating a view of the data table;
and when the original table authority information is compared and determined to have newly added sensitive information, inserting the newly added sensitive information into a data table corresponding to the sensitive information and updating the view of the data table.
7. The method according to claim 1 or 2, characterized in that the method further comprises:
when the user authority information in the data information is updated, judging whether a user corresponding to the updated user authority 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, a new user and a corresponding view are created according to the user permission information;
if the user exists, the updated user authority information is compared with the original user authority information;
when the comparison is determined 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 result shows that the user authority information of the existing user is newly added, inserting the newly added user authority information into the existing user and re-establishing the view of the existing user;
and when the comparison is determined to delete the user authority information of the existing user, deleting the view corresponding to the existing user.
8. An ad hoc query 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 determination module for determining a matching view of the lookup data table from views belonging to the user; the views belonging to the user are generated according to data information and user information, and the views are respectively matched with different data tables;
a replacing module, configured to replace the table name in the query statement with the view name of the matching view, so as to obtain an updated query statement;
and the query module is used for executing the updated query statement, querying and acquiring the matched view and returning the matched view to the user.
9. A computer device comprising a memory and a processor, the memory storing a computer program, characterized in that the processor, when executing the computer program, implements the steps of the method of any of claims 1 to 7.
10. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method of any one 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 true CN112416966A (en) | 2021-02-26 |
| CN112416966B 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) |
Citations (14)
| 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 |
| US20100262625A1 (en) * | 2009-04-08 | 2010-10-14 | Glenn Robert Pittenger | Method and system for fine-granularity access control for database entities |
| 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 |
| US20140380051A1 (en) * | 2013-06-21 | 2014-12-25 | International Business Machines Corporation | Secure data access using sql query rewrites |
| 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 |
-
2020
- 2020-12-11 CN CN202011439737.5A patent/CN112416966B/en active Active
Patent Citations (14)
| 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 |
| US20100262625A1 (en) * | 2009-04-08 | 2010-10-14 | Glenn Robert Pittenger | Method and system for fine-granularity access control for database entities |
| 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 |
| US20140380051A1 (en) * | 2013-06-21 | 2014-12-25 | International Business Machines Corporation | Secure data access using sql query rewrites |
| 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 |
|---|
| MEI LI 等: "Research of a QPC-based heterogeneous database query method", 《2010 3RD INTERNATIONAL CONFERENCE ON ADVANCED COMPUTER THEORY AND ENGINEERING(ICACTE)》, pages 5 - 347 * |
| 赵芳: "提高计算机数据库安全保密水平的技术措施", 《信息与电脑(理论版)》, no. 24, pages 185 - 186 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112416966B (en) | 2024-01-26 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107463632B (en) | Distributed NewSQL database system and data query method | |
| US11841963B2 (en) | Data aggregation system for enabling query operations on restricted data that originates from multiple independent multiple sources | |
| US8924373B2 (en) | Query plans with parameter markers in place of object identifiers | |
| CN109582831B (en) | Graph database management system supporting unstructured data storage and query | |
| CN106407360B (en) | Data processing method and device | |
| US20160103858A1 (en) | Data management system comprising a trie data structure, integrated circuits and methods therefor | |
| CN105989015B (en) | Database capacity expansion method and device and method and device for accessing database | |
| CN112434027A (en) | Indexing method and device for multi-dimensional data, computer equipment and storage medium | |
| US20130151561A1 (en) | Fuzzy full text search | |
| CN113094387A (en) | Data query method and device, electronic equipment and machine-readable storage medium | |
| US20150120697A1 (en) | System and method for analysis of a database proxy | |
| CN117033424A (en) | Query optimization method and device for slow SQL (structured query language) statement and computer equipment | |
| CN109656947B (en) | Data query method and device, computer equipment and storage medium | |
| CN109213775B (en) | Search method, search device, computer equipment and storage medium | |
| CN114356968A (en) | Query statement generation method and device, computer equipment and storage medium | |
| US20240037264A1 (en) | Systems and methods for providing network-based permissioning using security node hash identifiers | |
| CN111125216B (en) | Method and device for importing data into Phoenix | |
| US20090171921A1 (en) | Accelerating Queries Based on Exact Knowledge of Specific Rows Satisfying Local Conditions | |
| CN112416966A (en) | Ad hoc query method, apparatus, computer device and storage medium | |
| CN114048219A (en) | Graph database updating method and device | |
| CN114238334A (en) | Heterogeneous data encoding method and device, heterogeneous data decoding method and device, computer equipment and storage medium | |
| CN115114325A (en) | Data query method and device, electronic equipment and storage medium | |
| CN113821514A (en) | Data splitting method and device, electronic equipment and readable storage medium | |
| CN113868138A (en) | Method, system, equipment and storage medium for acquiring test data | |
| CN112579705A (en) | Metadata acquisition method and device, computer equipment and storage medium |
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 |