Slave user can't run "SHOW SLAVE STATUS" anymore after upgrade to 10.5, mysql_upgrade should take of that (MDEV-23610)

[MDEV-24107] SHOW SLAVE STATUS failed even though BINLOG MONITOR privilege provided Created: 2020-11-03  Updated: 2022-03-31  Resolved: 2021-10-11

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: 10.5.4
Fix Version/s: N/A

Type: Technical task Priority: Major
Reporter: Nilnandan Joshi Assignee: Jacob Moorman (Inactive)
Resolution: Fixed Votes: 0
Labels: None


 Description   

As per the documentation, BINLOG MONITOR is new name for REPLICATION CLIENT from MariaDB 10.5.2, (REPLICATION CLIENT still supported as an alias for compatibility purposes). Permits running SHOW commands related to the binary log, in particular the SHOW BINLOG STATUS, SHOW REPLICA STATUS and SHOW BINARY LOGS statements.
https://mariadb.com/kb/en/grant/#binlog-monitor

But SHOW SLAVE STATUS and SHOW REPLICA STATUS failed even though BINLOG MONITOR privilege provided. 

MariaDB [(none)]> show grants;
 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Grants for dbsupp1@localhost |
 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| GRANT PROCESS, SHOW DATABASES, BINLOG MONITOR, SHOW VIEW ON *.* TO "dbsupp1"@"localhost" IDENTIFIED BY PASSWORD '*' |
 
| GRANT SELECT ON "mysql".* TO "dbsupp1"@"localhost" |
 
| GRANT SELECT ON "performance_schema".* TO "dbsupp1"@"localhost" |
 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
3 rows in set (0.000 sec)
 
 
 
MariaDB [(none)]> show slave status\G
 
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION SLAVE ADMIN privilege(s) for this operation
 
MariaDB [(none)]> SHOW REPLICA STATUS\G
 
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION SLAVE ADMIN privilege(s) for this operation

Now REPLICATION SLAVE ADMIN is bigger privileges for the user because with that, user can START/STOP SLAVE and use CHANGE MASTER. So I think SHOW SLAVE STATUS/SHOW REPLICA STATUS should be a part of BINLOG MONITOR only.



 Comments   
Comment by Sujatha Sivakumar (Inactive) [ 2020-11-04 ]

Hello greenman

As part of https://jira.mariadb.org/browse/MDEV-21743 fix 'BINLOG MONITOR'
privilege was added. It is an alias for 'REPLICATION CLIENT' privilege. It
permits execution of SHOW commands related to the binary log.

  • BINLOG MONITOR / REPLICATION CLIENT
    • SHOW BINLOG STATUS (required SUPER or REPLICATION CLIENT prior to this task)
    • SHOW BINARY LOGS (required SUPER or REPLICATION CLIENT prior to this task)
    • SHOW BINLOG EVENTS(required REPLICATION SLAVE prior to this task)
  • New REPLICATION SLAVE ADMIN privilege which permits
    • START SLAVE (required SUPER prior this this task)
    • STOP SLAVE (required SUPER prior this this task)
    • CHANGE Master (required SUPER prior this this task)
    • SHOW REPLICA STATUS (required SUPER or REPLICATION CLIENT prior to this task)
    • BINLOG (required SUPER_ACL prior to this task
    • SHOW RELAYLOG EVENTS (required REPLICATION SLAVE prior to this task)

i.e SHOW REPLICA STATUS command requires 'REPLICATION SLAVE ADMIN' or 'SUPER'

Can you please update the documentation accordingly.

Please remove SHOW REPLICA STATUS / SHOW SLAVE STATUS from following pages.

Add "SHOW BINLOG EVENTS" command under BINLOG MONITOR privilege.

Please remove REPLICATION_CLIENT / BINLOG MONITOR from following page and
add 'REPLICATION SLAVE ADMIN'

Additional Note:
With REPLICATION SLAVE ADMIN privilege along with SHOW REPLICA STATUS,
user can perform several admin tasks like START SLAVE, STOP SLAVE,
CHANGE MASTER, SHOW REPLICA STATUS etc.

This behavior will change in next release. MDEV-23610 will introduce a new
privilege which will allow user to execute SHOW REPLICA STATUS alone.

Comment by Sujatha Sivakumar (Inactive) [ 2020-12-09 ]

Post MDEV-23610 fix privileges look like this:

SLAVE MONITOR/REPLICA MONITOR:

  • SHOW ALL REPLICAS STATUS (required REPLICATION SLAVE ADMIN prior to this task)
  • SHOW ALL SLAVES STATUS (required REPLICATION SLAVE ADMIN prior to this task)
  • SHOW REPLICA STATUS (required REPLICATION SLAVE ADMIN prior to this task)
  • SHOW SLAVE STATUS (required REPLICATION SLAVE ADMIN prior to this task)
  • SHOW RELAYLOG EVENTS (required REPLICATION SLAVE ADMIN prior to this task)
Comment by Manjot Singh (Inactive) [ 2020-12-14 ]

Why are we naming brand new privileges SLAVE MONITOR?

Comment by Daniel Black [ 2022-03-31 ]

Please remove SHOW REPLICA STATUS / SHOW SLAVE STATUS from following pages.

https://mariadb.com/kb/en/grant/#binlog-monitor
https://mariadb.com/kb/en/grant/#replication-client

Done, commented they had a pre-10.5 effect however

Generated at Thu Feb 08 09:27:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.