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 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

[17 Mar 2009 13:32] Roger Nay
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.
[9 Jul 2009 0:17] Enterprise Tools JIRA Robot
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.
[13 Jul 2009 13:42] Enterprise Tools JIRA Robot
Keith Russell writes: 
Patch installed in versions => 2.1.0.1076.
[13 Jul 2009 19:02] Enterprise Tools JIRA Robot
Marcos Palacios writes: 
Verified fixed in advisor bundle 2.1.0.1076.
[20 Jul 2009 14:43] Tony Bedford
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.