It is very common to see a grant statement like the following which gives access to all of the tables in a given database.
GRANT SELECT, SHOW VIEW
ON mydatabase.*
TO myreaduser@myhost IDENTIFIED BY 'somepassword';
FLUSH PRIVILEGES;
Where mydatabase, myreaduser, myhoost, and somepassword are the appropriate variables for your database. Note that the FLUSH PRIVILEGES command resets MySQLs privileges and you won’t be able to use the new user grants until it is run.
Such a user will be able to read and access all of the tables in a database. For this example we’re going to use the boatio database which has 3 tables: boats, trips, and users.
mysql> show tables;
+------------------+
| Tables_in_boatio |
+------------------+
| boats |
| trips |
| users |
+------------------+
3 rows in set (0.00 sec)
If we would like to create a user that only has access to the trips table, we simply replace the wildcard splat (*) which represents all tables, with the specific tables you’d like (in this case: trips).
GRANT SELECT, SHOW VIEW
ON boatio.trips
TO myreaduser@localhost IDENTIFIED BY 'somepassword';
FLUSH PRIVILEGES;
Now we can log in as the new user and run show tables to see that it only has access to the trips table and not the other two. This new user simply doesn’t know the other tables even exist.
$ mysql -umyreaduser boatio -psomepassword
mysql> show tables;
+------------------+
| Tables_in_boatio |
+------------------+
| trips |
+------------------+
1 row in set (0.00 sec)
To give the user access to more tables, simply re-run the same GRANT statement with the additional table names. For example, the following will grant access to the trips and users tables but not the boats.
GRANT SELECT, SHOW VIEW ON boatio.trips TO myreaduser@localhost IDENTIFIED BY 'somepassword';
GRANT SELECT, SHOW VIEW ON boatio.users TO myreaduser@localhost IDENTIFIED BY 'somepassword';
FLUSH PRIVILEGES;
And the proof: the user now has access to the trips and users tables but not boats.
$ mysql -umyreaduser boatio -psomepassword
mysql> show tables;
+------------------+
| Tables_in_boatio |
+------------------+
| trips |
| users |
+------------------+
2 rows in set (0.00 sec)