How to create a read-only MySQL user (step by step)
When a reporting tool, a spreadsheet, a monitoring service or an AI assistant needs to look at your MySQL database, give it its own user that can read but not change anything. It takes two commands, and it protects the data your business runs on from mistakes and misbehaving software.
Please don't reuse the root user, or the user your shop system or website uses. Those can change and delete data.
This guide works for MySQL 5.7 and 8.x, and for MariaDB.
What you need
- A way to run SQL on the server: the
mysqlcommand line, MySQL Workbench, phpMyAdmin, or the database tool in your hosting panel. - A user that is allowed to create users, usually
rootor your hosting panel's admin user. You use it once, to set things up. - The name of the database your business system uses.
- The address of the computer that will connect, or at least your office network range (for example
192.168.1.x).
Step 1: Create the user
MySQL users are tied to where they connect from. Choose the line that fits:
-- The tool runs on the same computer as MySQL
CREATE USER 'cuedis_reader'@'localhost' IDENTIFIED BY 'Choose-A-Long-Password-1';
-- The tool runs on another computer in your office network (192.168.1.anything)
CREATE USER 'cuedis_reader'@'192.168.1.%' IDENTIFIED BY 'Choose-A-Long-Password-1';
Avoid 'cuedis_reader'@'%' (any address) unless you have a good reason. Limiting the address is a cheap extra layer of protection.
Step 2: Allow it to read one database
Replace shop_db with your database's name, and use the same 'user'@'host' as in step 1:
GRANT SELECT, SHOW VIEW ON shop_db.* TO 'cuedis_reader'@'192.168.1.%';
SELECTallows reading every table in that database.SHOW VIEWallows seeing how views are written, which helps tools that want to understand your existing reports.shop_db.*limits it to that one database. The user cannot see any other database on the server.
There is no need for FLUSH PRIVILEGES after GRANT. MySQL applies it straight away.
If your business data is in several databases, run the GRANT once for each.
Step 3: Check that it really is read-only
Log in as the new user and try a read and a write:
mysql -u cuedis_reader -p -h 127.0.0.1 shop_db
SELECT * FROM sales LIMIT 5; -- should work
UPDATE sales SET amount = amount; -- should fail
The UPDATE should be refused with an error like:
ERROR 1142 (42000): UPDATE command denied to user 'cuedis_reader'@'localhost' for table 'sales'
You can also list exactly what the user is allowed to do:
SHOW GRANTS FOR 'cuedis_reader'@'192.168.1.%';
You should see only USAGE (which means "may log in") and SELECT, SHOW VIEW on your database.
Step 4: Let other computers connect
If the tool runs on the same computer as MySQL, you are done. If it runs on another computer on your network:
- MySQL must listen on the network. In the MySQL configuration file (
my.cnfon Linux and Mac,my.inion Windows), thebind-addresssetting is often127.0.0.1, which means "this computer only". Change it to the server's network address (or0.0.0.0on a server that is only on your office network), then restart MySQL. - The firewall must allow port 3306 from your office network.
- Never open port 3306 to the internet. If your database is with a hosting company, ask them how to allow a single address instead. Most hosting panels have a Remote MySQL page for this.
A note on stored procedures
SELECT does not allow running stored procedures. That is a good default for a read-only user. If a tool really needs to call a procedure that only reads, you can allow that one procedure:
GRANT EXECUTE ON PROCEDURE shop_db.daily_sales_report TO 'cuedis_reader'@'192.168.1.%';
Only do this for procedures you know do not change data. A procedure runs with the rights of whoever created it, so EXECUTE on the wrong procedure can bypass the read-only protection.
Changing the password or removing the user
-- New password
ALTER USER 'cuedis_reader'@'192.168.1.%' IDENTIFIED BY 'A-New-Long-Password-2';
-- Remove the user completely
DROP USER 'cuedis_reader'@'192.168.1.%';
Why bother
A read-only user is the simplest strong protection you can put between your data and any tool you connect to it. It does not rely on the tool being well written, and anyone can check it with SHOW GRANTS.
Well-behaved tools add their own safeguards on top. Cuedis, for example, opens every MySQL session in read-only mode, so even a login with more rights could not change anything through it. But a safeguard inside a tool is only as good as that tool. The read-only user protects you whatever you connect, so start here.