Limiting Access To Specific Tables in MySQL - Cheatsheat

Published: September 6, 2018

Tags:

MySQL

One of the core principles in the infosec field is the principle of least privilege. The idea is to limit permitted access by systems or processes as much as humanly possible. Applied to MySQL, in some circumstances this could mean only allowing access to specific tables for some user. This is a quick cheatsheet for working with table-level access in MySQL.

Granting Access to Specific Tables

Granting access to specific tables can be done via a GRANT statement. Here’s an example…

GRANT SELECT ON db.table TO 'user'@'localhost';

Showing Table Grants

The easiest way to see table level grants is via a SHOW GRANTS statement

SHOW GRANTS FOR 'user'@'localhost'
+-------------------------------------------------------------------------------------------------------------+
| Grants for user@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT ON `db`.`table` TO 'localhost'                                                                 |
+-------------------------------------------------------------------------------------------------------------+

Some Googling on this may lead you to check mysql.user, but you won’t find table level permissions there.

Revoking Access To Specific Tables

If you make a mistake in your granting or later find that a user no longer needs access a REVOKE statement can be used to remove access to specific tables…

REVOKE SELECT ON db.table FROM 'user'@'localhost';