Secure View Query Optimization via Predicate Pushdown
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Network-based database systems face challenges in securely sharing data across accounts while maintaining security and privacy restrictions, particularly in cloud data warehouses where providers need to grant access to only a subset of data.
Innovation Solution
The implementation of secure views and functions within the database system, which utilize secure predicates, pruning pushdowns, and secure projections to limit access and prevent data exposure, while optimizing performance through techniques like predicate pushdown and pullup.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If secure views and functions are implemented to limit data access, then data security is improved, but query performance deteriorates due to additional security boundary checks
Solution Approach 1:
The system performs preliminary actions by pushing down security predicates and pruning operations into secure view boundaries during query optimization, rather than evaluating all operations sequentially. This pre-positioning of security checks within the query execution plan reduces overhead during actual query execution while maintaining security guarantees.
Solution Approach 2:
The query execution plan is segmented into distinct regions inside and outside secure view boundaries. By dividing the query operations and applying different optimization strategies to each segment (pushing down predicates inside boundaries, keeping them outside when necessary), the system achieves both security compliance and performance optimization without treating the entire query uniformly.
2Productivity
If predicate pushdown optimization is applied across secure view boundaries, then query performance is improved, but data security is compromised due to potential data leakage
Solution Approach 1:
The system applies different optimization qualities to different regions of the query plan. Inside secure view boundaries, only security-compliant operations are permitted, while outside boundaries, aggressive optimizations like predicate pushdown can be applied. This localized approach to optimization quality ensures security is never compromised while maximizing performance where safe.
Solution Approach 2:
The secure view boundary acts as an intermediary layer between the query optimizer and the underlying data. This boundary mediates the conflict between optimization and security by allowing the optimizer to push down predicates generally, but blocking or modifying those pushes that would cross secure boundaries and potentially leak data, thus resolving the contradiction automatically.
3Reliability
If all query operations are evaluated inside secure view boundaries, then data security is maintained, but query execution time increases significantly
Solution Approach 1:
The system extracts operations that do not require security checking from inside secure view boundaries and positions them outside. By taking out non-sensitive operations (such as certain aggregations or filters that don't access protected data) from the secure boundary region, the system reduces the number of security checks performed during query execution while maintaining security for operations that actually access protected data.
Data Source
AI summary
A source table can be provided by a provider account in a data system. A secure view of the source table is provided to one or more consumer accounts, the secure view limiting access to a subset of data in the source table. A plan to execute a command using the secure view may be generated, the plan including a secure view boundary on a subset of operations defining the secure view. The plan may be modified to move a first operation that was outside the secure view boundary to within the secure view boundary to generate a second plan to optimize performance in view of limitations or restrictions placed by the secure view.