Create a Read-Only User for MySQL

I need a read-only user to monitor the state of a MySQL databases. This can be done by simply granting only the select privilege on a particular database to a given user.

You can check what MySQL users have already been created with the following:

mysql> SELECT host,user,password FROM mysql.user;

Here I create a user called readonly with a password of ‘9qA%z88Yr1Lj‘.

MariaDB [(none)]> CREATE USER 'readonly'@'localhost' IDENTIFIED BY '9qA%z88Yr1Lj';
Query OK, 0 rows affected (0.015 sec)

Grant select privilege:

MariaDB [(none)]> GRANT SELECT ON *.* TO 'readonly'@'localhost' IDENTIFIED BY '9qA%z88Yr1Lj';
Query OK, 0 rows affected (0.000 sec)

Don’t forget to flush:

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.005 sec)