# 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 ```sql 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: ```sql 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: ```sql GRANT SELECT ON agentdemo.* TO 'ai_agent'@'10.20.30.40'; ``` Read it back: ```sql 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](https://dev.mysql.com/doc/refman/8.4/en/request-access.html) **Adding a narrower account does not remove privileges from a leftover wildcard account.** Drop the old one and check for it: ```sql 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: ```sql 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: ```sql -- 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: ```sql 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. ```sql -- 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. ```sql 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 `SELECT`s | 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: ```sql GRANT SELECT ON agentdemo.orders TO 'ai_agent'@'10.20.30.40'; ``` Or per column: ```sql 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: ```sql 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: ```sql 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 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. [Get started free →](https://app.dbgorilla.com/signup) ## Related - [How to set up a read-only Postgres role for an AI agent](https://www.dbgorilla.com/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/) - [How to fix too many connections in MySQL](https://www.dbgorilla.com/learn/mysql/how-to-fix-too-many-connections-in-mysql/)