Read access privs to mysql information_schema.{TABLES,SCHEMATA}

[kloczek]

Hi,

I'm working on mysql (5.6) monitoring.
One of the things which I'm going to implement is summary monitoring of all data and indexes size per database.
On testing obtaining exact data I can form query which is working from root user but is not working from my test monitoring user.

$ mysql -uroot -sNe "select SUM(DATA_LENGTH) from information_schema.TABLES where TABLE_SCHEMA='test';"
3046817792

$ mysql -umonitoring -sNe "select SUM(DATA_LENGTH) from information_schema.TABLES where TABLE_SCHEMA='test';"
ERROR 1045 (28000): Access denied for user 'monitoring'@'localhost' (using password: NO)

The same is with access to information_schema.SHEMATA which contains list of databases.

So far monitoring user has granted only USAGE privs:

$ mysql -uroot -sNe "SHOW GRANTS FOR 'monitoring'@'localhost';"
GRANT USAGE ON *.* TO 'monitoring'@'localhost' IDENTIFIED BY PASSWORD '<pwd_hash>'

With USAGE priv I can access to all other things but seems on access to information_schema.{TABLES,SCHEMATA} needs to be granted more.

Can someone give a hint what I need to grant to monitoring@localhost account to make above working?

[kloczek]

I found all necessary details.
Minimal set of grants are:

• SELECT, INDEX *.* - to open access to information_schema.TABLES
• SHOW DATABASES - to open access to information_schema.SHEMATA

To above could be added REPLICATION CLIENT and REPLICATION SLAVE if someone is considering monitoring of the slave(s) engine(s).

What bothers me is that INDEX allows create indexes.
Seems like mysql has no well separated/defined privs to allow monitor databases without R/W access