Row-Level Security Predicate Injection for Faster Database Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face challenges in optimizing performance when handling complex and voluminous queries, particularly due to the need for row-level security policies that increase complexity and reduce query performance.
Innovation Solution
Implementing a mechanism where a database management system dynamically generates a filter predicate string based on a row-level security policy, which is then converted into a query optimizer predicate and injected into the query plan, resulting in a truncated query result set.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If row level security policies are implemented to secure database objects, then security and access control are improved, but query performance and system productivity deteriorate
Solution Approach 1:
The system pre-compiles row level security policies into executable filter expressions during policy definition, rather than evaluating policy conditions at query execution time. This preliminary compilation stores the security logic in an optimized format that can be directly applied to query results, eliminating the performance overhead of interpreting policy conditions during each query execution.
Solution Approach 2:
The system creates a copy of the query result set and applies the pre-compiled filter expression to the copied data rather than filtering the original results during query execution. This allows the security filtering to be performed on a duplicate data structure using optimized operations, reducing the performance impact on the main query execution path.
2Reliability
If row level security policies are enforced on complex queries, then data protection is improved, but the complexity of query processing increases
Solution Approach 1:
The system extracts the security filtering logic from the main query processing path and implements it as a separate, pre-compiled filter expression. This separation removes the complexity of policy interpretation from the query execution engine, allowing complex queries to be processed using standard optimization techniques while security is applied as a distinct, simplified operation on the result set.
Data Source
AI summary
When a query targeting a database object is detected, a database management system determines whether a row level security policy is defined for the database object. If a row level security policy is defined for the database object, the database management system dynamically generates a filter predicate string based on the row level security policy. Then, the filter predicate string is converted into a query optimizer predicate. Next, the query optimizer predicate is injected into a query plan corresponding to the query. Then, a first query result set is generated during execution of the query plan and the query optimizer predicate is applied to the first query result set. In an example, applying the query optimizer predicate to the first query result set results in the creation of a second query result set which is a truncated version of the first query result set.