MySQL Bugs: #43701: Incomplete error message: Slave Has Login Accounts With Inappropriate Privileges

Bug #43701	Incomplete error message: Slave Has Login Accounts With Inappropriate Privileges
Submitted:	17 Mar 2009 13:32	Modified:	20 Jul 2009 14:43
Reporter:	Roger David Nay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Advisors/Rules	Severity:	S3 (Non-critical)
Version:	Any	OS:	Any
Assigned to:	Andy Bang	CPU Architecture:	Any

Description:
I received the following alert from MySQL Enterprise Monitor:

====
Server: slave-01
Time: 2009-03-17 12:00:04 GMT
Advisor: Replication - Slave Has Login Accounts With Inappropriate Privileges

Problem Description
Altering and dropping tables on a slave can break replication. Unless the slave also hosts non-replicated tables, there is no need for accounts with these privileges.

Advice
Revoke the global ALTER and DROP privileges from the following accounts on server slave-01 unless they are absolutely necessary:

user_1@localhost,
user_2@localhost

Recommended Action
REVOKE ALTER, DROP ON *.* FROM user_name@host_name;
FLUSH PRIVILEGES;

=======

Problems with this advice:
1. The server is configured read_only so unless a user has SUPER privilege he can not DROP or ALTER tables.

2. MySQL grants are currently replicated from the master therefore it is not entirely surprising that they also exist on the slave.
again read_only ensures the slave can not be changed.

How to repeat:
N/A

Suggested fix:
Please consider adjusting the recommendation to take these possible facts into account when making recommendations. An additional line relating to read_only slaves or checking if read_only global variable is set and modifying the recommendation accordingly.

Andy Bang writes: 
Added "&& (%read_only% == "OFF")" to the expression so the rule will only fire if read_only is OFF.

Added a reference the read_only variable in the description, advice, command, and info sections.

Pushed up to revision 279.

Keith Russell writes: 
Patch installed in versions => 2.1.0.1076.

Marcos Palacios writes: 
Verified fixed in advisor bundle 2.1.0.1076.

An entry was added to the 2.1.0 changelog:

The advisor “Replication - Slave Has Login Accounts With Inappropriate Privileges” contained inappropriate advice information.

The advice message generated was:

Server: slave-01
Time: 2009-03-17 12:00:04 GMT
Advisor: Replication - Slave Has Login Accounts With Inappropriate Privileges

Problem Description
Altering and dropping tables on a slave can break replication. Unless the slave also hosts
non-replicated tables, there is no need for accounts with these privileges.

Advice
Revoke the global ALTER and DROP privileges from the following accounts on server slave-01
unless they are absolutely necessary:

user_1@localhost,
user_2@localhost

Recommended Action
REVOKE ALTER, DROP ON *.* FROM user_name@host_name;
FLUSH PRIVILEGES;

However, the problems with this advice were:

1. The server was configured read_only so it would not be possible to DROP or ALTER tables unless the user had SUPER privilege.

2. MySQL grants were replicated from the master and therefore appeared on the slave. Also, read_only ensured the slave could not be changed.