Hierarchical SQL Authorization Model for Database Permission Inheritance

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Conventional database management systems lack a uniform and generic authorization model to accommodate complex relationships between various objects, making it difficult to define and implement authorization rules effectively.

Innovation Solution

An extension to the ISO/ANSI SQL standard grammar is introduced to facilitate a hierarchical permissions model, allowing permissions to be granted on parent nodes and automatically inherited by child nodes, along with a method for changing ownership through a two-part handshake using GRANT TAKE OWNERSHIP and ALTER AUTHORIZATION statements.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If conventional DBMS authorization mechanisms are used, then authorization to resources can be provided, but the system lacks a uniform and generic authorization model to accommodate complex relationships between various objects

Engineering Contradiction:

Improveauthorization model adaptabilityVSAvoidauthorization mechanism complexity

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent implements a universal authorization model using a single GRANT statement syntax that can accommodate multiple object types (tables, views, procedures, functions) and multiple permission types (SELECT, INSERT, UPDATE, DELETE, EXECUTE) through a unified hierarchical structure. This allows one authorization mechanism to handle diverse authorization needs without requiring separate mechanisms for each object type.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

The patent segments the authorization hierarchy into parent nodes (schemas, databases) and child nodes (tables, views, procedures), allowing permissions to be granted at different levels of the hierarchy. This segmentation enables the system to manage complex relationships between objects by breaking down authorization into manageable hierarchical units that can be propagated automatically.

Inventive Principle:

Principle #1Segmentation

2Ease of operation

If permissions are granted on parent nodes in the hierarchy, then administrative convenience is improved, but the system must correctly propagate permissions across the hierarchy

Engineering Contradiction:

Improveadministrative convenienceVSAvoidpermission propagation accuracy

Core Design Contradiction:

Ease of operationVSReliability

Solution Approach 1:

The patent implements preliminary action by automatically propagating permissions from parent nodes to child nodes when a GRANT statement is executed at a higher level of the hierarchy. For example, when a user is granted SELECT permission on a schema, the system automatically propagates this permission to all tables and views within that schema, eliminating the need for manual permission granting at each level while ensuring accurate permission distribution.

Inventive Principle:

Principle #10Preliminary action

3Productivity

If a single GRANT operation applies to all current and future entities within a schema, then management efficiency is enhanced, but the system must accommodate future entities that may be created

Engineering Contradiction:

Improvemanagement efficiencyVSAvoidfuture entity accommodation

Core Design Contradiction:

ProductivityVSAdaptability or versatility

Solution Approach 1:

The patent implements preliminary action by establishing permissions at the schema level that automatically apply to all current and future entities within that schema. When a GRANT statement is executed on a schema, the system immediately propagates the permission to existing tables and views, and continues to propagate to any future tables or views that are created within the schema, eliminating the need for repeated permission granting operations.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

Inventive Principle:

Principle #6Universality (Multi-functionality)

Data Source

PatentUS7613711B2Specification of a hierarchical authorization model for a DBMS—SQL language extensions

Publication Date: 2009.11.03 MICROSOFT TECHNOLOGY LICENSING LLC

AI summary

Provided are systems and methods that facilitate providing permission to entities of a database. A system includes a component that authorizes a principal of a containing entity to grant a permission to that entity, and a component that grants the permission to the containing entity, the grantee of the permission inherits a set of permissions to one or more entities contained by the containing entity. When a permission is granted to a parent in a hierarchy of a relational database, the permission is inherited by the child nodes. Also provided is a method for transferring ownership of entities in a relational database. The method includes a two-part handshake that can be audited to avoid repudiation issues.