Query Optimization Logic Preserving Hints on VPD Protected Columns
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face inefficiencies when enforcing access control policies, as they lose track of optimization hints, leading to full table scans instead of using existing indexes on protected columns, resulting in significant performance issues.
Innovation Solution
Implementing query optimization logic that creates a new query with a predicate that conjunctively joins a clone of the original expression with a conditional expression derived from the access control policy, preserving optimization hints and allowing the use of existing indexes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a conditional expression is substituted for a protected column to enforce access control policy, then security is improved, but query performance deteriorates due to loss of optimization hints
Solution Approach 1:
The patent creates a clone of the original query expression that preserves the column reference needed for optimization hints, while separately enforcing the access control policy through conditional logic. This copying approach allows the system to maintain both security enforcement and query optimization capability.
Solution Approach 2:
The patent segments the query processing into distinct components: one part handles the access control policy enforcement through conditional expressions, while another part preserves the original expression structure for optimization hint preservation. This segmentation allows independent optimization of each function.
2Reliability
If the original query is modified to enforce access control, then security is improved, but the database system loses track of optimization hints leading to full table scans
Solution Approach 1:
The patent creates a clone of the original query expression that preserves the column reference needed for optimization hints, while separately enforcing the access control policy through conditional logic. This copying approach allows the system to maintain both security enforcement and query optimization capability.
Solution Approach 2:
The patent introduces an intermediary clone expression that acts as a bridge between the original query and the access control enforcement mechanism. This intermediary preserves the optimization hints while allowing the conditional expression to enforce security policies.
3Ease of manufacture
If an index exists on a protected column, then query optimization is possible, but substituting the column with a conditional expression prevents index utilization
Solution Approach 1:
The patent creates a clone of the original query expression that preserves the column reference needed for optimization hints, while separately enforcing the access control policy through conditional logic. This copying approach allows the system to maintain both security enforcement and query optimization capability.
Data Source
AI summary
A method and apparatus for preserving optimization hints in a transformed query is provided. In one embodiment, the methodology is implemented by query optimization logic. Upon receiving a first query to access values in a column of a table protected by an access control policy, the query optimization logic creates a second query that is equivalent to the first query as subject to the access control policy. Furthermore, the second query contains a new predicate that conjunctively joins a clone of a first expression in a predicate of the first query with a second expression that is derived, based on the access control policy, from the first expression. In one embodiment, the query optimization logic submits the second query for execution.