MySQL Bugs: #78345: mysqlrpladmin requires excessive GRANTs and permissions

Bug #78345	mysqlrpladmin requires excessive GRANTs and permissions
Submitted:	5 Sep 2015 22:33	Modified:	20 Jan 2016 8:01
Reporter:	monty solomon	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Utilities	Severity:	S2 (Serious)
Version:	1.5.4, 1.5.6, 1.6.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysqlrpladmin requires excessive GRANTs and permissions to exist on the servers for the mysqlrpladmin user for some operations.

The documentation does not explain why each GRANT is needed.

mysqlrpladmin checks for GRANTs for the superset of all operations it may run instead of the specific operation being used.

Why does mysqlrpladmin require GRANT OPTION if the replication user already exists?

Why does mysqlrpladmin require SELECT and INSERT privileges on *.* ? Does mysqlrpladmin update tables other than tables in the mysql database?

Why does mysqlrpladmin need CREATE and DROP privileges on *.* ? When does mysqlrpladmin CREATE and/or DROP tables?

When executing the switchover operation mysqlrpladmin looks for the following GRANTs and fails to run if they aren't present even when it doesn't need them.

GRANT CREATE, DROP, INSERT, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SUPER ON *.* TO 'SUSR_rpladmin'@'%' IDENTIFIED BY PASSWORD '*redacted' WITH GRANT OPTION;

How to repeat:
Run the mysqlrpladmin utility using a user that does not have all of the specified GRANTs in the documentation and get an error.

# Checking privileges.
User SUSR_rpladmin on lively-haze does not have sufficient privileges to execute the switchover command.
User SUSR_rpladmin on noisy-pond does not have sufficient privileges to execute the switchover command.
ERROR: Not enough privileges to execute command.

Suggested fix:
Update mysqlrpladmin and the other utilities to only require GRANTs that are actually needed. For example, mysqlrpladmin should not require GRANT OPTION if the replication user already exists.

Explain fully in the documentation why each GRANT is needed and what it is used for.

mysqlrpladmin needs UPDATE on the mysql users table. That provides it with more privilegesand capabilities than the GRANT OPTION.

Verified as described.

Posted by developer:
 
Please fix source code to require only necessary permissions, e.g. SELECT INSERT CREATE DROP on mysql schema only. Reflect in documentation any non-obvious privileges requirements.

Posted by developer:
 
This bug and others like it are in contrast to some of the design goals of the utilities themselves. The permissions checking code was designed and implemented to provide the broadest possible application to avoid becoming tedious to use (requiring specific, seldom used privileges). That is, while some operations can be performed with specific privileges, we chose to use more frequently used, broader privileges rather than the minimal set. Thus, users unfamiliar with the finer points of MySQL server security can use the utilities with minimal effort. 

Therefore, the solution for this bug and others like it may not completely satisfy the specifics of the requests herein. Rather, effort shall be made to reduce the confusion and eliminate cases where over privilege is clear as well as clarify the use of those privileges in the documentation.

In addition, it is clear that the privilege checking process for mysqlrpladmin is in need of some refactoring for finer grain privilege checking as noted in the bug report. More specifically, there should be a separate check for privileges for each command and its variants (choice of options) rather than a single check for groups of commands. This should help clear up the confusion and remove some of the over privilege requirements.