--- name: Block Destructive and Mutating Snowflake SQL tags: - snowflake - guard-warehouse-sql - ingress - sql - readonly - soc2 - pci-dss - sox publishedAt: 2026-07-12 description: | # snowflake / guard-warehouse-sql **Direction:** ingress (`tool_pre_invoke`) **Default:** deny on a mutating/destructive statement, allow otherwise **Package:** `snowflake.ingress.guard_warehouse_sql` ## What it does Inspects the SQL text that Snowflake MCP tools carry in their `query` argument and denies any statement in a mutating or destructive class — `DROP`, `TRUNCATE`, `DELETE`, `UPDATE`, `INSERT`, `MERGE`, `ALTER`, `CREATE`, `GRANT`, and `REVOKE` (the `CREATE` class also covers `CREATE USER` / `CREATE ROLE` governance DDL). Read-only statements — `SELECT` and other queries that match none of those keywords — pass through. The Snowflake-Labs server also exposes DDL tools that carry **no SQL string** — `create_object`, `create_or_alter_object`, and `drop_object` take structured args (`object_type`, `target_object`) instead. `drop_object` alone can drop a Database, Schema, Table, View, Warehouse, Role, or User. Because the SQL-text check can never see inside these, the policy denies them **by tool name**, so the read-only guarantee does not leak through them. The effect is to make any Snowflake connection **effectively read-only for ordinary agent callers**, independent of the managed server's `read_only` flag or the Labs server's `sql_statement_permissions` allowlist: the check is on the SQL text (and, for the structured DDL tools, the tool name) itself, so it holds even when the server-side gate is misconfigured or absent. Callers whose IdP-issued `groups` claim includes `data-platform-admins` are exempt, so break-glass DDL still works for the platform team. The exemption is read through an `object.get` chain that **fails closed** — a caller with no claims, or no `groups` claim, is treated as having no groups and is therefore subject to the deny. This runs at ingress, before the statement reaches Snowflake, so a blocked `DROP`/`DELETE`/`GRANT` never executes — Time Travel and undrop are not needed because the change never happens. ## Compliance alignment - **SOC 2 CC8.1** — supports change management by preventing the agent from making unreviewed schema/DDL changes (`CREATE`/`ALTER`/`DROP`) to production data structures outside a controlled break-glass path. - **PCI DSS 7.2.6** — supports restricting programmatic query access to stored cardholder data by role: mutating access to warehouse data over the MCP path is confined to the `data-platform-admins` group, and everyone else is read-only. - **SOX §802 / 18 U.S.C. §1519** — supports the anti-destruction/alteration of records control by blocking `DROP`/`TRUNCATE`/`DELETE`/`UPDATE` against financially relevant warehouse tables on the agent channel. - **HIPAA §164.312(c)(1) / §164.308(a)(4)** — supports the integrity standard and information-access management on a PHI-capable warehouse: blocking `DROP`/`TRUNCATE`/`DELETE`/`UPDATE` (and the structured `create_object`/`drop_object` DDL tools) for non-admin callers protects ePHI from improper alteration or destruction on the agent channel and confines mutating access to the `data-platform-admins` group. ## Tool name matching The policy matches the Snowflake tools that pass a raw SQL statement in a `query` argument, by **suffix** (the gateway prefixes tool names with the configured MCP server name, which is not standardized): - `*system_execute_sql` — Snowflake-managed server (`SYSTEM_EXECUTE_SQL` type) - `*run_snowflake_query` — Snowflake-Labs server (`snowflake-labs-mcp`) - `*read_query`, `*write_query`, `*create_table` — isaacwasserman community server A second set of Snowflake-Labs tools is matched by name (not by SQL text) because they carry no `query` argument and are mutating/destructive by design: - `*create_object`, `*create_or_alter_object`, `*drop_object` — Snowflake-Labs structured DDL tools (denied outright for non-exempt callers) Cortex read tools that emit natural language rather than raw SQL — `CORTEX_ANALYST_MESSAGE` (arg `message`) and `CORTEX_SEARCH_SERVICE_QUERY` (arg `query`) — do **not** end with any of these suffixes and pass through untouched, as do the read/list/describe helpers (`list_objects`, `list_databases`, `describe_table`, …). **Managed-server caveat:** the Snowflake-managed server lets the admin choose each tool's name (the dangerous semantics live in the tool `type`, which is not on the wire at call time). If your managed server exposes its SQL tool under a custom name (e.g. `sales-sql`), add that suffix to `sql_tool_suffixes` in `policy.md`, and pair this policy with a PF-28 `default-deny-unknown-tools` policy so an unrecognized SQL tool cannot slip past. See Known limitations. ## Argument shape All matched tools carry the statement in `input.payload.args.query`. As a defensive fallback the policy also inspects `statement` and `sql` keys and matches the concatenation, so a deployment that renamed the argument is still covered. If none of those keys is present the call has no inspectable SQL and is allowed through (the tool call is inert without a statement). ## Examples ### Allowed — read-only SELECT ```jsonc { "input": { "action": "tool_pre_invoke", "resource": { "name": "snowflake-mcp-run_snowflake_query", "type": "tool" }, "payload": { "name": "snowflake-mcp-run_snowflake_query", "args": { "query": "SELECT id, created_at, updated_at FROM orders LIMIT 10" } } } } ``` `allow = true` — `created_at`/`updated_at` do not trip `CREATE`/`UPDATE` because the pattern is anchored on word boundaries. ### Allowed — Cortex natural-language tool passes through ```jsonc { "input": { "action": "tool_pre_invoke", "resource": { "name": "snowflake-mcp-CORTEX_ANALYST_MESSAGE", "type": "tool" }, "payload": { "name": "snowflake-mcp-CORTEX_ANALYST_MESSAGE", "args": { "message": "update me on this quarter's revenue" } } } } ``` `allow = true` — not a SQL tool; the word "update" in prose is irrelevant. ### Denied — destructive SQL ```jsonc { "input": { "action": "tool_pre_invoke", "resource": { "name": "snowflake-mcp-run_snowflake_query", "type": "tool" }, "payload": { "name": "snowflake-mcp-run_snowflake_query", "args": { "query": "DROP TABLE customers" } } } } ``` `allow = false` with the mutating-SQL reason. ### Allowed — break-glass admin The same `DROP TABLE customers` call succeeds when `input.subject.claims.groups` contains `data-platform-admins`. ## Composition This policy blocks statement-class mutation. Useful companions: - **PF-07 bulk-export sibling** — deny `COPY INTO @` / `COPY INTO 's3://…'` and bare `CREATE STAGE` to stop exfiltration to external storage (this policy blocks `CREATE STAGE` via the `CREATE` class but not a `COPY INTO` that reuses an existing stage). - **PF-28 `default-deny-unknown-tools`** — allowlist the exact Snowflake tool names the gateway exposes so an admin-named managed SQL tool cannot bypass the suffix match here. - **PF-22 `deny-escape-hatches`** — deny opaque composite tools (`CORTEX_AGENT_RUN`, `GENERIC` UDF/stored-proc wrappers) whose SQL the gateway cannot see. - **Egress PII/PAN redaction** on `read_query`/`run_snowflake_query` results, since a permitted `SELECT *` can still return regulated data. ## Known limitations - **Regex over SQL text, not a parser.** The keyword match runs on the raw string. A mutating keyword inside a string literal or comment (e.g. `SELECT 'we will DROP this later'`) is a false positive; conversely, SQL that mutates without one of the listed keywords (a `CALL` to a stored procedure that deletes, an `EXECUTE IMMEDIATE` assembled from fragments) is a false negative. Treat this as a high-signal read-only guard, not a SQL firewall. - **Stored procedures / composite tools.** `CALL proc()` and Cortex Agent / `GENERIC` tools can have arbitrary side effects the gateway cannot inspect — deny those with the PF-22 companion. - **Non-string SQL arguments.** The SQL inspection only reads `query`/ `statement`/`sql` when they are strings (the verified schema for every matched tool). A crafted non-string value (a list or object) is treated as carrying no inspectable SQL and passes through as inert rather than erroring the rule open — but if a future/forked server actually executed SQL from a non-string argument, that statement would not be inspected. Pair with the PF-28 companion, which pins the exact tool set and argument contract. - **Managed-server admin naming.** Tools on the Snowflake-managed server have admin-chosen names; only the `system_execute_sql` suffix is matched by default. Add your configured name to `sql_tool_suffixes` and pair with PF-28. Verified tool names for the Labs and community servers come from the Snowflake landscape note; the managed-server tool *name* is unverified by design (semantics are carried in the non-wire `type`). - **Bulk export not covered.** `COPY INTO` against an existing external stage is not a listed keyword — use the PF-07 bulk-export companion. - **Group names are placeholders** — replace `data-platform-admins` with your IdP's group name at import time. The exemption reads `input.subject.claims.groups`; on Auth0 tenants without RBAC/permissions configured, no `groups` claim reaches the policy and the exemption never fires (fail-closed — everyone is read-only until the claim is wired up). > **Compliance note.** This policy supports alignment with the cited framework controls **on the MCP path only**. No policy or bundle makes an organization compliant with any framework; web-UI, native-API, and in-app access are outside the gateway's reach by design. Validate against your own compliance program before relying on it. direction: ingress apps: - snowflake industries: [] bundles: - soc2 - pci-dss - sox experimental: true schemaVersion: 1.0.0 minimumGatewayVersion: 1.0.0b24 --- ```rego package snowflake.ingress.guard_warehouse_sql # Deny-by-default: a Snowflake SQL tool call is permitted only when it carries no # mutating/destructive statement, or the caller is an exempt data-platform admin. default allow := false # --- Tool matching ----------------------------------------------------------- # The gateway prefixes tool names with the configured MCP server name, so we # match on the suffix to stay portable. These are the Snowflake tools that pass a # raw SQL statement in a `query` argument: # - `system_execute_sql` — Snowflake-managed server (SYSTEM_EXECUTE_SQL type) # - `run_snowflake_query` — Snowflake-Labs server # - `read_query`/`write_query`/`create_table` — isaacwasserman community server # Cortex tools (`CORTEX_ANALYST_MESSAGE`, `CORTEX_SEARCH_SERVICE_QUERY`) and # list/describe helpers do NOT end with any of these suffixes, so they pass # through untouched. sql_tool_suffixes := [ "system_execute_sql", "run_snowflake_query", "read_query", "write_query", "create_table", ] is_sql_tool if { name := lower(input.resource.name) some suffix in sql_tool_suffixes endswith(name, suffix) } # --- Structured mutating tools ---------------------------------------------- # The Snowflake-Labs server also exposes DDL tools that carry NO SQL string — # they take structured args (`object_type`, `target_object`) — so the SQL-text # check below can never see them. They are mutating/destructive by definition # (`drop_object` can drop a Database, Schema, Table, View, Warehouse, Role, or # User), so we deny them by tool name outright, subject to the same break-glass # exemption. Without this, the "effectively read-only" guarantee would leak. structured_mutating_suffixes := [ "create_object", "create_or_alter_object", "drop_object", ] is_structured_mutating_tool if { name := lower(input.resource.name) some suffix in structured_mutating_suffixes endswith(name, suffix) } # --- SQL extraction ---------------------------------------------------------- # All matched tools pass the statement in `query`; we also inspect `statement` # and `sql` defensively in case a deployment renamed the argument, and match the # concatenation of whichever are present. Only string values are inspected — a # crafted non-string arg (list/number/object) is treated as carrying no SQL # rather than erroring the rule into fail-open (see Known limitations). sql_text := concat(" ", [v | some key in ["query", "statement", "sql"] v := object.get(input.payload.args, key, "") is_string(v) v != "" ]) # Mutating/destructive statement classes. Case-insensitive `(?i)` and anchored on # word boundaries `\b` so identifiers like `created_at`, `updated_at`, or # `merge_log` do not trip CREATE/UPDATE/MERGE. The CREATE class also covers # CREATE USER / CREATE ROLE governance DDL. mutating_pattern := `(?i)\b(?:DROP|TRUNCATE|DELETE|UPDATE|INSERT|MERGE|ALTER|CREATE|GRANT|REVOKE)\b` is_mutating_sql if { regex.match(mutating_pattern, sql_text) } # --- Identity exemption ------------------------------------------------------ # Break-glass: callers in the `data-platform-admins` IdP group may run DDL. The # object.get chain fails closed — a missing `subject.claims` object or missing # `groups` claim yields an empty list, so an unauthenticated/unclaimed caller is # never exempt. caller_groups := object.get(object.get(input.subject, "claims", {}), "groups", []) is_exempt if { some g in caller_groups g == "data-platform-admins" } # --- Allow rules ------------------------------------------------------------- # Non-guarded tools (Cortex Analyst/Search, list/describe helpers, anything # else) pass through. Both the SQL-carrying tools and the structured DDL tools # are guarded, so neither slips through this branch. allow if { not is_sql_tool not is_structured_mutating_tool } # Break-glass admins may run any statement on the SQL tools. allow if { is_sql_tool is_exempt } # Ordinary callers may run read-only SQL (SELECT and other non-mutating reads). allow if { is_sql_tool not is_exempt not is_mutating_sql } # Break-glass admins may also run the structured DDL tools. allow if { is_structured_mutating_tool is_exempt } # --- Deny reason ------------------------------------------------------------- reasons contains "This Snowflake statement performs a mutating or destructive operation (DROP, TRUNCATE, DELETE, UPDATE, INSERT, MERGE, ALTER, CREATE, GRANT, or REVOKE — including CREATE USER / CREATE ROLE) and is blocked on the agent MCP path, which is read-only. Rewrite it as a SELECT, or have a member of the data-platform-admins group run break-glass DDL. If this was a false positive, contact your data platform team." if { is_sql_tool not is_exempt is_mutating_sql } reasons contains "This Snowflake tool performs a structured schema/object mutation (create/alter/drop of a database, schema, table, view, warehouse, role, or user) and is blocked on the agent MCP path, which is read-only. Route the change through your change-management process, or have a member of the data-platform-admins group run it as break-glass DDL. If this was a false positive, contact your data platform team." if { is_structured_mutating_tool not is_exempt } reason := joined if { count(reasons) > 0 reason_list := sort([r | some r in reasons]) joined := concat("; ", reason_list) } ```