Get table sizes without SELECT permission I want a scheduler to run a simple SQL script to collect the sizes of tables in our MySQL database:

SELECT table_name, data_length, index_length
FROM information_schema.TABLES


The user the scheduler logs in as has very limited privileges:

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'our-user'@'%' 
IDENTIFIED BY PASSWORD '...'


This is intentional - we don't want the user to access any data; only metadata such as table sizes.

From the MySQL manual I thought those permissions should suffice. In particular, that's what it has to say about information_schema.* tables: 


  Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges.


but then adds


  These restrictions do not apply for InnoDB tables; you can see them with only the PROCESS privilege.


Our tables are InnoDB tables.

Still, when running the script under our-user, I only get the system tables (TABLES, VIEWS, INNODB_*) listed. The same script does show our user tables when run by a different user with SELECT permission for our tables.

From the manual it also follows that VIEWS can be run with the privileges of creator, so I tried wrapping my query in a view:

CREATE DEFINER = CURRENT_USER SQL SECURITY DEFINER 
VIEW view_table_sizes AS &lt;the query above&gt;


However, when this view is SELECTed from by our-user, only the system tables are returned. Needless to say, the view was created by a privileged user.
Create an EVENT that fires every 15 minutes. Here is a tutorial.

The EVENT could be used to populate a table with the necessary information.

Then GRANT SELECT on that table and only that table to your user.