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:
Category:MySQL Enterprise Monitor: Documentation Severity:S3 (Non-critical)
Version:1.3, 2.0 OS:Any
Assigned to: Kay Roepke CPU Architecture:Any

[15 Oct 2008 13:33] Axel Schwenke
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.
[15 Oct 2008 13:47] Axel Schwenke
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.
[15 Oct 2008 14:19] Axel Schwenke
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 ( SHOW /*!40100 ENGINE */ INNODB STATUS

but there is no information that this statement actually failed with a permission error.
[3 Nov 2008 12:29] Kay Roepke
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.
[3 Nov 2008 14:22] Mark Leith
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.
[12 Nov 2008 16:24] Kay Roepke
The agent now logs errors resulting from SHOW INNODB STATUS on the highest log level possible (namely critical).
[10 Dec 2008 21:19] Diego Medina
Verified fixed on agent

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
[11 Dec 2008 14:07] MC Brown
A note has been added to the changelog, and the documentation has been updated with the requirement for SUPER.