A MySQL read-only user for an AI agent: grants, limits and the gaps

The short answer

Create a dedicated MySQL user for the agent, grant it SELECT on the specific tables (or columns) the task needs, and nothing else: no EXECUTE, no write privileges, no ALL. Add a SELECT timeout with max_execution_time and hourly caps with MAX_QUERIES_PER_HOUR and MAX_USER_CONNECTIONS.¹³

Don’t use the server’s read_only setting for this. It’s a global switch for the whole server, meant for replicas, and users with CONNECTION_ADMIN or SUPER can still write while it’s on.¹ A user’s own grants are what limit an agent.

The setup

Code A database command in SQL. The text around it says what it does.

-- 1. A dedicated user, with caps on connections and query volume
CREATE USER 'agent_reader'@'10.0.%' IDENTIFIED BY '…'
  WITH MAX_QUERIES_PER_HOUR 2000
       MAX_USER_CONNECTIONS 3;

-- 2. SELECT on the tables the task needs, not the whole database
GRANT SELECT ON shop.orders   TO 'agent_reader'@'10.0.%';
GRANT SELECT ON shop.products TO 'agent_reader'@'10.0.%';

-- 3. Only some columns of a table with personal data
GRANT SELECT (id, created_at, country) ON shop.customers TO 'agent_reader'@'10.0.%';

-- 4. Confirm what it has
SHOW GRANTS FOR 'agent_reader'@'10.0.%';

The pieces, with what the manual says about each:

Three habits make this hold up over time. Grant per table rather than ON shop.*, so a table created next month isn’t readable by default. Give each agent its own user, so you can revoke one without breaking others. And run SHOW GRANTS after every change; it’s the only reliable answer to “what can this user do?”.

Why not just turn on read_only?

read_only and super_read_only look like the obvious switch. They’re the wrong tool here, for three reasons from the manual:

1. They’re global. They apply to the whole server, for every client. You can’t turn them on for one user.¹
2. read_only exempts powerful users. With it on, users with CONNECTION_ADMIN, or the deprecated SUPER, can still write. Only super_read_only stops them too.¹
3. Some writes are still allowed. Even in read-only mode, the server permits operations on TEMPORARY tables, ANALYZE TABLE and OPTIMIZE TABLE, and replication updates.¹

They exist to keep a replica from accepting client writes. If your agent reads from a replica, by all means turn on super_read_only there as an extra layer. But the user’s grants are still what decides which tables it can see.

Stored routines: the quiet way to write

A SELECT can call a stored function. If that function updates a table, the “read” writes. Two facts make the defence straightforward. The EXECUTE privilege is required to run stored routines, and the default SQL SECURITY for a routine is DEFINER, which means it runs with its creator’s rights, not the caller’s.⁵

So a user granted only SELECT can’t execute routines, and that’s what you want. Don’t add EXECUTE “to make a report work” without reading what the routine does, because a DEFINER routine written by an admin can do anything the admin can.

The timeout setting knows about this too. max_execution_time applies only to read-only SELECT statements; a statement that calls a function which modifies data isn’t read-only in its eyes.¹

Limits that keep a read from hurting

A read-only user can’t change data, but it can still cause trouble: a query that scans a huge table at peak time, or an agent that loops.

• Timeout. max_execution_time sets a SELECT timeout in milliseconds; 0, the default, means none. It’s global or per session, and it’s ignored for SELECT statements inside stored programs.¹ Set it in the agent’s session when it connects, for example SET SESSION max_execution_time = 5000;, or give the tool a wrapper that does.
• Hourly caps. MAX_QUERIES_PER_HOUR stops a runaway loop from hammering the server.³ Pick a number a few times above normal use, and treat hitting it as a signal.
• Connections. MAX_USER_CONNECTIONS keeps one agent from using the whole connection pool.³
• A replica. If you have one, point the agent there. Its reads never compete with production writes.

Multi-statement strings

Many SQL-injection tricks rely on sending two statements in one string: a harmless query, a semicolon, and something else. In the MySQL C API, a query string is treated as one statement by default; multiple statements need the CLIENT_MULTI_STATEMENTS flag or a server option set on the connection.⁴ Prepared statements only work with single statements.⁴

Check how your agent’s MySQL tool connects. Some drivers and MCP servers turn multi-statement mode on for convenience. A read-only user still limits the damage, but there’s no reason to allow stacked statements from an agent at all.

A checklist before you connect the agent

1. One user per agent, with a host pattern that matches only where the agent runs.
2. SELECT on named tables, never ALL and never ON db.* unless every table is fine to read.
3. Column grants on tables with personal data.²
4. No EXECUTE, unless you’ve read every routine it unlocks.⁵
5. A session max_execution_time set by the tool on connect.¹
6. Hourly and connection caps on the account.³
7. Multi-statement mode off in the driver.⁴
8. SHOW GRANTS saved with the date, so the next review starts from a known state.

Run through it again whenever the agent’s task changes. A user set up for one report tends to collect grants until it can read everything; the checklist is how you notice.

What a read-only user doesn’t solve

• Reading what it shouldn’t share. A read-only agent can still read customer data and put it in a reply, a ticket or an email. Column grants limit what it sees; nothing in MySQL limits what it says.
• Prompt injection through data. Text stored in a table can carry instructions for the model. Read-only access keeps it from writing; it doesn’t keep it from being misled.
• Tasks that genuinely need writes. Then you need a separate user with narrow write grants, plus approval for risky writes and a record of what changed.

For the Postgres version of this setup, see the read-only role comparison and read-only database access. If the agent does write to MySQL, auditing MySQL changes made by an AI agent covers the logs, and cascades missing from the binlog covers what those logs can miss. The blast radius entry explains why per-agent users matter. To see how a gate treats reads and writes on a real database in your browser, try the playground; to grade what an agent can reach on your machine, run npx deedglass scan (the grade, explained).

Written with AI assistance and checked against our claims register.