There are some ways to ensure that MySQL tables are setup for readonly users with only certain access. This short snippet will explain the best way to do this.

mysql> CREATE USER ro_user;
Query OK, 0 rows affected (0.04 sec)

mysql> SET PASSWORD FOR ro_user = PASSWORD ('onionbag');
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON somedb.sometable TO ro_user;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE user SET Show_db_priv='Y' WHERE User='ro_user';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)


The above will create a default entry (‘%’) for all hosts, but we also want to ensure we add one for localhosts.


insert into user values ('localhost','ro_user','Y0UR_ENCRYTPED_PASSW0RD','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0);