List of tables that a user has SELECT privilege for in MySQL

Question

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 1

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.

Answer 2

MySQL users list and its privilege can be check with the Query.

select * from mysql.user\G;

http://www.devdescent.com/2012/04/retrieve-mysql-users-list-and-its.html

Answer 3

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