How to set up a read-only MySQL user for an AI agent

Quick answer: One CREATE USER with a specific host, REQUIRE SSL and resource limits, one GRANT SELECT on the single database it needs, and nothing on information_schema, which fails even for root and is not needed. Then connect as the account and test it, because SHOW GRANTS can leave out a privilege the session really has. Everything on this page was run against MySQL 8.4.11 in a container.

The setup

CREATE DATABASE IF NOT EXISTS agentdemo;
USE agentdemo;

CREATE TABLE IF NOT EXISTS orders (
  id     INT PRIMARY KEY,
  status VARCHAR(20),
  total  DECIMAL(12,2)
);
INSERT INTO orders VALUES (1, 'pending', 10);

Now create the account. Set the resource limits and transport requirement on the CREATE USER statement:

CREATE USER IF NOT EXISTS 'ai_agent'@'10.20.30.40'
  IDENTIFIED BY 'use-a-generated-secret'
  REQUIRE SSL
  WITH MAX_USER_CONNECTIONS 5
       MAX_QUERIES_PER_HOUR 2000;

The statement contains four decisions, including the host, which is often left at %. Put the agent’s address there, or the address of the proxy it actually connects through. That address may differ from the agent’s address. The host field does not segment the network. A firewall or network policy stops packets; the host field prevents a leaked credential from working elsewhere.

Store the secret in a secret manager rather than in the statement you paste. Anything you type here can reach shell history, the general log if it is on, and the repository if the script is committed.

REQUIRE SSL refuses an unencrypted connection. Measured on MySQL 8.4.11 in a container, 2026-09-20, --ssl-mode=DISABLED against an account with it set returned ERROR 1045 (28000): Access denied. The error can look like a wrong password and send people to the credential. The same client with TLS at its default connected and reported Ssl_cipher = TLS_AES_128_GCM_SHA256.

MAX_USER_CONNECTIONS matters for agents because fan-out into subagents can let a small tool use a large share of max_connections. It is not the only limit worth setting, and a later section measures what the other one counts.

Then grant reading on one database:

GRANT SELECT ON agentdemo.* TO 'ai_agent'@'10.20.30.40';

Read it back:

SHOW GRANTS FOR 'ai_agent'@'10.20.30.40';

On MySQL 8.4.11 in a container, 2026-09-20, that returned two lines:

GRANT USAGE ON *.* TO `ai_agent`@`10.20.30.40`
GRANT SELECT ON `agentdemo`.* TO `ai_agent`@`10.20.30.40`

USAGE grants nothing. It is the row that shows the account exists, and it is always the first line.

Why SHOW GRANTS is not the evidence it looks like

The section above is not the end of the job.

A MySQL account is a (user, host) pair, and a session authenticates as exactly one of them. Database privileges are not looked up only under that pair. They are also checked against the host the connection came from, so a second account whose host pattern matches can grant privileges through the account selected for the session.

Measured on MySQL 8.4.11 in a container, 2026-09-20, with two accounts present, ai_agent@% holding SELECT ON agentdemo.* and ai_agent@127.0.0.1 holding SELECT ON otherdb.* and nothing else:

what was asked	what came back
SELECT CURRENT_USER()	ai_agent@127.0.0.1
SHOW GRANTS in that session	two lines, USAGE and otherdb
SELECT id FROM agentdemo.orders	the row

The session read a database its own grants do not mention. Revoking SELECT ON agentdemo.* from the % account turned that read into ERROR 1142, and granting it back returned the row. That identifies the % account as the source rather than something else on the server.

The manual says why:

The server looks in the db table for a match on the Host, Db, and User columns. The Host and User columns are matched to the connecting user’s host name and MySQL user name.

Access Control, Stage 2, MySQL 8.4 manual

Adding a narrower account does not remove privileges from a leftover wildcard account. Drop the old one and check for it:

SELECT user, host FROM mysql.user WHERE user = 'ai_agent';

More than one row for the same user name needs investigation.

Roles hide the same way

A role has to be activated before it takes effect. Until then, a plain SHOW GRANTS does not show the privileges it carries. Measured on MySQL 8.4.11 in a container, 2026-09-20, with a role holding SELECT ON otherdb.payroll granted to the account:

state	SHOW GRANTS in the session	the read
granted, not activated	names the role, no payroll privilege	ERROR 1142
SET DEFAULT ROLE	adds GRANT SELECT ON otherdb.payroll	returned the row
activate_all_roles_on_login = ON	role live at connect	returned the row

The line count changes with a setting that nobody changed on the account, so counting lines is not a check. Ask the session what it is:

SELECT CURRENT_USER(), USER(), CURRENT_ROLE();
SHOW GRANTS;

CURRENT_USER() is the account the server checks privileges against, while USER() is where the connection came from. They differ when host matching is involved. Root can inspect a role without activating it with SHOW GRANTS FOR 'ai_agent'@'%' USING 'extra_reader'.

The grant that fails, and does not need to exist

Every guide to this eventually reaches for metadata access:

-- This fails. It is here because it is the thing everyone tries.
GRANT SELECT ON information_schema.* TO 'ai_agent'@'10.20.30.40';

Measured on MySQL 8.4.11 in a container, 2026-09-20, that returns:

ERROR 1044 (42000): Access denied for user 'root'@'localhost'
                    to database 'information_schema'

As root. information_schema is not a grantable database, and the error names the account used to run it. That can make the problem look like a permissions issue with your account rather than a statement that cannot succeed.

It is also unnecessary. On MySQL 8.4.11 in a container, 2026-09-20, the same ai_agent account with no grant on it at all counted 88 rows in information_schema.tables where root counted 333. Neither number is a property of MySQL; they depend on what those two accounts can see on that server. information_schema filters its rows per account.

That filtering is not uniform. The INNODB_ tables are refused outright rather than filtered:

SELECT COUNT(*) FROM information_schema.innodb_trx;
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS
                    privilege(s) for this operation

An agent that can enumerate every table it is allowed to read still cannot see what is running. PROCESS is a global privilege, so granting it requires a separate decision.

Confirm it by trying to write

Reading the grants tells you what was configured. Connecting tells you what is true.

As ai_agent, reading works:

SELECT * FROM orders;

Writing does not. Measured on MySQL 8.4.11 in a container, 2026-09-20:

INSERT INTO orders VALUES (2, 'x', 1);
ERROR 1142 (42000): INSERT command denied to user 'ai_agent'@'127.0.0.1'
                    for table 'orders'

That error is the evidence. Until someone has tried to write as the account, the restriction is only assumed.

One write is not the whole test, though, because every finding on this page came from something the grants said was impossible. Run these as the account and expect the result in the second column:

as the agent	expect
SELECT CURRENT_USER(), USER(), CURRENT_ROLE()	the account you meant, and no role you did not mean
SHOW GRANTS	the lines you granted, read against the account above
SELECT from an allowed table	rows
SELECT from a table in another schema	ERROR 1142
SELECT from each view in the schema	rows only from views you audited
INSERT, UPDATE, DELETE, CREATE TABLE	ERROR 1142
CALL any routine in the schema	ERROR 1370 unless you granted EXECUTE on purpose
SELECT user FROM mysql.user	ERROR 1142
connecting with --ssl-mode=DISABLED	refused, if you set REQUIRE SSL

The mysql.user line shows what a global grant would have exposed. Measured on that same MySQL 8.4.11 container, 2026-09-20, an account with SELECT ON *.* read authentication_string for root and got values beginning $A$005$. The agent account gets ERROR 1142 ... for table 'user' instead.

-- As ai_agent this is ERROR 1142; SELECT on *.* would have allowed it.
SELECT user FROM mysql.user;

What does not protect you

transaction_read_only looks like a control but is a session variable. Measured on MySQL 8.4.11 in a container, 2026-09-20, as the ai_agent account, with no special privilege:

SET SESSION transaction_read_only = ON;   SELECT @@transaction_read_only;  -> 1
SET SESSION transaction_read_only = OFF;  SELECT @@transaction_read_only;  -> 0

Anything that can send SQL can send that. The same applies to a tool that filters statement text. It can produce a clearer error and catch an accidental write, but it does not protect the data. The privilege check does, because the server applies it to the account rather than to the text, and nothing the client sends can change it.

read_only and super_read_only are server-level settings, and read_only does not stop every account. CONNECTION_ADMIN goes through it; only super_read_only stops that account too. Measured on MySQL 8.4.11 in a container, 2026-09-20, with read_only = ON and two accounts differing only in that privilege:

account	INSERT INTO agentdemo.orders
INSERT and SELECT only	ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
the same plus CONNECTION_ADMIN	inserted, and the row read back

Root holds it, so root writes through read_only too. These settings can protect a replica but do not replace an account that cannot write.

Bounding what a read can cost

A read-only account can still run an expensive query. max_execution_time caps read-only SELECT statements, in milliseconds, and nothing else: not connection lifetime, not result size, not temporary disk, not lock waits, and not anything inside a stored program, where it is ignored.

SET SESSION max_execution_time = 30000;

Setting it globally does reach the agent. Measured on MySQL 8.4.11 in a container, 2026-09-20, SET GLOBAL max_execution_time = 2000 gave a new agent session that value, and SELECT SLEEP(5) in it returned the interrupted result rather than sleeping.

It is not a ceiling because the account can change it. In the same session, with no special privilege, the agent account set it to 0 and SELECT SLEEP(3) then ran to completion; it also set it to 99999. Treat it as a guard against an accidental long query in a client you control. Put any limit that must be enforced somewhere the account cannot change: the account’s own resource limits or a proxy in front of the server.

MAX_QUERIES_PER_HOUR is that server-enforced limit, and it counts more than the statements you send. Measured on MySQL 8.4.11 in a container, 2026-09-20:

budget	shape	where it stopped
10	one connection, ten SELECTs	nine ran, the tenth was refused
10	three connections, one SELECT each	all three ran
5	one connection per SELECT	two ran, the third was refused

Nine statements exhausted a budget of ten, so a connection costs one query in addition to what you send. An agent that opens a fresh connection per tool call uses the quota about twice as fast as its query count suggests, and the refusal is abrupt:

ERROR 1226 (42000): User 'ai_agent' has exceeded the 'max_questions'
                    resource (current value: 5)

It behaves like a circuit breaker. It does not limit rows returned, bytes, memory, or the cost of an individual query. When it trips, it takes the agent out until the hour rolls or someone runs FLUSH USER_RESOURCES. Size it so that tripping indicates a problem.

MAX_USER_CONNECTIONS is the one that limits concurrent connections. Measured on that same MySQL 8.4.11 container, 2026-09-20, seven concurrent connections against a limit of 5 gave the sixth and seventh ERROR 1226 (42000): User 'ai_agent' has exceeded the 'max_user_connections' resource (current value: 5), and the five that were already in returned their rows.

Narrowing further

Grant per table when the database contains something the agent should not read:

GRANT SELECT ON agentdemo.orders TO 'ai_agent'@'10.20.30.40';

Or per column:

GRANT SELECT (id, status) ON agentdemo.orders TO 'ai_agent'@'10.20.30.40';

Column-level grants in MySQL are additive rather than subtractive. Grant the columns you want rather than trying to take one away. Measured on MySQL 8.4.11 in a container, 2026-09-20, against an account granted (id, status) only:

statement	result
SELECT id, status FROM orders	the row
SELECT total FROM orders	ERROR 1143, naming the column
SELECT * FROM orders	ERROR 1142, naming the table

The star fails as a whole-table denial, not a column denial. That matters because SELECT * is the first thing a model writes. If you use column grants, tell the agent which columns it can read.

What a schema-wide grant actually covers

GRANT SELECT ON agentdemo.* is a standing grant on the schema, not on the tables that existed when you ran it. Two consequences, both measured on MySQL 8.4.11 in a container, 2026-09-20.

A table created afterwards is included. A new agentdemo.added_later table, created by root after the grant, was readable by the agent with no new grant issued. A migration that adds a table therefore adds it to the agent’s reach. That is the expected behaviour, but it is easy to forget.

A view reaches wherever its definer can. A view is created with SQL SECURITY DEFINER unless you say otherwise, so it runs with its creator’s privileges rather than the caller’s:

statement, as the agent	result
SELECT salary FROM otherdb.payroll	ERROR 1142
SELECT * FROM agentdemo.v_payroll, a root-defined view over that table	returned the salary

The account has no privilege on otherdb at all. A view inside the granted schema returned the data anyway. The same is true of stored routines, with one extra step: SELECT does not include EXECUTE, so CALL was ERROR 1370 (42000): execute command denied until EXECUTE was granted. The routine then ran as its definer and returned the same salary.

An allowlist of tables and purpose-built views is therefore the safer shape. Read the DEFINER and SQL SECURITY of every view in the agent’s schema before you trust the schema grant:

SELECT table_schema, table_name, definer, security_type
FROM information_schema.views
WHERE table_schema = 'agentdemo';

Read-only is authorisation, not data minimisation

An account that cannot write can still read everything it is allowed to read and send it somewhere. That is what GRANT SELECT permits, and it needs a deliberate decision:

• Which of these tables hold personal data, secrets, or another tenant’s rows?
• Does the model provider receive raw row values, and is that acceptable for these columns?
• Is there a row or tenant scope the agent should be inside, which a view can enforce and a table grant cannot?
• Is the query log good enough to say afterwards what was read?

A read model built for the agent, with the sensitive columns left out, answers all four questions and is less work than addressing them one at a time.

Rotating the credential

The account is a long-lived secret held by a process that does not sit at a keyboard, so password expiry is a poor fit: an agent hits the expiry mid-run and fails rather than prompting anybody. Rotate on a schedule you control instead. ALTER USER ... IDENTIFIED BY changes the password in one statement, so the sequence is to write the new secret to the secret manager, roll the agent, then change it on the server.

Removing it

The account is a credential, so removal should be tested rather than assumed. Drop every row for the user name, not only the one you remember creating:

SELECT user, host FROM mysql.user WHERE user = 'ai_agent';

DROP USER IF EXISTS 'ai_agent'@'10.20.30.40';

Then connect as it and expect the connection to be refused. A DROP USER that left a second row behind has the same failure as the host matching above, with the credential still live.

Why does AI-generated code cause this?

Ask for a read-only MySQL user and you usually get GRANT SELECT ON *.*, which includes the mysql database and therefore every account’s password hash. It is the shortest statement that satisfies the request, and it can look more correct than a per-database grant.

The second pattern is the information_schema grant above. It appears in many setup scripts, it cannot succeed, and the script then either fails at that line or ignores the error and continues.

How do I stop it coming back?

One account per agent, per environment. Shared credentials make the audit trail meaningless.

Check for more than one row per user name. SELECT user, host FROM mysql.user WHERE user = 'ai_agent' is the check, and a second row is what makes SHOW GRANTS misleading.

Read SHOW GRANTS in the agent’s own session, next to CURRENT_USER() and CURRENT_ROLE(), rather than as root against the account you think it is.

Re-test after adding a database. A grant on agentdemo.* says nothing about the database created next week. That is the expected behaviour, but it surprises people.

Audit every view in the agent’s schema when one is added, because a view runs as its definer and a schema grant covers it.

Point the agent at a replica where possible, so read-only is true of the server as well as the account. Remember that CONNECTION_ADMIN goes through read_only.

How DBGorilla helps

DBGorilla can help

DBGorilla works on the same principle as this page: an agent gets more useful when it can read the database, and it should not need a credential that can write. DBGorilla connects read-only and gives your AI coding agent the schema, table sizes, query plans, the workload by query fingerprint, and activity samples that carry the account behind each session. Engine coverage is listed on the product pages; check it there rather than assuming from this article’s engine. It surfaces and explains; it does not write to your database.

Start free

FAQ

• How do I create a read-only MySQL user for an AI agent?

  CREATE USER with a generated password and resource limits, then GRANT SELECT on the one database it needs. Do not grant anything on ., and do not grant on information_schema. Then connect as that user and try an INSERT: measured on MySQL 8.4.11 in a container, 2026-09-20, it comes back as ERROR 1142, which is the evidence that the grant is what you think it is.

  Why does GRANT SELECT ON information_schema. fail?*

  Because information_schema is not a grantable database. Measured on MySQL 8.4.11 in a container, 2026-09-20, the statement fails with ERROR 1044 even when run as root. It is also unnecessary: on that server the agent account with no grant on it saw 88 of the 333 tables root saw, because information_schema filters rows by what each account is privileged to see.

• Is SHOW GRANTS proof that the account is restricted?

  No, and this is the trap. Measured on MySQL 8.4.11 in a container, 2026-09-20, a session that authenticated as ai_agent@127.0.0.1, whose SHOW GRANTS listed only otherdb, read a table in agentdemo, because a separate ai_agent@% account granted it and MySQL matches database privileges against the connecting host rather than the authenticated account. Revoking the % grant stopped the read and granting it back restored it.

• Does transaction_read_only make the connection safe?

  No. It is a session variable the client controls. Measured on MySQL 8.4.11 in a container, 2026-09-20, the agent user turned it on, read the value back as 1, then turned it off and read back 0, with no special privilege. Only the absence of INSERT, UPDATE and DELETE privileges stops a write, because that is checked by the server against the account.

• How do I stop an agent opening too many connections?

  CREATE USER ... WITH MAX_USER_CONNECTIONS 5. It is enforced per account, so however many subagents spawn, the sixth connection is refused. MAX_QUERIES_PER_HOUR bounds the volume as well, which is the other way an agent gets expensive.

• Should I use a MySQL role instead of granting directly?

  Roles are worth it once more than one account needs the same privileges, because you change the role rather than every account. The cost is that a plain SHOW GRANTS stops telling you what the account can read. Measured on MySQL 8.4.11 in a container, 2026-09-20, a granted but inactive role showed as one line naming the role and nothing about the privilege it carried; activating it added the privilege line and the read succeeded. Use SHOW GRANTS ... USING the role, or read CURRENT_ROLE() in the live session.

Related

• How to set up a read-only Postgres role for an AI agent
• How to fix too many connections in MySQL