Limiting Access To Specific Tables in MySQL - Cheetsheat

Published: September 6, 2018

Tags:

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 [email protected]                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| 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';

Max Chadwick Hi, I'm Max!

I'm a software developer who mainly works in PHP, but also dabbles in Ruby and Go. Technical topics that interest me are monitoring, security and performance.

During the day I solve challenging technical problems at Something Digital where I mainly work with the Magento platform. I also blog about tech, work on open source and hunt for bugs.

If you'd like to get in touch with me the best way is on Twitter.