Adding a read-only MySQL user

Instructions on how to set up and test a read-only account in MySQL.

Sometimes you may need to let a user have access to your MySQL database, for example for viewing data directly during testing or for running reports, but you do not want these users to update data or make schema changes.

It is actually quite easy to set up a read-only account in MySQL using the GRANT statement, which is ideal for these purposes.

Adding the new MySQL user

Connect to your database as root, then add your new user like so:

CREATE USER 'tester'@'%' IDENTIFIED BY 'password';

The % here means the user 'tester' connecting from any host, you can place a network hostname here instead if you want to restrict access further. Naturally you will also want to substitute password with something a little stronger ;-)

Now run the following to grant the SELECT privilage to the new user on all databases:

GRANT SELECT ON *.* TO 'tester'@'%';

Or if you want to restrict access to only one database:

GRANT SELECT ON DATABASE.* TO 'tester'@'%';

Confirming that it worked

Connect as the new user:

mysql -u tester -p

And now see if you can update a record:

mysql> USE alpha;
mysql> UPDATE Person SET version_num = 5 WHERE OID = 1;
ERROR 1142 (42000): UPDATE command denied TO USER 'tester'@'localhost' FOR TABLE 'Person'

John Collins

I have been writing about web technology and software development since 2001. I am the developer of the Alpha Framework for PHP, and the five.today personal productivity app. I love open source, technology, and economics.