<!-- https://testapilocally.com/questions/read-only-sql-access-for-ai-agents/ · updated 2026-11-19 -->
# Safe read-only SQL access for AI agents

## How do I give an AI agent read-only access to my database?

Enforce read-only **below** the agent, so the database refuses writes, rather than relying on a prompt that says "only run SELECT". The options, strongest first:

1. **A database user with only `SELECT` grants.** This is the best fit for shared or production-like databases.
2. **A read-only transaction or connection.** This is what the SignOff Studio MCP server does for `db_query` and `db_assert`: PostgreSQL `BEGIN READ ONLY`, MySQL/MariaDB `START TRANSACTION READ ONLY`, Oracle `SET TRANSACTION READ ONLY`, SQLite opened read-only, and on SQL Server a transaction that is always rolled back. Every read ends in a rollback.
3. **Parsing the SQL and allowing only reads.** This is weak on its own: CTEs with writes, functions with side effects and engine-specific syntax get through. The server uses it only as a first gate (for example, to stop DDL that would commit implicitly in MySQL or Oracle before the transaction could help).

SignOff Studio layers 2 and 3 on top of whatever grants your connection has, so a read-only user plus the server gives you all three.

## What else should a safe setup do?

- **One statement per call.** `SELECT 1; DROP TABLE x` is rejected rather than split.
- **Row and cell limits.** Results are capped (50 rows and 200 characters per cell by default), so one query can't flood the agent's context.
- **No passwords in the model.** The password comes from the workspace `.env` or the app's vault and is scrubbed from every output.
- **An audit trail.** Each call is logged to `.signoff/agent-audit.jsonl` (tool, target, outcome, time; not the SQL or data).
- **Writes are a separate, opt-in tool.** `db_execute` exists for test databases you choose (**Allow AI agent writes**). It never runs on connections tagged production, and it does a dry run (rolled back) by default.

## Is a prompt instruction like "never modify data" enough?

No. Instructions help the agent choose the right tool, but they aren't a control. A confused or prompt-injected agent can ignore them. Enforcement has to be in the database user or the connection.

## Can I hide connection details from the agent?

They're hidden by default. `db_connections` shows each connection's name, engine, environment tag, whether agent writes are allowed and where the password comes from, but not the host, database or user. To show those, set `"db": { "showConnectionDetails": true }` in the workspace policy (`.signoff/agent-policy.json`, git-ignored). The agent refers to connections by name either way.

See also: [How can an AI agent verify a database migration?](/questions/ai-agent-verify-database-migration/) · [MCP server overview](/mcp/)
