MySQL Read-Only User for Reports and Dashboards

Reporting tools, dashboards and analysts usually only need to read. A read-only MySQL user lets them query everything they need while making accidental (or malicious) changes impossible.

Related guide: Full MySQL users and permissions guide

1. Create the user

Use a separate account for each tool so you can revoke one without breaking the others.

CREATE USER 'reports'@'localhost' IDENTIFIED BY 'a-long-random-password';

2. Grant SELECT only

Read access to every table in one database.

GRANT SELECT ON appdb.* TO 'reports'@'localhost';

3. Optional: only some tables

Limit it further to the tables a dashboard needs.

GRANT SELECT ON appdb.orders TO 'reports'@'localhost';
GRANT SELECT ON appdb.customers TO 'reports'@'localhost';

4. Limit its load

Stop a runaway report from hogging the server.

ALTER USER 'reports'@'localhost' WITH MAX_USER_CONNECTIONS 5;

5. Verify it cannot write

This must fail with a command denied error.

mysql -u reports -p appdb -e "DELETE FROM orders LIMIT 1;"

Troubleshooting

ERROR 1045 Access denied: check the user name, the host part ('app'@'localhost' and 'app'@'%' are different accounts) and the password. ERROR 1044: the user has no rights on that database yet. See exactly what an account can do with SHOW GRANTS.

SHOW GRANTS FOR 'appuser'@'localhost';
SELECT user, host, plugin FROM mysql.user;

FAQ

Can a read-only user still see sensitive data?

Yes, everything in the tables you grant. Grant only the tables or views it needs, or create views that leave out sensitive columns.

Is a read replica better for heavy reports?

For heavy reporting, yes: point the read-only user at a replica so reports never slow down the live database.

Further reading: DigitalOcean: create a new user and grant permissions in MySQL