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 15:33	Modified:	11 Dec 2008 15:07
Reporter:	Axel Schwenke
Status:	Closed
Category:	Monitoring: Docs	Severity:	S3 (Non-critical)
Version:	1.3, 2.0	OS:	Any
Assigned to:	Kay Roepke	Target Version:	2.0 RC-02
Triage:	Needs Triage: D2 (Serious) / R1 (None/Negligible) / E2 (Low)

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.