Column-Level DML Privilege Enforcement in Relational Database Security
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current access control models in database management systems, such as SQL GRANT statements and Virtual Private Databases, are inadequate for enforcing fine-grained access control over data manipulation operations like INSERT, UPDATE, and DELETE, as they do not support column-level privileges and require cumbersome procedural logic, making it difficult to manage access control for multiple columns and data change operations.
Innovation Solution
Implementing a User Privilege model that allows column-level privileges to be explicitly associated with data access operations, enabling fine-grained access control by transforming tables into view definitions during query execution to restrict operations to authorized data, and using a two-phase statement rewrite to optimize privilege checks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If SQL GRANT statements are used for access control, then object-level privileges can be granted, but column-level fine-grained access control for DML operations is not supported
Solution Approach 1:
The patent segments the table object into individual columns, allowing access control privileges to be granted at the column level rather than at the table level. This segmentation enables fine-grained control where different users can have different access rights to different columns of the same table, directly addressing the limitation of SQL GRANT statements that only support object-level privileges.
2Adaptability or versatility
If VPD model with PL/SQL policy functions is used, then column-level DML privileges can be enforced, but administration becomes cumbersome with procedural logic
Solution Approach 1:
The patent creates a simplified copy of the VPD functionality that achieves column-level DML privilege enforcement without requiring complex PL/SQL policy functions. By copying the essential access control mechanism and removing the procedural complexity, the system maintains fine-grained control capabilities while significantly improving ease of administration.
3Adaptability or versatility
If multiple VPD policies are used for multiple columns, then different column policies can be enforced, but the system becomes complicated with multiple policies ANDed together
Solution Approach 1:
The patent merges multiple column-level policy enforcement mechanisms into a single unified access control structure. Instead of requiring multiple VPD policies that are ANDed together, the system combines all column-level privileges into one coherent policy framework, simplifying the overall system architecture while maintaining the ability to enforce different policies on different columns.
Data Source
AI summary
Embodiments allow, within database security policies, the grant of data change operation-specific privileges to particular users to be applied within particular data realms in a given table. Furthermore, according to one or more embodiments, User Privilege column-level privileges are explicitly associated with one or more data access operations such that the grant of such a column-level privilege allows the user to perform only those data access operations that are explicitly associated with the column-level privilege. Enforcement of the data security policies includes prevention of data leakage via WHERE and RETURNING INTO clauses. According to one or more embodiments, a two-phase rewrite is used to optimize enforcement of column-level privileges. During the two-phase rewrite of a given query, the privileges checked during enforcement of the User Privilege data security policies are pruned to avoid unnecessary privilege checks given the columns that are accessed in the query.