MySQL least privileges to get database/tables size

Question

I'm using centreon_plugins to monitor some mysql databases. As a best practice I create a dedicated user for monitoring and I give it the least possible privileges.

For simple monitoring I usually create a monitor user with only 2 global privileges:

mysql> create user 'monitor'@'%' identified by 'monitorpassword';
mysql> grant SHOW DATABASES, REPLICATION CLIENT on *.* to 'monitor'@'%';
mysql> flush privileges;

Now I'm required to check database size. To access DB size I need that the user monitor can access information_schema.tables, but I cannot give it direct select privileges to that table.

Obviously the root user I'm using has the GRANT privilege.

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant SELECT on information_schema.tables to 'monitor'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

The access denied is due to the particular use that mysql reserves to the information_schema DB; the access to that DB is limited by the granted access to all other mysql objects. I could achive the result giving the SELECT privilege on *.* to monitor user, but I don't want to do that because the monitor user is not protected enough (i.e. it's password is stored in cleartext in the monitoring software) and so it must not be able to see application data.

I tried various combination of privileges on different DBs and tables and none of them works. (ref. https://dev.mysql.com/doc/refman/5.7/en/grant.html)

Did someone had the same need? How did he/she resolved it?

For completeness, the queries executed by the monitoring plugin are:

show variables like 'innodb_file_per_table'
SELECT table_schema, table_name, engine, data_free, data_length+index_length as data_used, (DATA_FREE / (DATA_LENGTH+INDEX_LENGTH)) as TAUX_FRAG FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND engine IN ('InnoDB', 'MyISAM')

Thanks in advance. Flavio

Answer 1

This is a little confusing but the minimum privileges you need to check a table size is to have the privileges to SELECT that table. So if you want to know the whole DB size you need privileges to SELECT every single table in that DB.

This is why:

1. There is no way to read a DB size without reading its tables' sizes
2. The command to read a table size (no querying information_schema) is SHOW TABLE STATUS table_name
3. SHOW TABLE STATUS derives from SHOW TABLES, so the privileges and rules are similar.
4. Finally, the official documentation about SHOW TABLE: https://dev.mysql.com/doc/refman/8.0/en/show-tables.html

   If you have no privileges for a base table or view, it does not show up in the output from SHOW TABLES or mysqlshow db_name.

So the minimum access to check DB sizes is pretty invasive because you need to grant SELECT privileges to all tables.