Permission Delegation System Low-Level Design: Scoped Grants, Expiry, and Audit

Permission Delegation System: Low-Level Design

Permission delegation allows a user (delegator) to grant a subset of their own permissions to another user (delegatee) for a bounded scope and time period. Use cases include vacation coverage, multi-party approval workflows, service account impersonation, and enterprise role hierarchies. This article designs the delegation data model, chain enforcement, impersonation JWT structure, revocation propagation, and audit logging.

Core Rules

Subset-only delegation: a delegator cannot grant permissions they do not themselves hold. Attempting to grant admin:write without holding it is rejected at creation time.
Chain depth limit: delegation chains (A delegates to B, B delegates to C) are capped at a configurable depth (typically 3) to prevent uncontrolled permission amplification.
Time-bounded grants: every delegation has an expires_at; expired grants are treated as revoked at check time without requiring a cleanup job.
Revocation propagation: revoking a grant also revokes all downstream grants derived from it (transitive revocation).

SQL Schema


CREATE TABLE Permission (
    id            INT UNSIGNED      NOT NULL AUTO_INCREMENT,
    name          VARCHAR(128)      NOT NULL,  -- e.g. 'documents:read', 'admin:write'
    description   VARCHAR(512)      NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_name (name)
) ENGINE=InnoDB;

CREATE TABLE PermissionGrant (
    id              BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
    delegator_id    BIGINT UNSIGNED   NOT NULL,
    delegatee_id    BIGINT UNSIGNED   NOT NULL,
    permission_id   INT UNSIGNED      NOT NULL,
    scope           VARCHAR(512)      NULL,     -- e.g. 'resource_type=document,org=acme'
    parent_grant_id BIGINT UNSIGNED   NULL,     -- the grant this was derived from
    chain_depth     TINYINT UNSIGNED  NOT NULL DEFAULT 0,
    granted_at      DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at      DATETIME          NOT NULL,
    revoked_at      DATETIME          NULL,
    PRIMARY KEY (id),
    INDEX idx_delegatee_perm  (delegatee_id, permission_id, expires_at),
    INDEX idx_delegator_perm  (delegator_id, permission_id),
    INDEX idx_parent          (parent_grant_id)
) ENGINE=InnoDB;

CREATE TABLE DelegationChain (
    grant_id        BIGINT UNSIGNED   NOT NULL,
    ancestor_id     BIGINT UNSIGNED   NOT NULL,  -- every ancestor grant_id
    depth           TINYINT UNSIGNED  NOT NULL,  -- distance from ancestor to this grant
    PRIMARY KEY (grant_id, ancestor_id),
    INDEX idx_ancestor (ancestor_id)
) ENGINE=InnoDB;

CREATE TABLE DelegationAudit (
    id              BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
    actor_id        BIGINT UNSIGNED   NOT NULL,
    action          ENUM('create','revoke','check','impersonate') NOT NULL,
    grant_id        BIGINT UNSIGNED   NULL,
    delegatee_id    BIGINT UNSIGNED   NULL,
    permission_name VARCHAR(128)      NULL,
    result          ENUM('allow','deny') NOT NULL,
    reason          VARCHAR(512)      NULL,
    ip_address      VARCHAR(45)       NULL,
    recorded_at     DATETIME(3)       NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    INDEX idx_actor      (actor_id, recorded_at DESC),
    INDEX idx_grant_audit (grant_id, recorded_at DESC)
) ENGINE=InnoDB;

Python Implementation


from datetime import datetime, timezone
import jwt
import json
import db

MAX_CHAIN_DEPTH = 3
JWT_SECRET = "changeme"
JWT_ALGORITHM = "HS256"


def create_delegation(
    delegator_id: int,
    delegatee_id: int,
    permission_name: str,
    expires_at: datetime,
    scope: str | None = None,
    parent_grant_id: int | None = None,
) -> int:
    """
    Create a delegation grant. Validates that delegator holds the permission
    and that the chain depth limit is not exceeded.
    """
    # 1. resolve permission
    perm = db.fetchone("SELECT id FROM Permission WHERE name = %s", (permission_name,))
    if not perm:
        raise ValueError(f"Unknown permission: {permission_name}")
    permission_id = perm["id"]

    # 2. verify delegator holds the permission (directly or via valid grant)
    if not _holds_permission(delegator_id, permission_name):
        raise PermissionError(f"Delegator {delegator_id} does not hold {permission_name}")

    # 3. compute chain depth
    if parent_grant_id:
        parent = db.fetchone(
            "SELECT chain_depth FROM PermissionGrant WHERE id = %s AND revoked_at IS NULL AND expires_at > NOW()",
            (parent_grant_id,)
        )
        if not parent:
            raise ValueError("Parent grant is invalid or expired")
        new_depth = parent["chain_depth"] + 1
    else:
        new_depth = 0

    if new_depth > MAX_CHAIN_DEPTH:
        raise ValueError(f"Delegation chain depth {new_depth} exceeds maximum {MAX_CHAIN_DEPTH}")

    # 4. insert grant
    grant_id = db.execute(
        """INSERT INTO PermissionGrant
               (delegator_id, delegatee_id, permission_id, scope, parent_grant_id, chain_depth, expires_at)
           VALUES (%s, %s, %s, %s, %s, %s, %s)""",
        (delegator_id, delegatee_id, permission_id, scope, parent_grant_id, new_depth, expires_at)
    )

    # 5. populate closure table for transitive revocation
    _insert_chain_closure(grant_id, parent_grant_id)

    _audit(delegator_id, "create", grant_id=grant_id,
           delegatee_id=delegatee_id, permission_name=permission_name, result="allow")

    return grant_id


def revoke_delegation(grant_id: int, actor_id: int) -> int:
    """Revoke a grant and all downstream grants transitively."""
    # find all descendant grant IDs via closure table
    descendants = db.fetchall(
        "SELECT grant_id FROM DelegationChain WHERE ancestor_id = %s", (grant_id,)
    )
    ids_to_revoke = [grant_id] + [r["grant_id"] for r in descendants]

    placeholders = ",".join(["%s"] * len(ids_to_revoke))
    db.execute(
        f"UPDATE PermissionGrant SET revoked_at = NOW() WHERE id IN ({placeholders}) AND revoked_at IS NULL",
        tuple(ids_to_revoke)
    )

    _audit(actor_id, "revoke", grant_id=grant_id, result="allow",
           reason=f"revoked {len(ids_to_revoke)} grants (including descendants)")

    return len(ids_to_revoke)


def check_delegated_permission(
    user_id: int,
    permission_name: str,
    scope: str | None = None,
) -> bool:
    """Check if user holds a permission via any valid delegation grant."""
    perm = db.fetchone("SELECT id FROM Permission WHERE name = %s", (permission_name,))
    if not perm:
        return False

    row = db.fetchone(
        """SELECT id FROM PermissionGrant
           WHERE delegatee_id = %s
             AND permission_id = %s
             AND revoked_at IS NULL
             AND expires_at > NOW()
             AND (%s IS NULL OR scope = %s OR scope IS NULL)
           LIMIT 1""",
        (user_id, perm["id"], scope, scope)
    )
    result = "allow" if row else "deny"
    _audit(user_id, "check", grant_id=row["id"] if row else None,
           permission_name=permission_name, result=result)
    return bool(row)


def issue_impersonation_token(
    delegator_id: int,
    delegatee_id: int,
    grant_ids: list[int],
    expires_at: datetime,
) -> str:
    """Issue a JWT that encodes the delegation chain for impersonation."""
    chain = _build_chain(grant_ids)
    payload = {
        "sub": str(delegatee_id),
        "act": {"sub": str(delegator_id)},
        "delegation_chain": chain,
        "exp": int(expires_at.timestamp()),
        "iat": int(datetime.now(timezone.utc).timestamp()),
    }
    return jwt.encode(payload, JWT_SECRET, algorithm=JWT_ALGORITHM)


def _holds_permission(user_id: int, permission_name: str) -> bool:
    """Check direct role assignment or valid delegation grant."""
    # In a real system, also check RolePermission table for direct grants.
    return check_delegated_permission(user_id, permission_name) or 
           _has_direct_role_permission(user_id, permission_name)


def _has_direct_role_permission(user_id: int, permission_name: str) -> bool:
    row = db.fetchone(
        """SELECT 1 FROM UserRole ur
           JOIN RolePermission rp ON rp.role_id = ur.role_id
           JOIN Permission p ON p.id = rp.permission_id
           WHERE ur.user_id = %s AND p.name = %s LIMIT 1""",
        (user_id, permission_name)
    )
    return bool(row)


def _insert_chain_closure(grant_id: int, parent_grant_id: int | None) -> None:
    """Populate DelegationChain closure table."""
    # self entry
    db.execute(
        "INSERT INTO DelegationChain (grant_id, ancestor_id, depth) VALUES (%s, %s, 0)",
        (grant_id, grant_id)
    )
    if parent_grant_id:
        ancestors = db.fetchall(
            "SELECT ancestor_id, depth FROM DelegationChain WHERE grant_id = %s",
            (parent_grant_id,)
        )
        for a in ancestors:
            db.execute(
                "INSERT INTO DelegationChain (grant_id, ancestor_id, depth) VALUES (%s, %s, %s)",
                (grant_id, a["ancestor_id"], a["depth"] + 1)
            )


def _build_chain(grant_ids: list[int]) -> list[dict]:
    rows = db.fetchall(
        "SELECT id, delegator_id, delegatee_id, permission_id, expires_at FROM PermissionGrant WHERE id IN (%s)"
        % ",".join(["%s"] * len(grant_ids)),
        tuple(grant_ids)
    )
    return [{"grant_id": r["id"], "from": r["delegator_id"], "to": r["delegatee_id"]} for r in rows]


def _audit(actor_id, action, *, grant_id=None, delegatee_id=None,
           permission_name=None, result, reason=None, ip=None):
    db.execute(
        """INSERT INTO DelegationAudit
               (actor_id, action, grant_id, delegatee_id, permission_name, result, reason, ip_address)
           VALUES (%s,%s,%s,%s,%s,%s,%s,%s)""",
        (actor_id, action, grant_id, delegatee_id, permission_name, result, reason, ip)
    )

Impersonation JWT Structure

The token uses the RFC 8693 act (actor) claim to indicate that sub (the delegatee) is acting on behalf of the delegator. The delegation_chain claim lists each grant in the chain so the receiving service can verify the full delegation path without a database round-trip. Tokens are short-lived (max equal to the earliest expires_at in the chain).

Revocation Propagation via Closure Table

The DelegationChain closure table records every ancestor-descendant pair. Revoking grant G requires a single query: SELECT grant_id FROM DelegationChain WHERE ancestor_id = G returns all descendants, and a single UPDATE with an IN clause revokes them all atomically in one transaction.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “What is the subset-only rule in permission delegation?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A delegator can only grant permissions they currently hold. Before creating a delegation, the system verifies that the delegator has the requested permission via either a direct role assignment or a valid existing delegation grant. Attempting to delegate a permission not held results in a PermissionError. This prevents privilege escalation through delegation chains.”
}
},
{
“@type”: “Question”,
“name”: “How do you enforce delegation chain depth limits?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Each PermissionGrant row stores chain_depth, which starts at 0 for direct delegations. When a grant is created from a parent grant, the system reads the parent’s chain_depth and sets the new grant’s depth to parent_depth + 1. If this value exceeds MAX_CHAIN_DEPTH (typically 3), the creation is rejected. This prevents unbounded delegation chains that are difficult to audit and revoke.”
}
},
{
“@type”: “Question”,
“name”: “How does transitive revocation work in a delegation system?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A closure table (DelegationChain) records every ancestor-descendant grant pair. When revoking a grant, query the closure table for all grant_ids where ancestor_id equals the revoked grant. A single UPDATE sets revoked_at for the revoked grant and all its descendants in one transaction. This ensures that revoking a delegation automatically invalidates all downstream delegations derived from it.”
}
},
{
“@type”: “Question”,
“name”: “What does an impersonation JWT look like in a delegation system?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The JWT uses the RFC 8693 ‘act’ claim: sub is the delegatee (the user acting), and act.sub is the delegator (the user being impersonated). A custom delegation_chain claim lists each grant ID, delegator, and delegatee in the chain. The token’s exp is set to the earliest expires_at across all grants in the chain. Receiving services validate the token signature, check exp, and optionally verify each grant against the database for high-security operations.”
}
}
]
}

{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How is scope restriction enforced in delegation?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”When creating a grant, the system verifies that all requested_permissions are a subset of the delegator’s current permissions; any out-of-scope permission causes the grant to fail.”}},{“@type”:”Question”,”name”:”How is transitive revocation implemented?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”DelegationChain stores all grants derived from a root grant; revoking the root grant cascades to all descendant grants via a recursive CTE or by traversing the chain table.”}},{“@type”:”Question”,”name”:”How is the delegation depth limit enforced?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”DelegationChain stores a depth counter incremented on each delegation; the system rejects new grants that would exceed the maximum depth (e.g., 3 hops).”}},{“@type”:”Question”,”name”:”How are delegation grants represented in JWTs?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The impersonation token includes a delegation_chain claim—an array of (delegator_id, grant_id) pairs—that authorization middleware validates before accepting delegated permissions.”}}]}