List of tables that a user has SELECT privilege for in MySQL
Short version: How can I write an SQL procedure to list which of several tables in a MySQL database a particular user has access to?
Longer version:
I'm writing a multi-user app that accesses a database with data for several branches of a company. The database has a number of lookup tables that any user can access, and a table for each branch that only authorized users can access. My strategy is:
- Write a stored procecure that returns a list of the relevant tables for which the user has SELECT privilege.
- From the app, call the procedure. If there's only one table returned, use it, otherwise let the user select which branch they want to access (e.g. for managers).
I'm having trouble figuring out how to write such a stored procedure. SHOW GRANTS FOR CURRENT_USER is an obvious possibility, but parsing something like:
GRANT SELECT ON Company.BranchABC TO 'auser'@'%clientdomain.com'
in SQL to figure out what the tables are seems way too messy. Doing a SELECT from the actual tables that hold the permissions also seems problematic, because I'd have to duplicate MySQL's logic for combining the permissions from the various tables (user, db, host, etc.)
Any words of wisdom?
Answer
You can see what privileges on which tables a user has:
show grants for 'user'@'host';
For example, to see the privileges of user1 (all machines in the network 10.25), run:
show grants for 'user'@'10.25.%.%';