MySQL Bugs: #81847: What privileges are needed to START SLAVE and STOP SLAVE?

Bug #81847	What privileges are needed to START SLAVE and STOP SLAVE?
Submitted:	14 Jun 2016 11:40	Modified:	25 Apr 2019 14:33
Reporter:	Sheeri Cabral (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6 and 5.7, 5.7.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	privileges

Description:
According to https://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html and the 5.7 manual page, CHANGE MASTER TO needs the SUPER privilege. But there's no documentation on what is needed for the START SLAVE and STOP SLAVE privileges. 

How to repeat:
mysql> grant SELECT on *.* to sheeri@localhost identified by 'PASSWORD';

(login as user sheeri)

mysql> show slave status\G
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation

OK, that shows us the privilege needed, let's try start and stop slave:

mysql> start slave;
ERROR 1045 (28000): Access denied for user 'sheeri'@'localhost' (using password: NO)
mysql> stop slave;
ERROR 1045 (28000): Access denied for user 'sheeri'@'localhost' (using password: NO)

Suggested fix:
Document what level of privilege is needed for START SLAVE and STOP SLAVE.

Change the error message for start/stop slave to include the privilege level(s) that allow it.

Hello Sheeri,

Thank you for the report and feedback!
Verifying for the inconsistency observed in the error messages.
For the documentation part - imho, the required privileges information is present in relevant START SLAVE/STOP SLAVE pages i.e http://dev.mysql.com/doc/refman/5.7/en/start-slave.html and http://dev.mysql.com/doc/refman/5.7/en/stop-slave.html

But it would be better if this is included in the "Privileges Provided by MySQL" section of the manual as well https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

Thanks,
Umesh

-- 5.7.14 daily build

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.14: bin/mysql -ubug -p123 -S run/slave.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.14-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+------------------------------------------+
| Grants for bug@localhost                 |
+------------------------------------------+
| GRANT SELECT ON *.* TO 'bug'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> show slave status\G
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
mysql> start slave;
ERROR 1045 (28000): Access denied for user 'bug'@'localhost' (using password: YES)
mysql> show errors;
+-------+------+----------------------------------------------------------------+
| Level | Code | Message                                                        |
+-------+------+----------------------------------------------------------------+
| Error | 1045 | Access denied for user 'bug'@'localhost' (using password: YES) |
+-------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> stop slave;
ERROR 1045 (28000): Access denied for user 'bug'@'localhost' (using password: YES)
mysql>

Posted by developer:
 
Hi - I've just received this as a documentation bug. Thanks for pointing this out. The required privilege is REPLICATION_SLAVE_ADMIN. The privilege page said that was needed to "start and stop replication", but it didn't name the commands so you couldn't find them by searching. I've added the command names now.

Could we ask for explanation why 5.7 documentation pointing to privileges in 8.0 ?

https://dev.mysql.com/doc/refman/5.7/en/stop-slave.html Stops the slave threads. STOP SLAVE requires the REPLICATION_SLAVE_ADMIN (https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave-ad...) 

https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-privileges

When I am granting replication_slave_admin privilege in 5.7 it says -

grant REPLICATION_SLAVE_ADMIN on '*' TO 'repl_monitor'@'%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPLICATION_SLAVE_ADMIN on '*' TO 'repl_monitor'@'%'' at line 1

I am not sure if I always need to grant super privileges for replication admin user.

Ignore last command -

This is the correct one with *.*

mysql> GRANT REPLICATION_SLAVE_ADMIN ON *.* TO 'repl_monitor'@'%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPLICATION_SLAVE_ADMIN ON *.* TO 'repl_monitor'@'%'' at line 1