PRODUCTION DATABASE UPDATE TOOL
A computing system may obtain code for a query to a database from a first user. In response, the application may automatically perform an operation to determine a number of records of the database that would be accessed by executing the query on the database. The computing system may output, for display to the first user, the number of records of the database that would be accessed by executing the query on the database. The computing system may output, for display to the first user, a prompt for an acknowledgement of the number of records of the database that would be accessed. In response to receiving an indication of the acknowledgement by the first user of the number of records of the database that would be accessed, the computing system may output, for display to a second user, the code for the query for review by the second user.
This disclosure generally relates to databases.
BACKGROUNDProduction databases may include databases that store data that are used in day-to-day operations of organizations. For example, a production database may store data that describe statuses of orders received by an enterprise. In another example, a production database may store data describing policyholder accounts and insurance claims.
Because a production database may store data that are used in day-to-day operations of an organization, the production database may be critical to the mission of the organization. While it may be important to be able to make changes to the production database, it is also important to ensure that data in the production database is not lost or corrupted.
SUMMARYIn general, the disclosure describes techniques for improving operations of a database by filtering out queries that may impact the operation of the database (e.g., by reducing risks of improper queries being executed on databases). As described herein, a computing system may obtain code for a query to a database from a first user. In response, the computing system may automatically perform an operation to determine a number of records of the database that would be accessed by executing the query on the database. The computing system may output, for display to the first user, the number of records of the database that would be accessed by executing the query on the database. The computing system may output, for display to the first user, a prompt for an acknowledgement of the number of records of the database that would be accessed. In response to receiving an indication of the acknowledgement by the first user of the number of records of the database that would be accessed, the computing system may output, for display to a second user, the code for the query for review by the second user.
In one example, this disclosure describes a method comprising: obtaining, by processing circuitry of a computing device and from a first user, code for a query to a database, wherein the code for the query expresses an operation to be performed on the database in response to execution of the query; in response to receiving the code for the query, automatically performing, by the processing circuitry, an operation to determine a number of records of the database that would be accessed by executing the query on the database; outputting, by the processing circuitry and for display to the first user, the number of records of the database that would be accessed by executing the query on the database; outputting, by the processing circuitry and for display to the first user, a prompt for an acknowledgement by the first user of the number of records of the database that would be accessed; receiving, by the processing circuitry and from the first user, an indication of the acknowledgement by the first user of the number of records of the database that would be accessed; in response to receiving the code for the query and the indication of the acknowledgement by the first user of the number of records of the database that would be accessed, outputting, by the processing circuitry and for display to a second user, the code for the query for review by the second user; after outputting the code for the query for review by the second user, obtaining, by the processing circuitry and from the second user, an indication that the second user approves the code for the query; and based on the indication that the second user approves the code for the query, executing, by the processing circuitry, the query on the database.
In another example, this disclosure describes a computing system comprising processing circuitry and a storage system, the processing circuitry configured to: obtain, from a first user, code for a query to a database stored in the storage system, wherein the code for the query expresses an operation to be performed on the database; in response to receiving the code for the query, automatically perform an operation to determine a number of records of the database that would be accessed by executing the query on the database; output, for display to the first user, the number of records of the database that would be accessed by executing the query on the database; output, for display to the first user, a prompt for an acknowledgement by the first user of the number of records of the database that would be accessed; receive, from the first user, an indication of the acknowledgement by the first user of the number of records of the database that would be accessed; in response to receiving the code for the query and the indication of the acknowledgement by the first user of the number of records of the database that would be accessed, output, for display to a second user, the code for the query for review by the second user; after outputting the code for the query for review by the second user, obtain, from the second user, an indication that the second user approves the code for the query; and based on the indication that the second user approves the code for the query, execute the query on the database.
In another example, this disclosure describes a non-transitory, computer-readable medium comprising instructions that, when executed, are configured to cause processing circuitry of a computing device to: obtain, from a first user, code for a query to a database, wherein the code for the query expresses an operation to be performed on the database; in response to receiving the code for the query, automatically perform an operation to determine a number of records of the database that would be accessed by executing the query on the database; output, for display to the first user, the number of records of the database that would be accessed by executing the query on the database; output, for display to the first user, a prompt for an acknowledgement by the first user of the number of records of the database that would be accessed; receive, from the first user, an indication of the acknowledgement by the first user of the number of records of the database that would be accessed; in response to receiving the code for the query and the indication of the acknowledgement by the first user of the number of records of the database that would be accessed, output, for display to a second user, the code for the query for review by the second user; after outputting the code for the query for review by the second user, obtain, from the second user, an indication that the second user approves the code for the query; and based on the indication that the second user approves the code for the query, execute the query on the database.
The details of one or more examples of the techniques of this disclosure are set forth in the accompanying drawings and the description below. Other features, objects, and advantages of the techniques will be apparent from the description and drawings, and from the claims.
Like reference characters refer to like elements throughout the figures and description.
DETAILED DESCRIPTIONProduction databases are central to the operations of many types of organizations. For instance, a production database may be used in the day-to-day operations of an organization to store mission-critical data for an organization. Accordingly, data may be updated frequently in a production database. Because a production database may store mission-critical data, for proper operation of a database, it is important that queries that change data in the production database are correct and accomplish their intended purposes. An improper query, such as badly written query or a maliciously written query, can delete, overwrite, or move data in the production database in a manner that impacts access to the database, which may not be desired by the organization. For example, a minor typographical error in a query may result in the deletion of thousands of records in the production database instead of only a single record in the production database. In another example, seemingly minor defects in a query may result in the exfiltration of data from the production database, even if no data is changed in the production database.
Repairing damage caused by a badly written or maliciously written query may be extremely costly, both in terms of technical worker-hours and in terms of reputational damage. Likewise, it may be impossible to recover exfiltrated data. Furthermore, the occurrence of a data exfiltration event (e.g., a data breach) may result in significant legal damages.
This disclosure describes techniques that may reduce the risk of improper queries being executed against a production database. As described in one example of this disclosure, a query management system (QMS) executed by processing circuitry of a computing system may obtain, from a first user, code for a query to a database. Thus, discussion of activities performed by the QMS may in fact be performed by the processing circuitry. The code for the query expresses an operation to be performed on the database in response to execution of the query. In response to receiving the code for the query, the QMS automatically performs an operation to determine a number of records of the database that would be accessed (e.g., read, modified, deleted, etc.) by executing the query on the database. The QMS outputs, for display to the first user, the number of records of the database that would be accessed by executing the query on the database. Furthermore, in this example, the QMS outputs, for display to the first user, a prompt for an acknowledgement by the first user of the number of records of the database that would be accessed. The QMS may then receive, from the first user, an indication of the acknowledgement by the first user of the number of records of the database that would be accessed.
In this example, responsive to receiving the code for the query and the indication of the acknowledgement by the first user of the number of records of the database that would be accessed, the QMS may output, for display to a second user, the code for the query for review by the second user. After outputting the code for the query for review by the second user, the QMS may obtain, from the second user, an indication that the second user approves the code for the query. After receiving the indication that the second user approves the code for the query, the QMS may receive a request to execute the query on the database. In response to receiving the request to execute the query and based on the indication that the second user approved the code for the query, the QMS may execute the query on the database.
By informing the first user of the number of records of the database that would be accessed by executing the query, the QMS may alert the first user to the fact that execution of the query may result in more records being accessed than the first user may intend. Forcing the first user to acknowledge the number of records that would be accessed by executing the query may force the first user to consider whether the query is correct. The QMS enforces a requirement that two sets of eyes (i.e., the first user and the second user) review the query before the query is executed on the database. Furthermore, by informing the second user of the number of records of the database that would be accessed by executing the query, the QMS may alert the second, reviewing user that execution of the query may result in a suspicious number of records being accessed. For instance, if the second user observes that execution of the query may result in a suspicious number of records being accessed, the second user may be better positioned to investigate whether the query is malicious or contains errors.
Computing system 102 is a system that comprises one or more computing devices, each of which may include one or more processors. For instance, computing system 102 may include one or more mobile devices, server devices, personal computer devices, handheld devices, wireless routers, wireless communication hubs, special-purpose devices, and/or other types of devices. Actions described in this disclosure as being performed by computing system 102 may be performed by one or more of the computing devices of computing system 102, or more specifically, by processors of one or more of the computing devices of computing system 102. The processors may be implemented in circuitry.
Similarly, user devices 104 may include various types of computing devices that include processors. Example types of user devices 104 may include mobile devices, server devices, personal computer devices, handheld devices, wireless routers, wireless communication hubs, special-purpose devices, and/or other types of devices. User devices 104 may communicate with computing system 102 via one or more communication networks (not shown), such as local area networks, wide area networks, the Internet, and so on.
As shown in the example of the
Production database 112 may be implemented in one of a variety of ways. For example, production database 112 may be implemented at least in part as a relational database that comprises a set of tables. In some examples, production database 112 may be implemented at least in part using one or more Online Analytical Processing (OLAP) data cubes.
Production database 112 stores data in the form of records. In some examples where production database 112 is implemented using a set of tables, a record may be an individual row of one of the tables. In other examples where production database 112 is implemented using a set of tables, a record may be an individual cell in a table. In examples where production database 112 is implemented using an OLAP data cube, a record may be an individual cell in the OLAP data cube.
Production database 112 can be queried using queries that conform to a query language, such as Structured Query Language (SQL) or another type of query language. Although the term “query” appears to refer to simply a request to retrieve data from a database, a query may be used to read data from, move data within, delete data from, or otherwise access records in the database. In other words, a query may be used to access the database in a variety of ways, including reading, moving, and deleting data within the database.
QMS 108 may enable users 106 to define, create, maintain and control access to production database 112 and, in some examples, other databases. For instance, QMS 108 may execute queries on production database 112. Users 106 may use user devices 104 to submit queries to QMS 108 for execution on production database 112. In some examples, QMS 108 may interact with a web server (not shown in
As noted elsewhere in this disclosure, execution of improper queries, such as badly written or malicious queries, on production database 112 may have serious negative consequences on the operation and/or accessibility of database 112. For instance, execution of improper queries on production database 112 may cause data in production database 112 to be overwritten, deleted, improperly moved, or exfiltrated, each of which may result in disruption of operations of an organization that uses production database 112. For instance, in one example, a production database used by a health insurance provider may store data regarding the health insurance policies of subscribers. In this example, execution of a badly written or malicious query on production database 112 may result in subscribers or healthcare providers being unable to obtain reimbursement for healthcare services. In another example, an improper query may access so many records and/or perform an operation that is so complex that a performance and/or responsiveness level of computing system 102 is diminished.
Therefore, it may be important for the security and integrity of production database 112 to reduce the risk of improper queries (e.g., badly written queries or malicious queries) being executed on production database 112. In accordance with one or more techniques of this disclosure, QMS 108 may implement a set of processes that may help reduce the risk of improper queries being executed on production database 112 and/or help mitigate the impact of execution of an improper query being executed on production database 112. Thus, the techniques of this disclosure may improve the reliability and efficiency of computing system 102.
In the example of
In some examples, QMS 108 may automatically validate a syntax of the code for the query and output, for display to the first user, an indication of whether the syntax of the code for the query is valid. In some examples, to help a reviewer efficiently review submitted queries, QMS 108 may validate the syntax of the code for the query prior to outputting, for display to the second user, the code for the query for review by the second user. In some examples, when QMS 108 outputs data or an interface for display, QMS 108 may interact with a web server or other type of application that sends data (e.g., webpage data) to user devices 104 that user devices 104 may use to present the data or interfaces. In other examples, when QMS 108 outputs data or an interface for display, the data or interface may be presented on a display screen connected to a computing device of computing system 102.
In response to receiving the code for the query, QMS 108 automatically performs an operation to determine a number of records of production database 112 that would be accessed (e.g., read, modified, deleted, etc.) by executing the query on production database 112 (202). QMS 108 may perform the operation to determine the number of records of production database 112 that would be accessed by executing the query on production database 112 by partially executing the query in a way that does not alter production database 112 or actually return data from production database 112.
As an example, the data in production database 112 may be stored in a form of a plurality of interconnected linked lists. The code of the query may indicate through which linked lists QMS 108 is to traverse to access, update, delete, etc. the data. In some examples, QMS 108 may count the links in the linked list to determine how many records would need to be accessed. As another example, the code for the query may identify a plurality of data that is to be accessed in a batch (e.g., all patients with particular insurance claim). In this example, QMS 108 may access metadata of data stored in the production database 112 that indicates whether the data is associated with a particular insurance claim. QMS 108 may maintain a counter that increments based on the information available from the metadata. There may be other ways in which QMS 108 may partially execute the query, and the above are just some non-limiting examples.
QMS 108 may output, for display to the first user, the number of records of production database 112 that would be accessed by executing the query on production database 112 (204). Although this disclosure describes QMS 108 as outputting data, such as the number of records of production database 112 that would be accessed by executing the query on production database 112, QMS 108 may, in some examples, interact with one or more other applications, such as a web server, to output data to users, such as the first user. QMS 108 interacting with these other applications is encompassed as part of the QMS 108 outputting data for display to a user.
Furthermore, in the example of
In the example of
Additionally, in the example of
After outputting the code for the query for review by the second user, QMS 108 may obtain, from the second user, an indication of whether the second user approves the code for the query (212). For instance, QMS 108 may receive an indication of user input indicating that the second user selected an approve button or a reject button of a query approval confirmation interface.
In response to receiving an indication that the second user does not approve the code for the query (“NO” branch of 212), QMS 108 may return the query to the first user and does not add the query to a set of queries that are executable on the database (214). For instance, QMS 108 may send a notification message to the first user indicating that the code for the query was not approved.
However, if QMS 108 received an indication that the second user approved the code for the query (“YES” branch of 212), QMS 108 may subsequently receive a request to execute the query on production database 112 (216).
Users may use submit query tab 302, approve query tab 304, execute query tab 306, reporting tab 308, and generate rollback tab 310 to navigate among user interfaces for different database-related activities. In the example of
In some examples, specific users may not be able to use each of submit query tab 302, approve query tab 304, execute query tab 306, reporting tab 308, and generate rollback tab 310. For instance, a first user who submits a query might not be able to access a user interface associated with the approve query activity.
In the example of
Furthermore, in the example of
Database URL box 324 may accept user input or selection of a URL of a database on which a query is to be executed. For instance, database URL box 324 may accept user input or selection of a URL of production database 112. Database query box 326 may accept user input of the code of a query to be executed on the database indicated by the URL entered in database URL box 324.
Rollback query box 328 may accept user input of code of a rollback query. The rollback query is a query that rolls back one or more changes to the database caused by the query entered in database query box 326. In accordance with some techniques of this disclosure, it may be a requirement that the user provide the rollback query at the time of submission of the query entered in database query box 326. Thus, the rollback query may be immediately available for use in rolling back changes to the database that were caused by the query if the query turns out to be improper. As described in detail elsewhere in this disclosure, the user may use a user interface associated with the generate rollback activity to generate the rollback query.
In the example of
In some examples where a first query (e.g., a query entered in query box 326) expresses a first operation to be performed on production database 112 and the first operation is configured to alter at least one record stored by production database 112 from a first state to a second state, QMS 108 may, prior to outputting the code for the first query for review by the second user, output to the first user, a prompt for a second query for rolling back changes to production database 112 caused by execution of the first query. The second query may express a second operation to be performed on production database 112. The second operation is configured to revert the at least one record from the second state back to the first state. In this example, QMS 108 may receive, from the first user, the second code for rolling back changes to the database caused by execution of the first code.
As shown in the example of
The query number box 352 indicates a number of queries that are to be executed as a package. That is, in some examples, QMS 108 may receive a compound query that includes two or more separate queries that are to be executed in sequence. For instance, one of the queries may be read data from a set of records and a second one of the queries may be to modify one of those records. Type box 354 indicates types of the queries. Example types of queries may be read queries, delete queries, modify queries, and so on.
The total number of deleted records box 356 indicates a number records that would be deleted by executing the query on the database. The total number of modified records box 358 indicates a number of records that would be modified by executing the query on the database. Thus, query submission confirmation interface 350 may serve as a prompt for an acknowledgement by the user of the number of records of the database that would be accessed (e.g., deleted or modified) by executing the query on the database. QMS 108 may receive an indication of an acknowledgement by the first user of the number of records of the database that would be accessed when QMS 108 receives an indication of user selection of submit button 360. QMS 108 may close query submission confirmation interface 350 without submitting the query for approval in response to receiving an indication of user selection of cancel button 362.
Furthermore, in the example of
Specifically, in the example of
Thus, in some examples of this disclosure, the operation, that is performed when the query is submitted for approval, to determine the number of records of the database that would be accessed by executing the query on the database may be a first operation to determine a first number of records of the database that would be accessed by executing the query on the database. In response to receiving the request to execute the query and prior to executing the query on the database, QMS 108 may automatically perform a second operation to determine a second number of records of the database that would be accessed by executing the query on the database. QMS 108 may then output, for display, the second number of records of the database that would be accessed by executing the query on the database.
With respect to the example of
Additionally, reporting interface 600 includes a “from date” search box 626, a “to date” search box 628, a submitted by search box 630, an application (app) search box 632, and a ticket number search box 634. Reporting interface 600 also includes a search button 636 and an export button 638. A user may enter search criteria in one or more of “from date” search box 626, “to date” search box 628, submitted by search box 630, application search box 632, or ticket number search box 634. QMS 108 may execute a search for applicable queries based on the search criteria in response to receiving an indication of user input to select search button 636. QMS 108 may export a table of queries (e.g., one or more rows of the table shown in reporting interface 600) to a spreadsheet file in response to receiving an indication of user input to select export button 638.
In some examples, different types of users may only be able to see data regarding specific types of queries in reporting interface 600. For example, users having an administrator role may be able to see data regarding any queries in reporting interface 600. A user having an approver role may only be able to see data regarding queries approved or submitted by the user. A user having a normal user role may only be able to see data regarding queries submitted by the user.
Rollback query box 706 contains code of the rollback query. In some examples, a user 106 generates the rollback query and populates rollback query box 706 with the code of the rollback query. In some examples, user 106 may provide the code for the rollback query in the form of an input file. As an example, rollback interface 700 may provide a directory navigation interface that user 106 may use to upload the input file containing the code for the rollback query. In other examples, rollback interface 700 implements copy, paste, and cut commands that allows user 106 to “paste” the code for the rollback query into rollback query box 706.
In other examples, and as described in more detail below, QMS 108 may populate the code of the rollback query into rollback query box 706 in response to receiving an indication of user input to select generate rollback button 710. QMS 108 may reset the boxes of rollback interface 700 in response to receiving an indication of user input to select reset button 712.
To populate the code of the rollback query into rollback query box 706, QMS 108 processes the code for the query to automatically generate the code for the rollback query. In some examples, the Java programming language may be used to implement the portions of QMS 108 that process the code for the query to automatically generate the code for the rollback query. For example, QMS 108 identifies a type of the query specified by the code for the query. In examples where production database 112 is a SQL database, user 106 typically may provide only three types of queries for execution by QMS 108: INSERT, DELETE, and UPDATE. User 106 may use the INSERT query to insert new records in database tables of production database 112. User 106 may use the DELETE query to delete records from database tables of production database 112. User 106 may use the UPDATE query to update or change information of an existing record or records in database tables of production database 112. After identifying the type of the query specified by the code for the query, QMS 108 processes the code of the rollback query to generate the code of the rollback query, e.g., code that is specifically tailored to roll back the query supplied by user 106.
In one example, QMS 108 identifies the code for the query as an INSERT type. QMS 108 identifies a table name specified by the code for the query. As an example, the code for the query is “INSERT into Table-1 [9999][data to be inserted]”. In this example, the specified table name is “Table-1.” QMS 108 retrieves table definition information from a database data dictionary of production database 112. Most databases (including Oracle, DB2, SQL Server, etc.) keep table definition data in the database data dictionary. QMS 108 retrieves a field name of a table identified as a “Primary Key” from the database data dictionary. The “Primary Key” is a field of a database table that is unique to each record inserted or maintained within the database table. Using the “Primary Key” for the table and the value for the field specified by the code for the query provided by user 106, QMS 108 generates a “where clause” of a rollback query. With respect to the foregoing example, QMS 108 determines that the Primary Key specifies “ID” and the value for the field specified by the code for the query is “9999,” such that the “where” clause is “where ID=9999.” Further, QMS 108 generates a prefix DELETE statement and table name for the generated “where clause” to create a completed DELETE query configured to delete the record specified by the code for the query provided by user 106. With respect to the foregoing example, the completed DELETE query would be “DELETE from Table-1 where ID=9999.” Thus, as detailed above, where the query specified by user 106 is an INSERT query to add a specific record, QMS 108 generates a DELETE query that removes the specified record. Thus, the completed DELETE query is the code of the rollback query that rolls back the INSERT query specified by the code for the query provided by user 106.
As another example, QMS 108 identifies the code for the query as an DELETE type. As an example, the code for the query is “DELETE from Table-1 [9999][data to be deleted]”. QMS 108 identifies details specified by the code for the query as described above. For example, QMS 108 identifies a table name specified by the code for the query. As an example, a table name specified by the code for the query is “Table-1.” QMS 108 retrieves table definition information from a database data dictionary of production database 112. QMS 108 retrieves a “field name” of a table identified as a “Primary Key” from the database data dictionary. Using the “Primary Key” for the table and a value for the field specified by the code for the query provided by user 106, QMS 108 generates a “where clause.” With respect to the foregoing example, QMS 108 determines that the Primary Key specifies “ID” and the value for the field specified by the code for the query is “9999,” such that the “where” clause is “where ID=9999.” Using the identified table name, QMS 108 prepares an internal temporary SELECT query using the table name and the generated “where clause.” With respect to the foregoing example, the generated SELECT query would be “SELECT from Table-1 where ID=9999.” QMS 108 executes the SELECT query to reads the existing values of the field(s) of the table matching the SELECT query into memory. QMS 108 generates an INSERT query and appends the current values of the records stored in memory to form a completed INSERT query. With respect to the foregoing example, the completed INSERT query would be “INSERT into Table-1 where ID=9999 [records obtained from execution of the ‘SELECT from Table-1 where ID=9999’ query]”. Thus, as detailed above, where the query specified by the user is a DELETE query to delete a specific record, QMS 108 generates an INSERT query that contains the present values of the specified record such that the record may be restored after deletion. Thus, the completed INSERT query is the code of the rollback query that rolls back the DELETE query specified by the code for the query provided by user 106.
In another example, QMS 108 identifies the code for the query as an UPDATE type. QMS 108 may generate a rollback query that rolls back the UPDATE query specified by the code for the query provided by user 106 using a combination of the techniques for generating rollback code for the INSERT and DELETE queries described above. For example, where an UPDATE query changes a value to a field of an existing entry, QMS 108 may identify the table that includes the entry, the specific entry, the field, the previous value of the field, and the changed value of the field, and generate a rollback query to restore the previous value to the field of the entry.
The data in statistical data boxes 708 may indicate the number of queries involved in the query, the type of queries in the query, the number of records deleted by executing the query, and the number of records modified by executing the query.
Thus, in some examples, the code for a query (e.g., a query entered in query box 326) expresses an operation to be performed on production database 112 may be considered first code for the query and may express a first operation to be performed on production database 112. In such examples, the first operation may be configured to alter at least one record stored by production database 112 from a first state to a second state. Furthermore, in such examples, prior to outputting, for display to a second user, the code for the query for review by the second user, QMS 108 may process the first code to generate a second code for rolling back changes to the database caused by execution of the first code. The second code expresses a second operation to be performed on production database 112. The second operation is configured to cause production database 112 to revert the at least one record from the second state back to the first state.
As shown in the example of
Storage device(s) 816 may store information required for use during operation of computing system 102. Storage system 110 (
Computing system 102 may include one or more input device(s) 808 that computing system 102 uses to receive user input. Examples of user input include tactile, audio, and video user input. Input device(s) 808 may include presence-sensitive screens, touch-sensitive screens, mice, keyboards, voice responsive systems, microphones or other types of devices for detecting input from a human or machine.
Communication unit(s) 804 may enable computing system 102 to send data to and receive data from one or more other computing devices (e.g., via a communications network, such as a local area network or the Internet). For instance, communication unit(s) 804 may be configured to receive and receive data from user devices (e.g., user devices 104 (
Output device(s) 810 may generate output. Examples of output include tactile, audio, and video output. Output device(s) 810 may include presence-sensitive screens, sound cards, video graphics adapter cards, speakers, liquid crystal displays (LCD), or other types of devices for generating output.
Processor(s) 802 may be implemented in circuitry. Example types of processor(s) 802 may include microprocessors, application-specific integrated circuits, field-programmable gate arrays, and so on. Processor(s) 802 may read instructions from storage device(s) 816 and may execute instructions stored by storage device(s) 816. Execution of the instructions by processor(s) 802 may configure or cause computing system 102 to provide at least some of the functionality ascribed in this disclosure to computing system 102. As shown in the example of
Furthermore, as shown in the example of
In some examples, after obtaining the code for a query to production database 112 and prior to outputting the code for the query for review by a second user, QMS 108 may store, in a log database 822, a record for the query comprising the code for the query. Additionally, after obtaining the indication that the second user approves the code for the query and prior to receiving the request to execute the query on the first database, QMS 108 may store data in log database 822 indicating that the second user approves the code for the query. After receiving the request to execute the query on the production database 112, QMS 108 may store data in log database 822 that indicates the request to execute the query. In this way, QMS 108 may be able to store data that may be used to audit the approval and execution processes of queries that are executed on production database 112. For instance, data in log database 822 may be used to determine how an improper query came to be executed on production database 112.
Additionally, in some examples, QMS 108 may store an indication that a first user is a user of the database, store an indication that a second user is a supervisor of the first user. QMS 108 may also store data indicating that the first user is allowed to submit code for one or more queries to production database 112. QMS 108 may also store data indicating that the second user is allowed to submit approval of the one or more codes for the one or more queries to the database. QMS 108 may store such data in storage device(s) 816. In this way, QMS 108 may store data defining roles and permissions for users. Defining such roles and permissions for users may help ensure that only appropriate users are able to submit and review queries.
In some examples, QMS 108 may be configured to work with multiple databases. For example, QMS 108 may work with a MySQL database and also work with an SQL Server database. QMS 108 may store data defining different roles and permissions for users for different databases. For instance, QMS 108 may store data indicating that a first user is allowed to submit queries for a first database and execute queries on the first database, but not a second database. In another example, QMS 108 may store data indicating that a second user is allowed to review queries for a second database but not a third database, and so on. In some examples, QMS 108 may store, for each database, data indicating which users have permission to submit queries for the database, which users have permission to review queries for the database, and which users have permission to execute queries on the database. QMS 108 may store such data in storage system 110 (
In examples where QMS 108 works with multiple databases, there may be different applications associated with different ones of the databases. In other words, there may be a one-to-one mapping between applications and databases. During a setup process, applications may be registered with QMS 108. To register an application with QMS 108, QMS 108 may capture various types of information, such as a name of the application, a database type of the database corresponding to the application, and a database URL of the application corresponding to the application.
In some examples, QMS 108 may define users belonging to a particular role. QMS 108 may store data in storage system 110 (and hence, storage device(s) 816) data indicating roles for users. Example roles may include user, approver, administrator, and root. A user having the user role may be allowed to submit queries along with appropriate rollback queries for approval. A user having the user role may also be allowed to execute approved queries. A user having the approver role may approve or reject submitted queries. In some examples, a user having the approver role may be a subject matter expert (SME) or lead for an application. In some examples, a user having an approver role may not be allowed to approve a query submitted by the user himself/herself.
A user having an administrator role may be able to grant or revoke access to QMS 108 for users of particular applications. A user having the administrator role may be able to use QMS 108 to configure details of an application database of application software. A user having the administrator role may also be allowed to define roles of other users. In some examples, a user having the administrator role may be able to use audit data and/or purge old records. In some examples, there is only one user having an administrator role for each registered application or database. A user having the root role may be a super user of QMS 108. A user having the root role may set the user having the administrator role and register this user. In some examples, a user having the root role may access all requests to QMS 108. Furthermore, in some examples, only a user having the root role may be able to register an application. In some examples, a user having the root role may mark applications as being active or inactive.
Although this disclosure is described largely with reference to production databases, the techniques of this disclosure may be applicable to other types of databases.
The techniques described in this disclosure may be implemented, at least in part, in hardware, software, firmware or any combination thereof. For example, various aspects of the described techniques may be implemented within one or more processors, including one or more microprocessors, digital signal processors (DSPs), application specific integrated circuits (ASICs), field programmable gate arrays (FPGAs), or any other equivalent integrated or discrete logic circuitry, as well as any combinations of such components. The term “processor” or “processing circuitry” may generally refer to any of the foregoing logic circuitry, alone or in combination with other logic circuitry, or any other equivalent circuitry. A control unit comprising hardware may also perform one or more of the techniques of this disclosure.
Such hardware, software, and firmware may be implemented within the same device or within separate devices to support the various operations and functions described in this disclosure. In addition, any of the described units, modules or components may be implemented together or separately as discrete but interoperable logic devices. Depiction of different features as modules or units is intended to highlight different functional aspects and does not necessarily imply that such modules or units must be realized by separate hardware or software components. Rather, functionality associated with one or more modules or units may be performed by separate hardware or software components, or integrated within common or separate hardware or software components.
The techniques described in this disclosure may also be embodied or encoded in a computer-readable medium, such as a computer-readable storage medium, containing instructions. Instructions embedded or encoded in a computer-readable storage medium may cause a programmable processor, or other processor, to perform the method, e.g., when the instructions are executed. Computer readable storage media may include random access memory (RAM), read only memory (ROM), programmable read only memory (PROM), erasable programmable read only memory (EPROM), electronically erasable programmable read only memory (EEPROM), flash memory, a hard disk, a CD-ROM, a floppy disk, a cassette, magnetic media, optical media, or other computer readable media.
Various examples have been described. These and other examples are within the scope of the following claims.
Claims
1. A method comprising:
- obtaining, by processing circuitry of a computing device and from a first user, code for a query to a database, wherein the code for the query expresses an operation to be performed on the database in response to execution of the query;
- in response to receiving the code for the query, automatically performing, by the processing circuitry, an operation to determine a number of records of the database that would be accessed by executing the query on the database;
- outputting, by the processing circuitry and for display to the first user, the number of records of the database that would be accessed by executing the query on the database;
- outputting, by the processing circuitry and for display to the first user, a prompt for an acknowledgement by the first user of the number of records of the database that would be accessed;
- receiving, by the processing circuitry and from the first user, an indication of the acknowledgement by the first user of the number of records of the database that would be accessed;
- in response to receiving the code for the query and the indication of the acknowledgement by the first user of the number of records of the database that would be accessed, outputting, by the processing circuitry and for display to a second user, the code for the query for review by the second user;
- after outputting the code for the query for review by the second user, obtaining, by the processing circuitry and from the second user, an indication that the second user approves the code for the query; and
- based on the indication that the second user approves the code for the query, executing, by the processing circuitry, the query on the database.
2. The method of claim 1, wherein the method further comprises:
- prior to outputting, for display to the second user, the code for the query for review by the second user: automatically validating, by the processing circuitry, a syntax of the code for the query; and outputting, by the processing circuitry and for display to the first user, an indication of whether the syntax of the code for the query is valid.
3. The method of claim 1,
- wherein the method further comprises after receiving the indication that the second user approves the code for the query, receiving, by the processing circuitry, a request to execute the query on the database; and
- wherein executing the query on the database comprises: based on the indication that the second user approves the code for the query and in response to receiving the request to execute the query, executing, by the processing circuitry, the query on the database.
4. The method of claim 3,
- wherein the operation to determine the number of records of the database that would be accessed by executing the query on the database comprises a first operation to determine a first number of records of the database that would be accessed by executing the query on the database, and
- wherein the method further comprises: in response to receiving the request to execute the query and prior to executing the query on the database, automatically performing, by the processing circuitry, a second operation to determine a second number of records of the database that would be accessed by executing the query on the database; and outputting, by the processing circuitry, the second number of records of the database that would be accessed by executing the query on the database.
5. The method of claim 4,
- wherein the method further comprises: prior to executing the query on the database: determining that the second number of records of the database that would be accessed by executing the query on the database is different from the first number of records of the database that would be accessed by executing the query on the database; outputting, by the processing circuitry, an indication that the second number of records is different from the first number of records and a prompt for an explanation of why the second number of records is different from the first number of records; receiving, by the processing circuitry, data indicating the explanation of why the second number of records is different from the first number of records; and storing, by the processing circuitry, the data indicating the explanation of why the second number of records is different from the first number of records, and
- wherein in response to receiving the request to execute the query and based on the indication that the second user approves the code for the query, executing, by the processing circuitry, the query on the database comprises: in response to receiving the request to execute the query and the data from the first user indicating the explanation of why the second number of records is different from the first number of records, and based on the indication that the second user approves the code for the query, executing, by the processing circuitry, the query on the database.
6. The method of claim 1,
- wherein the code for the query that expresses the operation to be performed on the database comprises a first code for the query that expresses a first operation to be performed on the database, wherein the first operation is configured to alter at least one record stored by the database from a first state to a second state,
- wherein the method further comprises: prior to outputting, for display to the second user, the code for the query for review by the second user: outputting, by the processing circuitry and to the first user, a prompt for a second query for rolling back changes to the database caused by execution of the first code, wherein the second query expresses a second operation to be performed on the database, and wherein the second operation is configured to revert the at least one record from the second state back to the first state; and receiving, by the processing circuitry and from the first user, the second code for rolling back changes to the database caused by execution of the first code.
7. The method of claim 1,
- wherein the code for the query that expresses the operation to be performed on the database comprises a first code for the query that expresses a first operation to be performed on the database, wherein the first operation is configured to alter at least one record stored by the database from a first state to a second state,
- wherein the method further comprises: prior to outputting, for display to the second user, the code for the query for review by the second user: processing, by the processing circuitry, the first code to generate a second code, wherein the second code is for rolling back changes to the database caused by execution of the first code, wherein the second code expresses a second operation to be performed on the database, and wherein the second operation is configured to cause the database to revert the at least one record from the second state back to the first state.
8. The method of claim 1,
- wherein the database comprises a first database, and
- wherein the method further comprises: after obtaining the code for the query to the first database and prior to outputting the code for the query for review by the second user, storing, by the processing circuitry and in a second database, a record for the query comprising the code for the query; after obtaining the indication that the second user approves the code for the query and prior to receiving the request to execute the query on the first database, storing, by the processing circuitry, data in the second database that indicates that the second user approves the code for the query; and after receiving the request to execute the query on the first database, storing, by the processing circuitry, data in the second database that indicates the request to execute the query.
9. The method of claim 1, further comprising:
- storing, by the processing circuitry, an indication that the first user is a user of the database;
- storing, by the processing circuitry, an indication that the second user is a supervisor of the first user;
- storing, by the processing circuitry, data indicating that the first user is allowed to submit one or more codes for one or more queries to the database; and
- storing, by the processing circuitry, data indicating that the second user is allowed to submit approval of the one or more codes for the one or more queries to the database.
10. The method of claim 1, the first user is disallowed from submitting approval of one or more queries to the database that the same first user submitted.
11. The method of claim 1, wherein receiving the request to execute the query on the database comprises receiving, from the first user, the request to execute the query on the database.
12. A computing system comprising processing circuitry and a storage system, the processing circuitry configured to:
- obtain, from a first user, code for a query to a database stored in the storage system, wherein the code for the query expresses an operation to be performed on the database;
- in response to receiving the code for the query, automatically perform an operation to determine a number of records of the database that would be accessed by executing the query on the database;
- output, for display to the first user, the number of records of the database that would be accessed by executing the query on the database;
- output, for display to the first user, a prompt for an acknowledgement by the first user of the number of records of the database that would be accessed;
- receive, from the first user, an indication of the acknowledgement by the first user of the number of records of the database that would be accessed;
- in response to receiving the code for the query and the indication of the acknowledgement by the first user of the number of records of the database that would be accessed, output, for display to a second user, the code for the query for review by the second user;
- after outputting the code for the query for review by the second user, obtain, from the second user, an indication that the second user approves the code for the query; and
- based on the indication that the second user approves the code for the query, execute the query on the database.
13. The computing system of claim 12, wherein the processing circuitry is configured to:
- prior to outputting, for display to the second user, the code for the query for review by the second user: automatically validate a syntax of the code for the query; and output, for display to the first user, an indication of whether the syntax of the code for the query is valid.
14. The computing system of claim 13,
- wherein the processing circuits are further configured to receive, after receiving the indication that the second user approves the code for the query, a request to execute the query on the database; and
- wherein the processing circuits are configured to execute, based on the indication that the second user approves the code for the query and in response to receiving the request to execute the query, the query on the database.
15. The computing system of claim 14,
- wherein the operation to determine the number of records of the database that would be accessed by executing the query on the database comprises a first operation to determine a first number of records of the database that would be accessed by executing the query on the database, and
- wherein the processing circuitry is further configured to: in response to receiving the request to execute the query and prior to executing the query on the database, automatically perform a second operation to determine a second number of records of the database that would be accessed by executing the query on the database; and output the second number of records of the database that would be accessed by executing the query on the database.
16. The computing system of claim 15,
- wherein the processing circuitry is further configured to: prior to executing the query on the database: determine that the second number of records of the database that would be accessed by executing the query on the database is different from the first number of records of the database that would be accessed by executing the query on the database; output an indication that the second number of records is different from the first number of records and a prompt for an explanation of why the second number of records is different from the first number of records; receive data indicating the explanation of why the second number of records is different from the first number of records; store, in the storage system, the data indicating the explanation of why the second number of records is different from the first number of records; and in response to receiving the request to execute the query and the data from the first user indicating the explanation of why the second number of records is different from the first number of records, and based on the indication that the second user approves the code for the query, execute the query on the database.
17. The computing system of claim 12,
- wherein the code for the query that expresses the operation to be performed on the database comprises first code for the query that expresses a first operation to be performed on the database, wherein the first operation is configured to alter at least one record stored by the database from a first state to a second state,
- wherein the processing circuitry is further configured to: prior to outputting, for display to the second user, the code for the query for review by the second user: output, to the first user, a prompt for second query for rolling back changes to the database caused by execution of the first code, wherein the second query expresses a second operation to be performed on the database, and wherein the second operation is configured to revert the at least one record from the second state back to the first state; and receive, from the first user, the second code for rolling back changes to the database caused by execution of the first code.
18. The computing system of claim 12,
- wherein the code for the query that expresses the operation to be performed on the database comprises first code for the query that expresses a first operation to be performed on the database, wherein the first operation is configured to alter at least one record stored by the database from a first state to a second state,
- wherein the processing circuitry is further configured to: prior to outputting, for display to the second user, the code for the query for review by the second user: process the first code to generate a second code for rolling back changes to the database caused by execution of the first code, wherein the second code expresses a second operation to be performed on the database, and wherein the second operation is configured to cause the database to revert the at least one record from the second state back to the first state.
19. The computing system of claim 12,
- wherein the database comprises a first database, and
- wherein the processing circuitry is further configured to: after obtaining the code for the query to the first database and prior to outputting the code for the query for review by the second user, store, in a second database, a record for the query comprising the code for the query; after obtaining the indication that the second user approves the code for the query and prior to receiving the request to execute the query on the first database, store data in the second database that indicates that the second user approves the code for the query; and after receiving the request to execute the query on the first database, store data in the second database that indicates the request to execute the query.
20. The computing system of claim 12, wherein the processing circuitry is further configured to:
- store, in the storage system, an indication that the first user is a user of the database;
- store, in the storage system, an indication that the second user is a supervisor of the first user;
- store, in the storage system, data indicating that the first user is allowed to submit one or more codes for one or more queries to the database; and
- store, in the storage system, data indicating that the second user is allowed to submit approval of the one or more codes for the one or more queries to the database.
21. The computing system of claim 12, the first user is disallowed from submitting approval of one or more queries to the database that the same first user submitted.
22. A non-transitory, computer-readable medium comprising instructions that, when executed, are configured to cause processing circuitry of a computing device to:
- obtain, from a first user, code for a query to a database, wherein the code for the query expresses an operation to be performed on the database;
- in response to receiving the code for the query, automatically perform an operation to determine a number of records of the database that would be accessed by executing the query on the database;
- output, for display to the first user, the number of records of the database that would be accessed by executing the query on the database;
- output, for display to the first user, a prompt for an acknowledgement by the first user of the number of records of the database that would be accessed;
- receive, from the first user, an indication of the acknowledgement by the first user of the number of records of the database that would be accessed;
- in response to receiving the code for the query and the indication of the acknowledgement by the first user of the number of records of the database that would be accessed, output, for display to a second user, the code for the query for review by the second user;
- after outputting the code for the query for review by the second user, obtain, from the second user, an indication that the second user approves the code for the query; and
- based on the indication that the second user approves the code for the query, execute the query on the database.
Type: Application
Filed: Nov 19, 2019
Publication Date: Dec 30, 2021
Inventors: Prashant Kumar Sinha (Gaya), Rajesh Kumar Agrawal (Haryana)
Application Number: 16/687,882