Database Query Rewriting for Sensitive Data Access Control
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems lack effective mechanisms to enforce user policies for controlling access to sensitive data, leading to potential exposure of sensitive information without consent, especially when employees fetch data for various purposes without explicit identifiers for data subjects.
Innovation Solution
A system and method that preprocess queries by analyzing sensitivity using a sensitive tree generated from database metadata, rewrites queries to enforce user policies, and implements a merger function for queries with set operators to ensure data subject privacy, thereby preventing unauthorized access and notifying data subjects as per predefined preferences.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If employees write flexible SQL queries to fetch data for resolving tickets, then query flexibility and operational ease are improved, but data subject identifier information is lost and sensitive data exposure cannot be controlled
Solution Approach 1:
The system performs preliminary actions by automatically adding data subject identifier columns to queries before execution. The query rewriting module intercepts employee queries, identifies sensitive data columns, and automatically appends identifier columns (e.g., customer_id, account_id) to retrieve data subject information without requiring employees to manually modify their queries.
Solution Approach 2:
The system introduces an intermediary layer (query rewriting module and sensitive tree) between the employee's original query and the database. This intermediary automatically enriches queries with data subject identifier columns, acts as a mediator that adds necessary information without changing the employee's original query intent or operational ease.
2Reliability
If data subject identifier columns are added to all queries, then data subject notification capability is improved, but query complexity and processing overhead increase
Solution Approach 1:
The system applies local quality by selectively adding data subject identifier columns only to queries that access sensitive data columns. The sensitive tree module analyzes each query to determine which columns are sensitive, and only then adds identifier columns to those specific queries, avoiding unnecessary complexity in queries that don't access sensitive data.
Solution Approach 2:
The system changes parameters dynamically based on query content. The query rewriting module analyzes the original query, identifies sensitive columns, and only modifies the query by adding identifier columns when necessary. This parameter change approach ensures data subject notification capability is improved only when needed, without universally increasing query processing complexity.
3Productivity
If sensitive data is accessed without explicit consent mechanisms, then operational efficiency is maintained, but privacy compliance and data security deteriorate
Solution Approach 1:
The system implements feedback mechanisms through data subject notification. After queries are executed and sensitive data is accessed, the system generates notifications to data subjects informing them of the access. This feedback loop ensures privacy compliance while maintaining operational efficiency, as the notification process automatically occurs after data retrieval without interfering with employee productivity.
Solution Approach 2:
The system introduces an intermediary notification mechanism between data access and data subject awareness. The preference enforcement module acts as an intermediary that automatically generates and sends notifications to data subjects when their sensitive data is accessed, ensuring privacy compliance without requiring explicit consent checks during the data retrieval process itself.
Data Source
AI summary
A system and method are provided for enforcing user policies on database. In one aspect a user policy and/or enterprise policy is predefined and mapped to the column of the database. Further, the query is run through a query parsing module, the result is sent to a query analyzing module to analyze the sensitivity of each query. A query rewriting module rewrites the query and the rewritten query is sent to the database. A sensitive tree is generated using database metadata, which is used during query analysis and query re-writing. In cases the original query does not contain any set operators the rewritten query is executed on the database and results are displayed as per the user policy. The cases where the original query comprises set operators a function called merger is implemented in the database or at the proxy server and data is displayed as per the user policy.