Create a read-only MySQL user

A read-only MySQL user is the cheapest way to let analysts, support staff, or BI tools query production without ever risking a write. The grants below are the minimum to read tables and views.

minimum viable read-only user

CREATE USER 'analyst'@'%' IDENTIFIED BY '<password>';
GRANT SELECT, SHOW VIEW ON `mydb`.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

SELECT allows reading from tables. SHOW VIEW is required to read the definition of any view the user queries — without it, querying a view fails even when SELECT would otherwise succeed.

Useful add-ons

Read-only often means more than SELECT. The grants you usually want layered in:

Grant	What it enables	When to add it
`SHOW VIEW`	Reading view definitions and querying views	Whenever the schema contains views (almost always)
`EXECUTE`	Calling stored procedures and functions	Only if procs return data the user needs
`PROCESS`	Seeing other connections in `SHOW PROCESSLIST`	For troubleshooting users; risky on shared databases
`REPLICATION CLIENT`	Reading replica lag / binlog position	For monitoring tools

analyst with the common extras

GRANT SELECT, SHOW VIEW, EXECUTE
  ON `mydb`.*
  TO 'analyst'@'%';

Scope: one database vs all databases

The pattern mydb.* limits the grant to a single database. To grant read on every database (typical for a DBA-style read-only role):

GRANT SELECT, SHOW VIEW ON *.* TO 'analyst_all'@'%';

Beware: *.* includes the mysql system database, which contains password hashes. For an analyst account, prefer enumerating the specific databases:

GRANT SELECT, SHOW VIEW ON `app_prod`.*    TO 'analyst'@'%';
GRANT SELECT, SHOW VIEW ON `app_metrics`.* TO 'analyst'@'%';

Verifying and managing

audit the grants on a user

SHOW GRANTS FOR 'analyst'@'%';

rotate the password

ALTER USER 'analyst'@'%' IDENTIFIED BY '<new-password>';

revoke a specific permission

REVOKE EXECUTE ON `mydb`.* FROM 'analyst'@'%';

remove the user entirely

DROP USER 'analyst'@'%';

SHOW GRANTS is the only canonical source of truth — re-derive the list of grants from it rather than from your memory of what you ran six months ago.

What to remember

Two grants — SELECT and SHOW VIEW — cover the common case; layer in EXECUTE only if needed. Constrain the host part to the smallest reasonable network range. Audit with SHOW GRANTS, rotate with ALTER USER, and never grant *.* to a human account because *.* includes mysql.user.