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

• Akina: Create a procedure which retrieves needed data with SQL SECURITY DEFINER. Grant the user in question EXECUTE privilege to this procedure.
• tombom: The data in information_schema are only estimates, in my experience they can even be horribly wrong and not relied on. I solved this by reading the actual directory sizes with a script that telegraf executes and sends its output to influxdb/grafana
• Rick James: See if you can do it via SHOW TABLE SIZE LIKE 'table_name';
• Rick James: @tombom - The values for Data/Index_length/free add up to the disk space used. The Rows and Avg_row_size are estimates.
• tombom: @RickJames That's not true. The manual clearly states that those are "approximate" values: dev.mysql.com/doc/refman/8.0/en/tables-table.html And when you put it to the test, the values are off by more than 10%

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.

• Jesus Uzcanga: Happy to help. If you think that this is the correct answer to your question, please mark it and vote up.