Secure Database Query Handling via Pre-Approved Templates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In conventional database operations, non-administrator users, such as database performance teams, face delays in resolving performance issues due to the need for approval and intervention from database administrators to execute queries on views and tables, which can compromise data privacy and integrity.
Innovation Solution
A system that enables non-administrator users to submit queries through a user interface, with a query parser that automatically generates and executes secure, read-only queries on database views and tables, ensuring data privacy and integrity by limiting access and preventing modifications.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If database administrators manually review and approve queries to ensure data privacy and integrity, then data security is maintained, but query execution time and system productivity are reduced
Solution Approach 1:
The system pre-approves query templates and patterns before runtime, allowing non-administrator users to execute queries without real-time administrator intervention. Query templates are reviewed and approved in advance, storing approved query structures that can be automatically executed against database views and tables, eliminating the need for manual approval during actual query execution while maintaining security through pre-established validation rules
Solution Approach 2:
The system introduces a query template approval mechanism as an intermediary layer between non-administrator users and the database. Instead of requiring direct administrator approval for each query, the system uses pre-approved query templates that act as mediators, automatically validating and executing queries that match approved patterns, thus reducing manual intervention while maintaining data security through the template approval process
2Productivity
If non-administrator users are granted direct query access to database views and tables, then system productivity improves, but data privacy and integrity may be compromised
Solution Approach 1:
The system segments query access into two distinct layers: query template definition and query execution. Non-administrator users are granted access only to the execution layer, where they can run queries against pre-approved templates. The template definition and approval layer remains restricted to administrators. This segmentation allows users to execute queries independently while administrators maintain control over what queries are permitted, thus improving productivity without compromising data privacy
Solution Approach 2:
Approved query templates serve as intermediaries between non-administrator users and the database. These templates act as pre-validated query structures that users can execute without direct administrator involvement. The templates themselves are the security mechanism, encoding the approved access patterns and ensuring that even though users have execution access, they can only run queries that have been previously validated by administrators, thus maintaining data privacy while enabling user autonomy
3Loss of time
If manual query approval processes are eliminated, then system response time improves, but the risk of unauthorized data access increases
Solution Approach 1:
The system performs security validation in advance by pre-approving query templates during a controlled process. Administrators review and approve query templates beforehand, establishing a library of validated query patterns. Once approved, these templates can be executed immediately by non-administrator users without requiring real-time approval, thus eliminating approval delays while maintaining security through the preliminary validation that occurred during template approval
Solution Approach 2:
The system introduces query templates as intermediaries that embody pre-approved access patterns. These templates act as security proxies, allowing users to execute queries without direct administrator involvement. The templates themselves enforce the security policy by limiting queries to pre-approved structures and data access patterns, thus enabling fast execution while maintaining protection against unauthorized access through the intermediary template validation mechanism
Data Source
AI summary
Embodiments regard protected handling of database queries. An embodiment of a method for querying database system views and tables includes: receiving a user query from a user, the user query being directed to one or both of a view and a table of a database, wherein the user is not a database administrator; parsing the user query with a query parser to identify elements of the user query, parsing the query including determining whether the query meets certain database access criteria; automatically generating a database query based on the parsing of the user query, the generation of the database query including generating a database query that is limited by the database access criteria; accessing the one or both of the view and the table using the generated database query, wherein the access is limited to read-only access; and obtaining a result of the access of the one or both of the view and table.