MySQL Bugs: #40050: agent user permissions are different for MySQL 5.0 and 5.1

Bug #40050	agent user permissions are different for MySQL 5.0 and 5.1
Submitted:	15 Oct 2008 13:33	Modified:	11 Dec 2008 14:07
Reporter:	Axel Schwenke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Documentation	Severity:	S3 (Non-critical)
Version:	1.3, 2.0	OS:	Any
Assigned to:	Kay Roepke	CPU Architecture:	Any

Description:
The MEM documentation here https://enterprise.mysql.com/docs/monitor/1.3/en/mem-install-agent.html#mem-agent-rights and here: https://enterprise.mysql.com/docs/monitor/2.0/en/mem-install.html#mem-agent-rights
lists the SELECT, REPLICATION CLIENT, SHOW DATABASES and SUPER privileges as necessary for the agent account. In MySQL 5.1 the agent user needs the PROCESS privilege in order to execute SHOW ENGINE INNODB STATUS (but probably the SUPER privilege is not needed anymore).

How to repeat:
Setup an agent to monitor a 5.1 server. There will be no stats for InnoDB unless the agent user is granted the PROCESS privilege.

Suggested fix:
Extend the documentation.

I wonder if there is a bug in the agent, too. If the agent user has insufficient privileges, the SHOW ENGINE INNODB STATUS statement fails with an error "you need the PROCESS privilege for this". But the agent does not log this error. Instead it logs a bunch of "regex not matched" messages.

I checked once more the agent log for time periods with insufficient permissions. In fact there is nothing in the logs. But IMNSHO there should be a message about failed SHOW ENGINE INNODB STATUS.

With log-level raised to message, the agent logs a line

2008-10-15 16:22:16: (message) mysql-query (127.0.0.1:3306): SHOW /*!40100 ENGINE */ INNODB STATUS

but there is no information that this statement actually failed with a permission error.

We currently log the message at "warning" level, which seems appropriate.
In 5.1.24 SHOW INNODB STATUS needs the global PROCESS privilege (which is the "monitoring" privilege).

Hence, to work properly, the agent needs to have at least PROCESS.

We (Jan, Kay, myself), would like to expose what privileges the agent user has as data collection items, which can then have rules written against them.

For instance, the agent would do a SHOW GRANTS on it's connection, and then check whether it has the PROCESS privilege, and report this back up to the service manager as "has_process_priv" with 1/0 (true/false). We would then write a rule that describes why the PROCESS privilege is required for the agents, and check whether this is broken to raise an alert, rather than having to check the log files for each agent. 

This both raises the awareness that there is an issue within the UI (where people will be looking by default anyway), as well as allowing us to do this centrally for any number of agents that might be affected.

The agent now logs errors resulting from SHOW INNODB STATUS on the highest log level possible (namely critical).

Verified fixed on agent 2.0.0.7111

We now see on the agent's log file

(critical) executing 'SHOW /*!50000 ENGINE */ INNODB STATUS' failed: Access denied; you need the PROCESS privilege for this operation

A note has been added to the changelog, and the documentation has been updated with the requirement for SUPER.