Rule-based query control

Systems for fine-grained control of query execution are disclosed. The system receives and stores access control rules to be applied to queries. The system applies access control rules to a received query to determine one or more actions corresponding to the received query. Based on the access control rules, the system can allow execution of the query without conditions, allow execution of the query under a set of conditions, or prevent execution of the query.

TECHNICAL FIELD

The present disclosure relates to database management, and, more specifically, to controlling processing of database queries.

BACKGROUND

Query languages are used to extract information from databases and information systems. Using a query language, such as the Structured Query Language (SQL), users can retrieve, manipulate, and transform data in relational databases. A relational database is a collection of related tables with a fixed number of named columns and any number of rows of data. Queries are textual statements requesting retrieval of a particular set of data from relational databases.

Database management involves controlling access of information in databases by queries to maintain security, performance, and other operational parameters. Some access control techniques place few, if any, restrictions on queries. However, the techniques may only offer administrators high level control over the information accessed by the queries. For example, access control policies may restrict users' connection to particular databases and information users can access. Some other access control techniques give administrators full control over queries made to databases, but allow little or no flexibility for users to construct queries tailored to the users' individual purposes. Also, the techniques typically implement restrictions on case-by-case bases. As a result, the techniques can be costly and time consuming to develop and maintain.

The approaches described in this Background section are ones that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art.

DETAILED DESCRIPTION

The embodiments are illustrated by way of example and not by way of limitation in the figures of the accompanying drawings. It should be noted that references to “an” or “one” embodiment in this disclosure are not necessarily to the same embodiment, and they mean at least one.

This Detailed Description section includes the following subsections:A. GENERAL OVERVIEWB. SYSTEM ENVIRONMENTC. SYSTEM ARCHITECTURED. QUERY CONTROL PROCESSE. EXAMPLE EMBODIMENTF. HARDWARE OVERVIEWG. MISCELLANEOUS; EXTENSIONS

A. General Overview

One or more embodiments implement fine-grained control for the execution of queries. The system receives and stores access control rules to be applied to queries. The access control rules may include user-defined rules that are different from and in addition to a set of validation rules. The system applies the access control rules to a received query to determine one or more actions corresponding to the received query. Based on the access control rules, the system may, for example, allow execution of the query without conditions, allow execution of the query under a limiting set of conditions (e.g., time or resource limits), or prevent execution of the query. Furthermore, the system may present warnings and/or information in relation to the received query. The system may reject a request to execute a query, which is otherwise valid (e.g., authorized for a requesting user and executable on a database), based on the application of stored rules to the query.

While this General Overview subsection describes various example embodiments, it should be understood that one or more embodiments described in this Specification or recited in the claims may not be included in this subsection.

B. System Environment

FIG.1shows a system block diagram illustrating an example of a computing environment100for implementing systems and processes in accordance with one or more embodiments. The computing environment100includes one or more administrator devices105, one or more user devices107, a server115, and a database system120communicatively connected, directly or indirectly via one or more communication links117. The communication links117can be wired and/or wireless information channels, such as the Internet, an intranet, an Ethernet network, a wireline network, a wireless network, a mobile communications network, and/or another communication network.

The database system120can be a system that stores and maintains one or more databases. The database system120can comprise one or more hard disk drives, flash drives, or the like. The database can be any type of database, such as a hierarchical database, network database, or a relational database, such as an SQL database.

The administrator device105and the user device107can be one or more client computing devices allowing users to access and interact with the server115and/or the database system120. For example, the administrator device105and the user device107can be personal computers, workstations, servers, mobile devices, mobile phones, tablet devices, and/or other processing devices capable of implementing and/or executing server processes, software, applications, etc. The administrator device105and the user device107can include one or more processors that execute software or other computer-readable instructions and may include a memory to store the software, other computer-readable instructions, and data. The administrator device105and the user device107can also include a communication device to communicate with the server115and the database system120via the communication links117. Additionally, the administrator device105and the user device107can generate a computer-user interface enabling a user to interact with the server115and the database system120using input/output devices. For example, by way of the computer-user interface, a user of the user device107can connect to the database system120via the server115to code and submit a query151for accessing data, such as tables and records, maintained by the database system120.

The server115can be one or more computing devices that manage communication, access, and operations among the administrator device105, the user device107, and the database system120. One or more embodiments of the server115include a validation library125, a rule configurator127, a rules library129, a query validator131, a query processor133, a rules engine135, and a query executor137.

The validation library125can store validation rules152for one or more users or clients of the server115and/or the database system120. The validation rules152can specify users that may access the database system120, circumstances under which the users can access the database system120, and information in the database system120the users are authorized to access. For example, the validation rules152may restrict access to some or all information stored in the database system120based on a users' and/or a clients' authorization, role, competence, authority, obligation, conflict-of-interest, and the like.

The rule configurator127can be an application or module for configuring rules154for controlling query execution in the database system120. One or more embodiments can receive inputs from a user of the administrator device150including rules154for controlling retrieval of information from the database system120. For example, the rule configurator127can receive the rules154constructed via inputs to a computer-user interface or via a predefined library recorded on storage device. The rules154can be applied based on the content and/or characteristics of a query151. The rules154can comprise criteria and actions. An individual rule can define a mapping between a one or more criteria and one or more actions. The criteria can be based on syntax, semantics, operations, target data, and computing resources of a query151. Example criteria can include:(1) query semantics, such as QUERY USING JOINS(2) query plan, such as: QUERY DOING A FULL TABLE SCAN(3) target data of a query, such as: QUERY USING COLUMN A(4) past performance of same or similar queries, such as: QUERY USING A PARTICULAR OPERATION ON A TABLE HAVING AVERAGE RESPONSE TIME OF MORE THAN 10 SECONDS(5) Current status of a system executing a query, such as: NUMBER OF PARALLEL QUERY EXECUTIONS USING JOINS

Actions can specify one or more operations to perform based on a corresponding query matching a criterion. Actions can function to, for example: prevent execution, limit execution, limit resources consumed, limit results returned, and generate alerts. Example actions can include:(1) Reject execution of a query(2) Execute a query(3) Execute a query with a specific time out(4) Execute a query with a limit on the total number of queries that can be executed(5) Lock out the user(6) Trigger a notification, such as an alarm, alert, or the like

The rule configurator127can also generate mappings between one or more criteria and one or more corresponding actions, and store the mapping in the mapping in, for example, the rules library129. An example rule mapping a criterion to an action can be, for example:IF QUERY_PATTERN USING JOINS:DENY QUERY_EXECUTION

The query validator131can be a software application or module that validates database queries, such as the query151based on user credentials159received from the user device107and validation rules152stored in the validation library125. The query validator131can determine which, if any, information in the database system120a user or client is permitted to and/or prohibited from accessing. For example, the validation library125can include information identifying particular tables, rows, or records a user is forbidden to access. In response to determining that the query151requests unauthorized information, the query validator131can invalidate the query. On the other hand, in response to determining that the query151is authorized, the query validator131can validate the query151and pass the valid query153to the query processor133.

The query processor133can be a software application or module that determines content of the query153, characteristics of the query153, characteristics of the user requesting the query153, and characteristics of the database system120. Content of the query153can include operations contained in the query and data requested by the query153, such as: a quantity and types of joins in the query; a quantity and types of functions in the query; a quantity and types of conditions in the query; a quantity and types of operations in the query; a quantity and types of clauses in the query; a quantity and the types of keywords in the query; a quantity, nested level, and types of sub-queries in the query; a quantity and types of columns used in the query; querying a time series table; and inclusion of time predicates. One or more embodiments of the system determine the contents by parsing the query153and creating a parse tree using a query language grammar (e.g., SQL syntax). For example, using a parser, the system can convert the query into a string of elements (e.g., tokens indicating database operations and data), and using a grammar (e.g., grammar211below), determine the syntax and semantic patterns of the elements.

The characteristics of the query can include information describing execution of the query153, including time, resource consumption, result size, and the like. For example, the characteristics can include information from one or more query plans, including instructions for executing the query (e.g., database instructions163), an estimated resource cost for executing the query (e.g., startup cost and total cost), an amount of information included in the query result (e.g., quantity of rows, average bytes of information per row, and total number of bytes of information). The characteristics of the query153can also include query history information such as an average time taken in the past for a particular criterion. Example characteristics of the query153can be a number of rows in the target of the query, a number of rows accessed in the target of the query, a number of rows to be returned by the query, an amount of data to be processed for the query, the query causing a full table scan, a query response time for execution of the query, resource usage for execution of the query, and a time for execution of the query.

The characteristics of the user can include usage information and resource consumption by the user. For example, characteristics of the user can include an average time taken for the user's queries, an average size of a results retrieved by the user, and an average amount of computing resources consumed by the user over one or more time frames (e.g., hour, day, month, quarter, or year).

The characteristics of the system can include computing resources currently available or predicted to be available for executing queries. Currently available resources can be determined using characteristics of a current state of the system obtained from a resource management tool. Predicted resources can be determined using historical characteristics of past states of the system from the resource management tool, which can be used to predict a future state of the system. For example, the characteristics can include usage metrics, health metrics, query characteristics, previous query execution plan characteristics, query characteristics of individual users, and available computing resources. Computing resources can include, for example, resources available, resources occupied, quantity of queries currently being executed, and current execution time (e.g., average query execution time or maximum query execution time). For example, the system can determine a number of parallel queries of a particular criterion getting executed at the same time.

The rules engine135can be a software application or module that determines whether to reject, execute, or conditionally execute queries by applying the rules154stored by the rules library129to the query content and characteristics155determined by the query processor133. One or more embodiments of the rules engine135apply the rules154to the query content and characteristics155determined by the query processor133. For example, the rules library129can include a rule specifying that execution of queries including join operations should be rejected. Based on the rules, the rules engine135can perform actions defined by the rules. If a rule154specifies the validated query153should be rejected, then the rules engine135can refrain from forwarding the query153to the query executor137and, instead, communicate a query rejection157to the user device indicating the rejection of the query153, the reasons for the rejection, and any actions taken in association with the rejection, such a locking out the user and alerting administrators. Additionally, some embodiments can communicate the rejection to the administrator device105. On the other hand, if the rules engine135determines the query153is authorized without conditions, the rules engine135can communicate a query authorization158to the query executor137. Also, if the rules engine135determines the query153is authorized with conditions, the query authorization158can include information specifying the conditions (e.g., a maximum quantity of results and maximum query execution time).

The query executor137can be a software application or module that controls the storage, organization, and retrieval of data from database system120based on query153. The query executor137can generate database instructions163for retrieving database records165from the database system120to communicate a query result167for the user device107. One or more embodiments of the query executor137generate the query instructions163including conditions or restrictions in accordance with the rules applied by the rules engine135. Based on the conditions or restrictions, the query result167can be a limited set of records in comparison to a query result167generated without conditions or restrictions. For example, based on the restrictions and conditions, the query result167can be limited to 1000 records; whereas an unconditional or unrestricted result could include a substantially unlimited quantity of records.

FIG.1illustrates the server115including the rule configurator127, the validation library125, the rules library129, the query validator131, the query processor133, the rules engine135, and the query executor137. It is understood that one or more of the components of the server115can be combined. Additionally, it is understood that one or more of the components can be included in or combined with a system other than the server115. For example, one or more embodiments may combine the components of the server into the database system120.

C. System Architecture

FIG.2shows a system block diagram illustrating an example of a server115in accordance with one or more embodiments. The server115can be the same or similar to that described above. The server115includes hardware and software that perform the processes and functions disclosed herein.

Embodiments of the server115can include a processor203, one or more memory devices205, and a storage device209. The processor203can be one or more general-purpose processors, special-purpose processors, or other programmable data processing apparatuses providing the functionality and operations detailed herein. The memory device205and storage device209can be operatively and/or communicatively connected to the processor203. In some implementations, the memory device205and/or the storage device209includes one or more types of memory structures. For example, the memory structures can include random access memory (RAMs) units, read only memory (ROMs), flash memory (e.g., solid state drives (SSDs)), electrically erasable/programmable read only memory (EEPROMs), etc. It should be appreciated that communication between the memory device205, the storage device209, and the processor203or another controller, encompasses the processor or controller accessing the memory device205and/or the storage device209, exchanging data with the memory device205and/or the storage device209(e.g., reading/writing data to the memory device205), or storing data to the memory device205and/or the storage device209.

The storage device209can store a validation library125and rule library129, which can be the same or similar to those previously described above. The storage device209can also store a grammar211and resource information213. The resource information213can be a log of information of current and past queries executed on a database (e.g., database system120). The statistics can include information indicating types of and quantities of queries executed on the database system120by the system and by particular users. The statistics can also include information quantifying resources consumed by the queries and results generated by the queries.

The processor203can also execute computer-readable program instructions of a rule configurator127, a query validator131, a query processor133, a rule engine135, and a query executor137, all of which can be the same or similar to those previously describe above. It is understood that functionality of one or more of the rule configurator127, the query validator131, the query processor133, the rule engine135, and the query executor137can be combined into a common software module and/or divided between additional modules.

It is noted that the server115can comprise any general-purpose computing article of manufacture capable of executing computer program instructions installed thereon (e.g., a personal computer, server, etc.). However, the server115is only representative of various possible equivalent-computing devices that can perform the processes described herein. To this extent, in embodiments, the functionality provided by the server115can be any combination of general and/or specific purpose hardware and/or computer program instructions. In each embodiment, the program instructions and hardware can be created using standard programming and engineering techniques, respectively.

The components illustrated inFIG.2may be implemented in software and/or hardware. Each component may be distributed over multiple applications and/or machines. Multiple components may be combined into one application and/or machine. Operations described with respect to one component may instead be performed by another component.

D. Query Control Process

The flow diagrams inFIGS.3A-3Cillustrate functionality and operations of systems, devices, processes, and computer program products according to various implementations of the present disclosure. Each block inFIGS.3A-3Ccan represent a module, segment, or portion of program instructions, which includes one or more computer executable instructions for implementing the illustrated functions and operations. In some implementations, the functions and/or operations illustrated in a particular block of the flow diagrams can occur out of the order shown inFIGS.3A-3C. For example, two blocks shown in succession can be executed substantially concurrently, or the blocks can sometimes be executed in the reverse order, depending upon the functionality involved. Additionally, in some implementations, the blocks of the flow diagrams can be rearranged in different orders. Further, in some implementations, the flow diagram can include fewer blocks or additional blocks. It is also noted that each block of the flow diagrams and combinations of blocks in the flow diagrams can be implemented by special-purpose hardware-based systems that perform the specified functions or acts, or combinations of special-purpose hardware and computer instructions.

Referring toFIG.3A, at block305, a system (e.g., server115executing rule configurator127) configures rules (e.g., rules154) for controlling execution of queries. Configuring the rules can include, at block307, define criteria for controlling queries from a user. For example, an administrator (via, e.g., administrator device105) can input the rules using a code editor application, retrieve the rules from a preestablished library, or upload a file containing predefined rules. As previously described above, rules can comprise criteria mapped to actions. The criteria can identify include syntax and semantic patterns of queries; query plans, past history of queries (e.g., past execution history of same criteria, user history of query execution), system status (e.g., system resource usage, system time of execution). For example, rules can specify a number of joins in a query, a level of nested sub-queries, a number of rows in a table, whether the query includes a rank operation on a particular column of a table, a time predicate for a time series table, an average query response time for a particular table, and an average query response time for a view associated with a query.

Configuring the rules can also include, at block311, mapping one or more actions to the criteria defined at block307. As previously described above, the actions can include operations controlling the system to reject queries, execute queries, or execute queries with conditions and/or restrictions. The actions can be implemented before, during, or after execution of the queries. For example, actions can include: a time limit for execution of a query, a set of resources that may be accessed by a query, a number of queries that may be concurrently executed with another query, computing resources involved in executing the query, and computing resources available for executing the query. Additionally, one or more embodiments can implement different rules before, during, or after query execution. For example, a first rule can set a time limit (e.g., 10 seconds) before execution of a query, and a second rule can modify the time limit during the execution.

The system can obtain the actions in a same or similar manner as the criteria. Some embodiments can receive a table mapping the criteria with one or more corresponding actions as a table file. Some embodiments can be an interactive graphic user interface for constructing rules, associating the criteria with one or more actions, and checking the rules for syntactic errors. Configuring the rules can also include, at block315, stores the mapping between the criteria and corresponding actions. For example, the system can store and maintain the mapping in a library (e.g., rules library129.)

At block325, the system can receive a query (e.g., query151) from a user of a user device (e.g., user device107). For example, a developer can input a query in a user interface of a code editor and submit the query to the system for execution. At block329, the system (e.g., executing query validator131) can determine whether the query received at block325is valid. One or more embodiments validate the query using one or more access rules (e.g., validation rules152stored in the validation library125) restrict access to some or all information stored in the database system120. For example, the system can compare user credentials (e.g., credentials159, such as authorization, role, competence, authority, obligation, and conflict-of-interest) to the access rules. If the query is not valid (e.g., block327is “No”), then at block331the system can reject the query and, at block333, present information indicating the rejection of the query via the user device. For example, via a graphic computer-user interface, the system can generate a response to the query indicating that the user is not authorized to execute the query or to access data requested by the query. On the other hand, if the system determines that the received query is valid (e.g., block327is “Yes”), then the process300proceeds to block345onFIG.3B, as indicated by off-page connector “A.”

At block345, the system (e.g., executing query processor133) can process the query validated at block327. Processing the query can include determining content of the query, characteristics of the query, characteristics of the user, and characteristics of the system. Some embodiments receive the request to process the query from the user via the computer-user interface. Other embodiments automatically execute the query based on successful validation at block327. Processing the query can include, at block347, determining content of the query, such as the syntax and sematic patterns of the query. For example, using a parser, the system can convert the query into a string of elements (e.g., tokens) and using a grammar (e.g., grammar211), determine syntax and semantic patterns of the elements.

Processing the query can also include, at block349, determining characteristics of the query, such as resources involved in executing the query. For example, using a query optimizer, the system can determine one or more query plans for executing the query. The query plan can include an estimated resource cost for executing the query (e.g., startup cost and total cost), an amount of information included in the query result (e.g., quantity of rows, average bytes of information per row, and total number of bytes of information). Query characteristics can also include historical information logged from past queries of the same type. For example, the historical information can include execution time, resource consumption, and result size.

Processing the query can further include, at block351, determining characteristics of the user requesting the query, such as user history information. User history can include usage information and resource consumption information consumed by queries of the user within one or more time frames. One or more embodiments can maintain a log of each query executed by the user within the last hour, day, week, or the like, along with corresponding quantities of time, computing resources, and data consumed by the queries.

Processing the query can additionally include, at block353, determining characteristics of the system, such as system resources. Determining system resources can include, for example, resources available, resources occupied, quantity of queries currently being executed, and current execution time (e.g., average query execution time, maximum query execution time). One or more embodiments determine the system resources information based on the received query. For example, the system can determine a number of parallel queries of a particular criterion executed at the same time. The resource information can include current resource availability. Additionally, the resource information can include past resource consumption. For example, the system can retrieve past information of queries including operations and data the same or similar to the query.

At block355, the system (e.g., executing rules engine135) can apply one or more of the rules configured at block305to the query validated at block327based on the contents and characteristics determined at block345. Applying the rules can include, at block359, determining whether the query meets one or more of the criteria defined by the rules. If no matching criteria are identified (e.g., block359is “No”), the process300can proceed to block387ofFIG.3C, as indicated by off-page connector “B.” If the system determines one or more matching criteria at block359, then at block363, the system can determine actions mapped to the one or more matching criteria.

Referring toFIG.3C, as indicated by off page connector “C,” at block375the system can aggregate any actions determined at block363. Aggregating the actions can include, at block389, determining whether the one or more actions specify the valid query should be rejected, executed without restrictions, or executed with restrictions. The determination at block389can be based on the most restrictive option included in the one or more actions determined for the query. If any action of the one or more actions determined at block361indicates the query is rejected (e.g., block389is “reject”), then the process300proceeds to block377, as described below. If none of the one or more actions indicates the query is rejected, but at least one indicates the query is restricted (e.g., block389is “restrict”), then the process proceeds to block381, as described below. If none of the one or more actions indicates the query is rejected or restricted or all of the actions indicate the query should executed (e.g., block389is “execute”), then the process proceeds to block381, as described below.

For example, if a first action corresponding to a first criterion matching the query indicates the query can be executed, a second action corresponding to a second criterion matching the query indicates the query should be restricted, and a third action corresponding to a third criterion matching the query indicates the query should be rejected, then the most restrictive option would be to reject the query. Accordingly, at block389, the system can reject the query and proceed to block377. In another example, if a first action corresponding to a first criterion matching the query indicates the query can be executed, a second action corresponding to a second criterion matching the query indicates the query should be restricted, then the most restrictive option would be to restrict the query. Accordingly, at block389, the system can restrict the query and proceed to block377.

At block377, the system can reject the execution of the query based on the determination at block389. Rejecting execution can prevent generation or execution of any instructions (e.g., database instructions163) or retrieving information from the database. Rejecting execution can also prevent consumption of substantially any computing resources of the system or the database that would be involved in executing the query. At block379, the system can display an indication of the query rejection (e.g., query rejection157) to the user (e.g., user device107) via a computer-ser interface (e.g., a user interface). The indication can also include a description of the reasons for the rejection based on the corresponding action.

At block381, the system can execute the query with each of the restrictions indicated by the actions determined at block361. As previously described, the restrictions can specify a time limit for execution of the query, a set of resources that may be accessed by the query, and a number of queries that may be concurrently executed with the query, computing resources involved in executing the query, and computing resources available for executing the query. At block381, the system can retrieve information from the database in accordance with the restrictions identified at block389. At block385, the system can present the query results (e.g., query results167) to the user via the computer-ser interface), along with an indication describing the restrictions imposed on the results. For example, the system can indicate that execution of the query was limited to 30 seconds and 1,000 results.

At block387, the system can execute the query without restrictions or conditions. At block389, the system can retrieve information from the database. And, at block385, the system can present the query results to the user.

FIG.4illustrates an example data file400specifying example criteria403,405, and407, and example rules409,411,413, and415. Criterion403identifies “Basic queries using select clause, from clause, where clause and group by clause only once in their queries.” Criterion405identifies “Queries using basic aggregations.” Criterion407identifies “Queries using joins.”

Rule409, “Allow execution for basic queries using basic aggregations,” maps the action “allow query execution” if the query matches criteria403and405. Rule411, “Force a special time out for queries using joins,” maps the action “set query time out as 30 seconds” if the query matches criterion407. Rule413, “Set a special query execution count for queries using joins,” maps the action “allow query execution” if the query matched criterion403and the estimated query count will be less than 1000 within 5 minutes. The system can determine the estimated query count and time constraints based on determinations of the characteristics of the query and the current system resources. Rule415, “Force a result set count to a maximum of 1000 regardless of data requested in the query,” maps the action “set maximum result size to 1000 records” for any query.

As previously described, in response to a query matching criteria included one of the rules409,411,413, and415, the system performs one or more actions mapped to the matched criterion. For example, a query can state, “SELECT NAME, EMP_NO FROM EMPLOYEES. Based on the content of the query (e.g., determined by query processor133), the system can determine that only the criterion of rule415(“QUERY_PATTERN=ANY”) matches the query. Accordingly, as indicated by rule415, the system can generate and execute database instructions (e.g., database instructions163) restricting the retrieved records (e.g., database records165) based on the action “set maximum result size to 1000 records”.

In another example a query can state, “SELECT NAME, AVG(SALARY) FROM DEPT GROUP BY NAME.” Based on the content of the query, the system can determine that none of the rules409,411,413, and415include criteria matching the query. Notably, the query includes the aggregation “AVG,” which is similar to the aggregations in rule405(e.g., SUM, MIN, MAX). However, as the query does not match criteria of any of the rules409,411,413, and415, the system can reject execution of the query.

In a further example, a query can state, “SELECT LEAVES.LEAVE ID, EMPLOYEES.EMP_NAME, LEAVES.LEAVEDATE FROM LEAVES INNER JOIN EMPLOYEES ON LEAVES.EMP_ID=EMPLOYEES.EMPLOYEE_ID.” Based on the content of the query, the system can determine that the query matches criteria of rules411,413, and415. Accordingly, the system can perform the actions specified by each of the rules411,413, and415, which include “Allow query execution, if not more than 1000 executions have happened in the last 5 minutes,” “Set they query time out to 30 seconds,” and “Limit the result set rows to 1000.”

F. Hardware Overview