Database Visibility Statistics for Hybrid Sharing Query Planning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face inefficiencies in computing visibility statistics for user access to records due to the combination of materialized and unmaterialized sharing rules, leading to poor query performance and increased resource utilization, especially in cloud-hosted applications with complex access control policies.
Innovation Solution
The system computes visibility statistics for both materialized and unmaterialized sharing rules efficiently by determining per-user-group and per-target-value counts, allowing for optimized query execution plans that prioritize subqueries based on the number of accessible records, thereby reducing computational overhead and resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the system applies every sharing rule and traverses all hierarchies to determine record access in real time, then access control accuracy is improved, but query execution time increases significantly
Solution Approach 1:
The system pre-computes visibility statistics that capture the essence of sharing rules and hierarchy relationships before they are needed for query execution. These statistics are stored and reused during runtime, eliminating the need to re-traverse hierarchies and re-evaluate sharing rules for every query, thus maintaining access control accuracy while dramatically reducing query execution time
Solution Approach 2:
The system segments the complex access control evaluation into two distinct phases: an offline pre-computation phase where visibility statistics are calculated and stored, and an online query phase where these pre-computed statistics are efficiently retrieved and applied. This segmentation allows the computationally intensive hierarchy traversal and sharing rule evaluation to occur only when necessary, rather than repeatedly during each query execution
2Speed
If the system pre-calculates and persists record access data to facilitate rapid scanning, then query execution speed is improved, but computational overhead during pre-processing increases
Solution Approach 1:
The system computes visibility statistics at appropriate granularity levels - not for every individual record-access combination, but aggregated by sharing rule and hierarchy level. This local quality approach computes only the necessary statistical summaries needed for query optimization, avoiding the exponential computational overhead of calculating complete access matrices while still enabling rapid query execution through selective use of pre-computed data
3Productivity
If the system optimizes for frequent updates of visibility statistics, then query performance is improved, but resource consumption increases
Solution Approach 1:
The system updates visibility statistics periodically or on-demand based on configuration changes, rather than continuously or before every query. This periodic action maintains query performance by ensuring statistics are current when needed, while avoiding unnecessary computational resource consumption during periods when no configuration changes occur. The system balances freshness of statistics with resource efficiency by triggering updates only when necessary
Data Source
AI summary
An entity-level visibility statistic may be determined for a database entity in a database system based on one or more visibility rules providing access to instances of the database entity to one or more user accounts. A user-level visibility statistic quantifying a set of instances of the database entity accessible to a user account via the one or more visibility rules may be determined based at least in part on the entity-level visibility statistic. A request may be received by the user account to execute an input database query retrieving one or more of the instances of the database entity. A database object retrieval query including two or more data security subqueries evaluating accessibility of the one or more instances of the database entity and positioned based at least in part on the user-level visibility statistic may be determined based on the input database query.