Adding a read-only MySQL user

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'