Column-Based Access Control for Database Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database configurations offer only all-or-nothing access control, failing to provide efficient column-level access control, which limits the ability to restrict access to specific data columns based on user roles or organizational permissions.
Innovation Solution
Implementing column-based access control mechanisms within a database system, where access to restricted columns is determined by values in restricting columns, using calculated columns that enforce security policies dynamically at runtime, allowing for both column-level and line-level access restrictions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If all-or-nothing access control is used for database instances, then implementation is simple, but granular data protection capability is poor
Solution Approach 1:
The patent segments the database table into multiple columns, applying different access control policies to each column. Instead of treating the entire table as a single unit with uniform access rights, the system divides the data structure into column-level units that can be independently authorized. This allows specific columns to be restricted based on user roles while other columns remain accessible, resolving the contradiction between simple implementation and granular protection capability.
Solution Approach 2:
The patent implements local quality by applying different security attributes to different columns within the same table. Each column can have its own access control list (ACL) defining which users or roles can access it. This enables fine-grained security where sensitive columns (e.g., salary information) can be restricted to specific roles while non-sensitive columns (e.g., employee name) remain broadly accessible, achieving granular data protection without requiring complete system redesign.
2Adaptability or versatility
If column-level access control is implemented, then granular data protection is improved, but system complexity increases
Solution Approach 1:
The patent implements self-service by automatically generating SQL queries that incorporate column-level access control logic. The system introspects the database schema to identify restricted columns, then automatically modifies query execution to filter or mask data in those columns based on user authorization. This automation eliminates the need for manual query modification or complex access control management interfaces, reducing system complexity while maintaining granular control capability.
Solution Approach 2:
The patent applies preliminary action by pre-configuring access control lists for each column during database setup or migration. The system预先 identifies which columns require restricted access and establishes the authorization rules in advance. When queries are executed, the pre-configured controls are automatically applied without requiring real-time decision-making or complex runtime evaluations, thereby simplifying the access control system while enabling granular data protection.
3Productivity
If full table access is granted to users, then data retrieval efficiency is high, but data security is compromised
Solution Approach 1:
The patent extracts the security control logic from the data retrieval process by implementing column-level filtering at the query execution level. Instead of granting full table access and then applying security filters in application code, the system extracts and enforces access controls directly within the database engine. This allows efficient data retrieval through optimized query execution while simultaneously protecting sensitive columns by excluding unauthorized data from the result set, thus maintaining both productivity and security.
4Object-affected harmful factors
If access restrictions are applied to specific columns, then data security is improved, but query performance may deteriorate
Solution Approach 1:
The patent applies preliminary action by pre-compiling and caching access control policies for each column. The system stores authorization rules in an optimized format that allows rapid evaluation during query execution. When a query accesses restricted columns, the pre-compiled access control logic is quickly applied without requiring complex runtime computations, thereby maintaining query performance while enforcing data security restrictions.
Data Source
AI summary
A system, method, and computer-readable medium, to receive a query specifying a result set of data from at least one database table; determine whether at least one column of the at least one database table is subject to a column-based authorization restriction; modify the query, in an instance it is determined that at least one column of the at least one database table is subject to a column-based authorization restriction, to restrict the result set of data in accordance with the column-based authorization restriction; and execute, in response to the modifying of the query, the modified query.