Limiting Access To Specific Tables in MySQL - Cheatsheat

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

Max Chadwick Hi, I'm Max!

I'm a software developer who mainly works in PHP, but loves dabbling in other languages like Go and Ruby. Technical topics that interest me are monitoring, security and performance. I'm also a stickler for good documentation and clear technical writing.

During the day I lead a team of developers and solve challenging technical problems at Rightpoint where I mainly work with the Magento platform. I've also spoken at a number of events.

In my spare time I blog about tech, work on open source and participate in bug bounty programs.

If you'd like to get in contact, you can find me on Twitter and LinkedIn.