MDEV-23607 MariaBackup - align required GRANTS to cmd options

Since the 10.5 split of the privileges, the required GRANTs
for various mariabackup operations has changed.

In the addition of tests, a number of mappings where incorrect:

The option --lock-ddl-per-table didn't require connection admin.

The option --safe-slave-backup requires SLAVE MONITOR even without
the --no-lock option.

3 files changed, 78 insertions, 9 deletions

diff --git a/extra/mariabackup/xtrabackup.cc b/extra/mariabackup/xtrabackup.cc
index da2ae65447b..2c4da11c860 100644
--- a/extra/mariabackup/xtrabackup.cc
+++ b/extra/mariabackup/xtrabackup.cc
@@ -6200,22 +6200,28 @@ static bool check_all_privileges()
 	}
 
 	/* KILL ... */
-	if ((!opt_no_lock && (opt_kill_long_queries_timeout || opt_lock_ddl_per_table))
-		/* START SLAVE SQL_THREAD */
-		/* STOP SLAVE SQL_THREAD */
-		|| opt_safe_slave_backup) {
+	if (!opt_no_lock && (opt_kill_long_queries_timeout || opt_kill_long_query_type)) {
 		check_result |= check_privilege(
 			granted_privileges,
-			"SUPER", "*", "*",
+			"CONNECTION ADMIN", "*", "*",
+			PRIVILEGE_WARNING);
+	}
+
+	/* START SLAVE SQL_THREAD */
+	/* STOP SLAVE SQL_THREAD */
+	if (opt_safe_slave_backup) {
+		check_result |= check_privilege(
+			granted_privileges,
+			"REPLICATION SLAVE ADMIN", "*", "*",
 			PRIVILEGE_WARNING);
 	}
 
 	/* SHOW MASTER STATUS */
 	/* SHOW SLAVE STATUS */
 	if (opt_galera_info || opt_slave_info
-		|| (opt_no_lock && opt_safe_slave_backup)) {
+		|| opt_safe_slave_backup) {
 		check_result |= check_privilege(granted_privileges,
-			"REPLICATION CLIENT", "*", "*",
+			"SLAVE MONITOR", "*", "*",
 			PRIVILEGE_WARNING);
 	}
 
diff --git a/mysql-test/suite/mariabackup/backup_grants.result b/mysql-test/suite/mariabackup/backup_grants.result
index ed793e7ff1a..56899f8d9c0 100644
--- a/mysql-test/suite/mariabackup/backup_grants.result
+++ b/mysql-test/suite/mariabackup/backup_grants.result
@@ -3,4 +3,12 @@ FOUND 1 /missing required privilege RELOAD/ in backup.log
 FOUND 1 /missing required privilege PROCESS/ in backup.log
 FOUND 1 /GRANT USAGE ON/ in backup.log
 GRANT RELOAD, PROCESS on *.* to backup@localhost;
+NOT FOUND /missing required privilege REPLICA MONITOR/ in backup.log
+GRANT REPLICA MONITOR ON *.* TO backup@localhost;
+REVOKE REPLICA MONITOR ON *.* FROM backup@localhost;
+GRANT CONNECTION ADMIN ON *.* TO backup@localhost;
+FOUND 1 /missing required privilege REPLICATION SLAVE ADMIN/ in backup.log
+NOT FOUND /missing required privilege REPLICA MONITOR/ in backup.log
+GRANT REPLICATION SLAVE ADMIN ON *.* TO backup@localhost;
+GRANT REPLICA MONITOR ON *.* TO backup@localhost;
 DROP USER backup@localhost;
diff --git a/mysql-test/suite/mariabackup/backup_grants.test b/mysql-test/suite/mariabackup/backup_grants.test
index eadeedd9b5f..894ae73aeb9 100644
--- a/mysql-test/suite/mariabackup/backup_grants.test
+++ b/mysql-test/suite/mariabackup/backup_grants.test
@@ -25,7 +25,62 @@ GRANT RELOAD, PROCESS on *.* to backup@localhost;
 --disable_result_log
 exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf  --backup -ubackup --target-dir=$targetdir;
 --enable_result_log
+rmdir $targetdir;
 
-DROP USER backup@localhost;
-# Cleanup
+# MDEV-23607 Warning: missing required privilege REPLICATION CLIENT
+# --slave-info and galera info require REPLICA MONITOR
+--disable_result_log
+error 1;
+exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --slave-info  --target-dir=$targetdir > $MYSQLTEST_VARDIR/tmp/backup.log;
+--enable_result_log
+rmdir $targetdir;
+
+--let SEARCH_PATTERN=  missing required privilege REPLICA MONITOR
+--source include/search_pattern_in_file.inc
+
+GRANT REPLICA MONITOR ON *.* TO backup@localhost;
+--disable_result_log
+exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --slave-info --target-dir=$targetdir;
+--enable_result_log
+rmdir $targetdir;
+REVOKE REPLICA MONITOR ON *.* FROM backup@localhost;
+
+# TODO need a query that would delay a BACKUP STAGE START/ BACKUP STAGE BLOCK_COMMIT longer than the kill-long-queries-timeout
+#--send SELECT SLEEP(9) kill_me
+## kill-long-query-type=(not empty) requires CONNECTION ADMIN
+#--disable_result_log
+#error 1;
+#--exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup  --kill-long-query-type=all --kill-long-queries-timeout=4 --target-dir=$targetdir > $MYSQLTEST_VARDIR/tmp/backup.log;
+#--reap
+#--enable_result_log
+#rmdir $targetdir;
+#
+#--let SEARCH_PATTERN=  missing required privilege CONNECTION ADMIN
+#--source include/search_pattern_in_file.inc
+
+GRANT CONNECTION ADMIN ON *.* TO backup@localhost;
+--disable_result_log
+exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup  --kill-long-query-type=all --kill-long-queries-timeout=1 --target-dir=$targetdir;
+--enable_result_log
+rmdir $targetdir;
+
+# --safe-slave-backup requires REPLICATION SLAVE ADMIN, and REPLICA MONITOR
+--disable_result_log
+error 1;
+exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --safe-slave-backup --target-dir=$targetdir > $MYSQLTEST_VARDIR/tmp/backup.log;
+--enable_result_log
 rmdir $targetdir;
+
+--let SEARCH_PATTERN=  missing required privilege REPLICATION SLAVE ADMIN
+--source include/search_pattern_in_file.inc
+--let SEARCH_PATTERN=  missing required privilege REPLICA MONITOR
+--source include/search_pattern_in_file.inc
+
+GRANT REPLICATION SLAVE ADMIN ON *.* TO backup@localhost;
+GRANT REPLICA MONITOR ON *.* TO backup@localhost;
+--disable_result_log
+exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --safe-slave-backup --target-dir=$targetdir;
+--enable_result_log
+rmdir $targetdir;
+
+DROP USER backup@localhost;