Skip to content

PATTERN Cited by 1 source

Dual-layer governance — SQL-statement guard plus per-tool access guard

Pattern

When an MCP server (or equivalent agent-facing surface) brokers AI-agent access to a database, defend the database with two independent guards: a SQL-statement guard that inspects every SQL statement before it reaches the database, and a per-tool access guard that gates which of the server's exposed tools each agent profile can call. Pre-build a small set of named profiles (typically read_only / analyst / developer / admin) that map onto the underlying catalog's GRANT model, so that a misconfigured or malicious agent fails closed at both layers — "physically cannot drop a table" — even if one layer is bypassed.

Canonical instance

Lakebase MCP in Backstage with Lakebase, Part 2: Governance (Thoughtworks, 2026-05-15).

Verbatim from the source: "Two design choices keep this safe. First, dual-layer governance: a SQL-statement guard and a per-tool access guard, with four pre-built profiles (read_only, analyst, developer, admin) that map onto the same UC access patterns shown above. A coding assistant runs as read_only and physically cannot drop a table."

The canonical 4-profile preset:

Profile Use Example tool the SQL-guard caps
read_only Coding assistants SELECT only — no DDL, no DML
analyst Read + analyse, no mutation SELECT + read-only EXPLAIN
developer Schema changes on dev branches DDL on branches, DML on test data
admin Full surface DBA-on-top-of-the-platform

Each profile "maps onto the same UC access patterns shown [earlier in the post]" — i.e., the four profiles are presets over the underlying Unity Catalog GRANT model, not a parallel permission system.

Why two layers, not one

The single-layer alternative — gate only at the SQL-statement layer — is brittle because the SQL parser doesn't see intent: a CREATE TEMP TABLE x AS SELECT * FROM secrets looks like DDL + SELECT to a parser, but is a read_only-tier exfiltration if the agent shouldn't be able to materialize secrets. The per-tool guard handles this by saying "the read_only profile can call the select tool but not the materialize tool," operating at the higher-altitude semantic of what the agent is asking the database to do, not just what SQL it's emitting.

The single-layer-tool-only alternative — gate only at the tool layer — is brittle because tool authors emit SQL whose effect exceeds the tool's stated contract. A query tool whose agent substrate emits ;DROP TABLE x between statements bypasses the tool layer if the SQL guard isn't there to catch it.

The two layers compose by independently failing closed, so the worst-case behaviour is at least as restrictive as the strict intersection of both guards.

When it fits

  • AI-agent or third-party access to a database — the "agent identity is malleable, agent capability surface is vendor- controlled" threat model where you can't trust either the agent's chosen SQL or the agent's chosen tool boundary.
  • The underlying catalog has a GRANT model the profiles can map onto (UC GRANT in the canonical case). Without a back-stop GRANT model, the four-profile preset is just a soft policy.
  • A small number of well-defined profiles is enough — the pattern's preset count is small (4 in the canonical case) by design, because per-profile testing and behavioural review becomes a per-profile cost.

When it doesn't fit

  • Per-user fine-grained policy — the pattern is preset-based, not arbitrary-policy-based. If every user needs a unique allow-list, the pattern collapses to a single per-user-policy layer.
  • No access to a server-side guard. If the agent talks straight to the database with no MCP/proxy in front, the guard layer doesn't exist; the SQL-statement layer must be enforced inside the database itself (which Postgres can do via row-level security and column masks but not via arbitrary statement-class denial).

What it composes with

  • patterns/tool-tagged-query-attribution: the pattern's observability dual. Once the dual-layer guard has decided to let a statement through, the statement still gets tagged with the originating tool so audit and billing can attribute it. Without tagging, the "physically cannot drop a table" guarantee is enforced but the "who tried what" signal is lost.
  • [[patterns/foreign-catalog-federation-for-operational-db- governance]]: the four-profile preset "maps onto the same UC access patterns shown above" — i.e., the federated foreign- catalog GRANT model is the back-stop layer the agent-tier guards rest on. The pattern doesn't replace UC GRANT; it adds a tool- altitude layer on top.
  • concepts/separation-of-duties-data-governance: the pattern preserves separation-of-duties for AI-agent access by treating agent profile as a separate axis from user identity — the same DBA who has admin access in their human capacity has read_only access through a coding assistant.

Anti-patterns

  • Trust the SQL parser to enforce intent. SQL statements satisfy the "is this a SELECT" contract while exfiltrating data through computed expressions, JSON projection, or side-effecting functions. The tool layer catches this; SQL-only enforcement misses it.
  • Trust the tool layer in isolation. Tools' SQL emissions can exceed their declared contract. The SQL layer catches this; tool-only enforcement misses it.
  • Per-tool ACLs without preset profiles. Without a small, reviewable set of named profiles, the access surface becomes per-(tool × user × time) — operationally indistinguishable from the manual-ACL state the pattern is meant to replace.
  • Profile authoring without UC backstop. If profiles aren't rooted in the underlying catalog's GRANT model, the agent-tier becomes the only enforcement point — the structural property of "physically cannot" requires the guard layers and the underlying RBAC to agree.

Caveats

  • The 2026-05-15 source asserts the dual-layer design at the capability level but does not disclose: where the SQL guard parses (AST? regex? grammar?), how the two guards interact when one denies and the other allows (precedence rule), whether the guards run in shadow mode with override telemetry, and how the per-tool guard handles tool composition (a tool that internally invokes another tool).
  • The 4-profile preset is the canonical-instance default but is not asserted as a universal preset count — the pattern's structure (preset over a back-stop GRANT model) generalises to more or fewer profiles.

Seen in

  • sources/2026-05-15-databricks-backstage-with-lakebase-part-2 — First canonical wiki disclosure of the dual-layer-governance pattern. Lakebase MCP applies a SQL-statement guard plus a per-tool access guard with four pre-built profiles (read_only / analyst / developer / admin) mapping onto the same UC GRANT model that governs the underlying Lakebase database. "A coding assistant runs as read_only and physically cannot drop a table."
Last updated · 542 distilled / 1,571 read