Making a MySQL table readonly to a user anywhere and for one tables only

mysql

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);