Query Management System for Database Risk Mitigation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Production databases face risks from improper queries, such as badly written or malicious queries, which can lead to data loss, corruption, or exfiltration, causing significant technical and reputational damage, and are difficult to recover from.
Innovation Solution
A query management system (QMS) that obtains code for a query, determines the number of records that would be accessed, prompts users for acknowledgement, and requires approval from two users before executing the query, thereby reducing the risk of improper queries by ensuring two sets of eyes review the query.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a production database allows direct query execution for operational efficiency, then productivity is improved, but the risk of data loss or corruption from improper queries increases
Solution Approach 1:
The system performs preliminary actions by calculating the number of records that would be accessed by a query before the query is executed. This advance computation allows the system to warn users about potentially problematic queries (e.g., those accessing excessive records) and prevent data loss or corruption before it occurs, while still allowing legitimate queries to proceed efficiently
Solution Approach 2:
The system implements feedback by notifying users of the number of records a query would access and seeking user confirmation before execution. This feedback loop allows users to reconsider potentially harmful queries while maintaining the ability to execute necessary queries, thus balancing productivity and data integrity
2Reliability
If the system calculates the number of records accessed by every query to prevent improper queries, then data integrity is improved, but the time required to execute queries increases
Solution Approach 1:
The system applies partial action by calculating record counts for queries that are likely to be problematic (e.g., complex queries, queries by certain users, or queries during certain times) rather than for every single query. This selective approach maintains data integrity for high-risk operations while minimizing the time overhead for routine queries
3Reliability
If the system requires user acknowledgement and approval for query execution to prevent improper queries, then the risk of data loss is reduced, but the complexity of the database operation process increases
Solution Approach 1:
The system applies local quality by requiring user acknowledgement and approval only for queries that access a significant number of records or meet specific risk criteria, rather than for all queries. This selective application of the approval process reduces unnecessary complexity for simple, low-risk queries while maintaining strong safeguards for potentially harmful operations
Data Source
AI summary
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.