MySQL 5.6 – Get Table Sizes Without SELECT Permission

innodbMySQLmysql-5.6

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 <the query above>

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.

Best Answer

This is not quite what you asked for:

-- As root:
GRANT EXECUTE, PROCESS, REPLICATION CLIENT ON *.* TO 'se129148'@'localhost' 
    IDENTIFIED BY  'se129148';

use test;
DELIMITER //
CREATE PROCEDURE se129148 ()
    SQL SECURITY DEFINER
BEGIN
    SELECT table_name, data_length, index_length
        FROM information_schema.TABLES
        WHERE table_schema = 'test'
        LIMIT 5;
END //
DELIMITER ;

-- As se129148:
CALL se129148();

+------------+-------------+--------------+
| table_name | data_length | index_length |
+------------+-------------+--------------+
| 07         |           0 |         1024 |
| 597377b    |           0 |         1024 |
| abbrevstmt |    25739264 |      2637824 |
| adjprice   |         400 |         1024 |
| altc       |       16384 |            0 |
+------------+-------------+--------------+

Note that I had to add EXECUTE permission.

Alternatively:

-- as root:
DELIMITER //
CREATE PROCEDURE se129148b ()
    SQL SECURITY DEFINER
BEGIN
    SHOW TABLE STATUS LIKE 'abbrevstmt';
END //
DELIMITER ;

-- as se129148:
mysql> CALL se129148b();
+------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+--
--------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | A
uto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+--
--------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| abbrevstmt | InnoDB |      10 | Compact    | 53379 |            482 |    25739264 |               0 |      2637824 |   5242880 |
      5413815 | 2014-03-05 10:02:21 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+--
--------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> SHOW GRANTS;
    | Grants for se129148@localhost
    | GRANT EXECUTE, REPLICATION CLIENT ON *.* TO 'se129148'@'localhost' IDENTIFIED BY PASSWORD '...' |

Note: EXECUTE, not PROCESS.

Related Question