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:
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?
I have never granted per table permissions to MySQL users before, but to do this, you would check that the TABLE_PRIVILEGES
table in the information_schema
database.
That should point you in the right direction.