List of tables for which the user has SELECT privilege for MySQL

Question

Short version . How to write an SQL procedure to indicate which of several tables in a MySQL database a particular user has?

Longer version :

I am writing a multi-user application that accesses a database with data for several branches of the company. There are several lookup tables in the database that can be accessed by any user, and a table for each branch that only authorized users can access. My strategy:

• Write a stored procedure that returns a list of matching tables for which the user has SELECT privileges.
• In the application, call the procedure. If only one table is returned, use it; otherwise, let the user choose which branch they want to access (for example, for managers).

I find it difficult to understand how to write such a stored procedure. SHOW GRANTS FOR CURRENT_USERis an obvious possibility, but parsing something like:

GRANT SELECT ON Company.BranchABC TO 'auser'@'%clientdomain.com'

in SQL to understand that tables look too confusing. Executing SELECTfrom actual tables containing permissions also seems problematic because I have to duplicate MySQL logic to combine permissions from different tables (user, db, host, etc.).

Any words of wisdom?

Answer 1

MySQL , TABLE_PRIVILEGES information_schema.

.

Answer 2

MySQL .

select * from mysql.user\G;

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