MySQL Table Level Permissions

Data Tutorial

It is very common to see a grant statement like the following which gives access to all of the tables in a given database. sql 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. sql 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). sql 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. sql $ 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. sql 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. ```sql $ mysql -umyreaduser boatio -psomepassword

mysql> show tables; +——————+ | Tables_in_boatio | +——————+ | trips | | users | +——————+ 2 rows in set (0.00 sec) ```