Compute openWorldHint from effective ClickHouse grants instead of hard-coding it

[BorisTyshkevich]

Branch codex/issue-48-tool-safety starts announcing 3 flags that prevent the LLM client from asking confirmations:

• annotations.readOnlyHint = true
• annotations.destructiveHint = false
• annotations.openWorldHint = false

However, openWorldHint is being set too aggressively.

Per the MCP tool annotation docs, openWorldHint=false means a tool does not interact with an open world of external entities, and the default is true: MCP tools / annotations. OpenAI's Apps SDK guidance also treats these annotations as safety-relevant hints for tool behavior: Apps SDK reference, Define tools, Build your MCP server.

For ClickHouse, read_only is not enough to justify openWorldHint=false. A read-only user can still reach external systems through table functions and external engines. ClickHouse's own recent guidance says that a read-only service can still export data externally through a table function: How to set up ClickHouse for agentic analytics. ClickHouse access control is granular enough to distinguish this, because effective permissions can be inspected with SHOW GRANTS WITH IMPLICIT, and external access is governed through grants/revokes on table engines and related objects: GRANT statement.

That means we should not hard-code openWorldHint=false for:

• execute_query
• dynamic tools backed by arbitrary ClickHouse views

Instead, we should derive openWorldHint from the effective ClickHouse grants of the user actually executing the query.

Proposed behavior

Use SHOW GRANTS WITH IMPLICIT FINAL against the effective ClickHouse identity and compute a cached safety profile.

High-level rule:

• Default to openWorldHint=true.
• Set openWorldHint=false only when the effective grants prove the user cannot access external systems through denylisted table engines / table-function-related access.
• If grant inspection fails, is unsupported, or cannot be parsed confidently, keep openWorldHint=true.

Initial denylist should include at least:

• URL
• S3
• HDFS
• AzureBlobStorage
• MySQL
• PostgreSQL
• MongoDB
• JDBC
• ODBC
• Redis
• SQLite
• File
• Distributed
• Hive
• Kafka
• RabbitMQ
• NATS
• REMOTE

Heuristic to implement:

• If grants show explicit access to denylisted external engines or equivalent broad external access, keep openWorldHint=true.
• If grants include broad access such as TABLE ENGINE ON *, treat that as open-world unless every denylisted external engine is explicitly revoked.
• If no broad external access is present and no denylisted external grant is present, set openWorldHint=false.

Example effective grants that should classify as closed-world are along these lines:

GRANT TABLE ENGINE ON * TO demo
REVOKE TABLE ENGINE ON AzureBlobStorage FROM demo
REVOKE TABLE ENGINE ON Distributed FROM demo
REVOKE TABLE ENGINE ON File FROM demo
REVOKE TABLE ENGINE ON HDFS FROM demo
REVOKE TABLE ENGINE ON Hive FROM demo
REVOKE TABLE ENGINE ON JDBC FROM demo
REVOKE TABLE ENGINE ON Kafka FROM demo
REVOKE TABLE ENGINE ON MongoDB FROM demo
REVOKE TABLE ENGINE ON MySQL FROM demo
REVOKE TABLE ENGINE ON NATS FROM demo
REVOKE TABLE ENGINE ON ODBC FROM demo
REVOKE TABLE ENGINE ON PostgreSQL FROM demo
REVOKE TABLE ENGINE ON RabbitMQ FROM demo
REVOKE TABLE ENGINE ON Redis FROM demo
REVOKE TABLE ENGINE ON S3 FROM demo
REVOKE TABLE ENGINE ON SQLite FROM demo
REVOKE TABLE ENGINE ON URL FROM demo

Implementation notes

• Cache the computed safety profile per effective ClickHouse identity.
• In non-JWE mode, this is one cache entry for the configured user.
• In JWE mode, compute and cache per resolved ClickHouse user/credentials, not globally.
• Apply the computed openWorldHint to both:
  • execute_query
  • dynamic view-backed tools
• Keep existing readOnlyHint / destructiveHint behavior unchanged.
• Dynamic tool COMMENT metadata must not override the server-computed openWorldHint.

Acceptance criteria

• execute_query no longer hard-codes openWorldHint=false.
• Dynamic tools no longer hard-code openWorldHint=false.
• SHOW GRANTS WITH IMPLICIT is used to derive a cached per-identity safety profile.
• Unknown / failed grant classification falls back to openWorldHint=true.
• Tests cover:
  • no external grants => closed-world
  • broad wildcard grant without matching revokes => open-world
  • wildcard grant plus full denylist revokes => closed-world
  • explicit grant on one denylisted external engine => open-world
  • JWE identities do not share cached grant classification

References

• MCP tools / annotations
• OpenAI Apps SDK reference
• OpenAI Apps SDK: Define tools
• OpenAI Apps SDK: Build your MCP server
• ClickHouse GRANT statement
• How to set up ClickHouse for agentic analytics