MDEV-28294: set default role bypasses Replicate_Wild_Ignore_Table: mysql.%bb-10.4-MDEV-28294-merge

Problem:
========
When replicating SET DEFAULT ROLE, the pre-update check (i.e. that
in set_var_default_role::check()) tries to validate the existence of
the given rules/user even when the targeted tables are ignored. When
previously issued CREATE USER/ROLE commands are ignored by the
replica because of the replication filtering rules, this results in
an error because the targeted data does not exist.

Solution:
========
Before checking that the given rules/user exist of a SET DEFAULT
ROLE command, first ensure that the mysql.user and
mysql.roles_mapping tables are not excluded by replication filters.

Reviewed By
============
Andrei Elkin <andrei.elkin@mariadb.com>

7 files changed, 303 insertions, 2 deletions

diff --git a/mysql-test/suite/rpl/r/rpl_filter_set_var_missing_data.result b/mysql-test/suite/rpl/r/rpl_filter_set_var_missing_data.result
new file mode 100644
index 00000000000..96b854ad83a
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_filter_set_var_missing_data.result
@@ -0,0 +1,69 @@
+include/master-slave.inc
+[connection master]
+#
+# Set replica to ignore system mysql tables
+connection slave;
+set @save_dbug=@@GLOBAL.debug_dbug;
+set @@GLOBAL.debug_dbug="+d,sync_set_var_rpl_filtered";
+include/stop_slave.inc
+SET @@GLOBAL.replicate_wild_ignore_table="mysql.%";
+include/start_slave.inc
+#
+# Execute grant-based commands on primary which modify mysql system
+# tables
+connection master;
+CREATE ROLE journalist;
+CREATE USER testuser@localhost IDENTIFIED by '';
+GRANT journalist to testuser@localhost;
+#
+# Execute SET commands which use the previous user/role data
+SET DEFAULT ROLE journalist for testuser@localhost;
+SET PASSWORD for testuser@localhost= PASSWORD('123');
+include/save_master_gtid.inc
+#
+# Verify primary's grant tables have the correct user/role data
+select count(*)=1 from mysql.user where User='testuser';
+count(*)=1
+1
+select count(*)=1 from mysql.roles_mapping where User='testuser';
+count(*)=1
+1
+connection slave;
+#
+# This first synchronization validates that the rpl_filter will ignore
+# the SET DEFAULT ROLE command
+SET DEBUG_SYNC='now WAIT_FOR ignoring_event';
+SET DEBUG_SYNC='now SIGNAL ack_event_ignored';
+#
+# This second synchronization validates that the rpl_filter will ignore
+# the SET PASSWORD command
+SET DEBUG_SYNC='now WAIT_FOR ignoring_event';
+SET DEBUG_SYNC='now SIGNAL ack_event_ignored';
+#
+# Ensure that the replica receives all of the primary's events without
+# error
+include/sync_with_master_gtid.inc
+Last_SQL_Error = 
+Last_SQL_Errno = 0
+#
+# Verify that the replica did not execute the master's commands
+select count(*)=0 from mysql.user where User='testuser';
+count(*)=0
+1
+select count(*)=0 from mysql.roles_mapping where User='testuser';
+count(*)=0
+1
+#
+# Clean up
+connection master;
+DROP ROLE journalist;
+DROP USER testuser@localhost;
+include/save_master_gtid.inc
+connection slave;
+include/sync_with_master_gtid.inc
+SET @@GLOBAL.debug_dbug=@save_dbug;
+SET DEBUG_SYNC='RESET';
+include/stop_slave.inc
+SET @@GLOBAL.replicate_wild_ignore_table="";
+include/start_slave.inc
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_filter_set_var_missing_data.test b/mysql-test/suite/rpl/t/rpl_filter_set_var_missing_data.test
new file mode 100644
index 00000000000..3142e967192
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_filter_set_var_missing_data.test
@@ -0,0 +1,100 @@
+#
+# Purpose:
+#   This test ensures that the SET DEFAULT ROLE and SET PASSWORD commands can
+# be ignored by replica filter rules. MDEV-28294 exposed a bug in which
+# SET DEFAULT ROLE would check for the existence of the given rules/user even
+# when the targeted tables are ignored, resulting in errors if the targeted
+# data does not exist.
+#
+# Methodology:
+#   Using a replica configured with replicate_wild_ignore_table="mysql.%",
+# execute SET DEFAULT ROLE and SET PASSWORD on the primary and ensure that the
+# replica neither errors nor executes the commands which the primary sends.
+# DEBUG_SYNC points are used to validate that the rpl_filter ignores the
+# commands at the expected point.
+#
+# References:
+#   MDEV-28294: set default role bypasses Replicate_Wild_Ignore_Table: mysql.%
+#
+
+source include/master-slave.inc;
+source include/have_binlog_format_mixed.inc;
+source include/have_debug.inc;
+source include/have_debug_sync.inc;
+
+--echo #
+--echo # Set replica to ignore system mysql tables
+connection slave;
+set @save_dbug=@@GLOBAL.debug_dbug;
+set @@GLOBAL.debug_dbug="+d,sync_set_var_rpl_filtered";
+let $old_filter= query_get_value(SHOW SLAVE STATUS, Replicate_Wild_Ignore_Table, 1);
+source include/stop_slave.inc;
+SET @@GLOBAL.replicate_wild_ignore_table="mysql.%";
+source include/start_slave.inc;
+
+--echo #
+--echo # Execute grant-based commands on primary which modify mysql system
+--echo # tables
+connection master;
+CREATE ROLE journalist;
+CREATE USER testuser@localhost IDENTIFIED by '';
+GRANT journalist to testuser@localhost;
+
+--echo #
+--echo # Execute SET commands which use the previous user/role data
+SET DEFAULT ROLE journalist for testuser@localhost;
+SET PASSWORD for testuser@localhost= PASSWORD('123');
+--source include/save_master_gtid.inc
+
+--echo #
+--echo # Verify primary's grant tables have the correct user/role data
+select count(*)=1 from mysql.user where User='testuser';
+select count(*)=1 from mysql.roles_mapping where User='testuser';
+
+connection slave;
+--echo #
+--echo # This first synchronization validates that the rpl_filter will ignore
+--echo # the SET DEFAULT ROLE command
+SET DEBUG_SYNC='now WAIT_FOR ignoring_event';
+SET DEBUG_SYNC='now SIGNAL ack_event_ignored';
+
+--echo #
+--echo # This second synchronization validates that the rpl_filter will ignore
+--echo # the SET PASSWORD command
+SET DEBUG_SYNC='now WAIT_FOR ignoring_event';
+SET DEBUG_SYNC='now SIGNAL ack_event_ignored';
+
+--echo #
+--echo # Ensure that the replica receives all of the primary's events without
+--echo # error
+--source include/sync_with_master_gtid.inc
+let $error= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error, 1);
+--echo Last_SQL_Error = $error
+let $errno= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
+--echo Last_SQL_Errno = $errno
+
+--echo #
+--echo # Verify that the replica did not execute the master's commands
+select count(*)=0 from mysql.user where User='testuser';
+select count(*)=0 from mysql.roles_mapping where User='testuser';
+
+--echo #
+--echo # Clean up
+
+# The master has to drop the role/user combination while the slave still has
+# its filters active; otherwise, the slave would try to drop users/roles that
+# were never replicated.
+--connection master
+DROP ROLE journalist;
+DROP USER testuser@localhost;
+--source include/save_master_gtid.inc
+
+--connection slave
+--source include/sync_with_master_gtid.inc
+SET @@GLOBAL.debug_dbug=@save_dbug;
+SET DEBUG_SYNC='RESET';
+source include/stop_slave.inc;
+--eval SET @@GLOBAL.replicate_wild_ignore_table="$old_filter"
+source include/start_slave.inc;
+
+--source include/rpl_end.inc
diff --git a/sql/set_var.cc b/sql/set_var.cc
index b97bf0677f3..c0a84e910ad 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -938,6 +938,14 @@ int set_var_password::update(THD *thd)
 #endif
 }
 
+void set_var_password::get_modified_tables(TABLE_LIST **tables)
+{
+  size_t n_tables;
+  acl_get_tables_set_password(&user_table, &n_tables);
+  DBUG_ASSERT(n_tables == 1);
+  *tables= &user_table;
+}
+
 /*****************************************************************************
   Functions to handle SET ROLE
 *****************************************************************************/
@@ -1003,6 +1011,14 @@ int set_var_default_role::update(THD *thd)
 #endif
 }
 
+void set_var_default_role::get_modified_tables(TABLE_LIST **tables)
+{
+  size_t n_tables;
+  acl_get_tables_set_default_role(&roles_mapping_table, &n_tables);
+  DBUG_ASSERT(n_tables == 1);
+  *tables= &roles_mapping_table;
+}
+
 /*****************************************************************************
   Functions to handle SET NAMES and SET CHARACTER SET
 *****************************************************************************/
diff --git a/sql/set_var.h b/sql/set_var.h
index 56de00221d8..d7a2b685bc2 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -279,6 +279,21 @@ public:
     @returns whether this variable is @@@@optimizer_trace.
   */
   virtual bool is_var_optimizer_trace() const { return false; }
+
+  /*
+    Output any tables that will be modified during the update process. This is
+    used for rpl_filter validation to ignore SET commands which should not be
+    replicated.
+
+    @param tables [out] The address of the pointer which should be updated to
+                  reference the list of modified tables. Will be NULL if no
+                  tables will be modified.
+
+  */
+  virtual void get_modified_tables(TABLE_LIST **tables)
+  {
+    *tables= NULL;
+  }
 };
 
 
@@ -347,11 +362,15 @@ public:
 class set_var_password: public set_var_base
 {
   LEX_USER *user;
+  TABLE_LIST user_table;
 public:
   set_var_password(LEX_USER *user_arg) :user(user_arg)
-  {}
+  {
+    user_table.next_local= user_table.next_global= NULL;
+  }
   int check(THD *thd);
   int update(THD *thd);
+  void get_modified_tables(TABLE_LIST **tables);
 };
 
 /* For SET ROLE */
@@ -373,11 +392,16 @@ class set_var_default_role: public set_var_base
   LEX_USER *user, *real_user;
   LEX_CSTRING role;
   const char *real_role;
+  TABLE_LIST roles_mapping_table;
 public:
   set_var_default_role(LEX_USER *user_arg, LEX_CSTRING role_arg) :
-    user(user_arg), role(role_arg) {}
+    user(user_arg), role(role_arg)
+    {
+      roles_mapping_table.next_local= roles_mapping_table.next_global= NULL;
+    }
   int check(THD *thd);
   int update(THD *thd);
+  void get_modified_tables(TABLE_LIST **tables);
 };
 
 /* For SET NAMES and SET CHARACTER SET */
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 10805aedaf0..d5d4e92e07d 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -1916,6 +1916,48 @@ class Grant_tables
   inline const Roles_mapping_table& roles_mapping_table() const
   { return m_roles_mapping_table; }
 
+  /*
+    Outputs the TABLE_LIST corresponding to the table specification of
+    which_tables.
+
+    @param which_tables [in]  Bit map specifying which tables to include in the
+                              output. See enum_acl_tables
+
+    @param lock_type [in]     Lock type for the tables
+
+    @param tbls_out [out]     Empty TABLE_LIST which will be populated using
+                              the specification from which_tables
+
+    @param n_tables [out]     The number of tables output in tbls_out
+  */
+  static void get_table_list(uint which_tables, enum thr_lock_type lock_type,
+                             TABLE_LIST *tbls_out, size_t *n_tables)
+  {
+    DBUG_ENTER("Grant_tables::get_table_list");
+    uint tmp_which_tables= which_tables;
+    *n_tables= 0;
+    DBUG_ASSERT(tbls_out && which_tables < (1 << (USER_TABLE + 1)));
+
+    for (int i=USER_TABLE; i >=0 && tmp_which_tables; i--)
+    {
+      if (tmp_which_tables & 1)
+      {
+        tbls_out->init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_TABLE_NAME[i],
+                           NULL, lock_type);
+        *n_tables+= 1;
+        /*
+          Stop if this was the last table to populate
+        */
+        if (!(tbls_out->next_local))
+          break;
+
+        tbls_out= tbls_out->next_local;
+      }
+      tmp_which_tables >>= 1;
+    }
+    DBUG_VOID_RETURN;
+  }
+
  private:
 
   /* Before any operation is possible on grant tables, they must be opened.
@@ -3932,6 +3974,21 @@ wsrep_error_label:
   DBUG_RETURN(result);
 }
 
+void acl_get_tables_set_password(TABLE_LIST *tables, size_t *n_tables)
+{
+  DBUG_ENTER("acl_get_tables_set_password");
+  Grant_tables::get_table_list(Table_user, TL_WRITE, tables, n_tables);
+  DBUG_VOID_RETURN;
+}
+
+void acl_get_tables_set_default_role(TABLE_LIST *tables, size_t *n_tables)
+{
+  DBUG_ENTER("acl_get_tables_set_default_role");
+  Grant_tables::get_table_list(Table_roles_mapping, TL_WRITE, tables,
+                               n_tables);
+  DBUG_VOID_RETURN;
+}
+
 int acl_check_set_default_role(THD *thd, const char *host, const char *user,
                                const char *role)
 {
diff --git a/sql/sql_acl.h b/sql/sql_acl.h
index dc8a085c96c..bda528ca948 100644
--- a/sql/sql_acl.h
+++ b/sql/sql_acl.h
@@ -421,6 +421,8 @@ int acl_check_set_default_role(THD *thd, const char *host, const char *user,
                                const char *role);
 int acl_set_default_role(THD *thd, const char *host, const char *user,
                          const char *rolename);
+void acl_get_tables_set_password(TABLE_LIST *out, size_t *n_tables);
+void acl_get_tables_set_default_role(TABLE_LIST *out, size_t *n_tables);
 
 extern SHOW_VAR acl_statistics[];
 
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 6a1b849b912..2b718972fc6 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3661,6 +3661,39 @@ mysql_execute_command(THD *thd)
                  MYF(0));
       DBUG_RETURN(0);
     }
+
+    /*
+      Check if the SET command will modify a table that should be ignored in
+      replication.
+    */
+    if (lex->sql_command == SQLCOM_SET_OPTION)
+    {
+      List<set_var_base> *lex_var_list= &lex->var_list;
+      List_iterator_fast<set_var_base> it(*lex_var_list);
+      set_var_base *set_var;
+      while ((set_var=it++))
+      {
+        TABLE_LIST *tables;
+        set_var->get_modified_tables(&tables);
+        if (tables && all_tables_not_ok(thd, tables))
+        {
+          /*
+            Used in MTR to prove that the event was ignored _here_
+          */
+          DBUG_EXECUTE_IF(
+              "sync_set_var_rpl_filtered",
+              DBUG_ASSERT(!debug_sync_set_action(
+                  thd, STRING_WITH_LEN("now SIGNAL ignoring_event WAIT_FOR "
+                                       "ack_event_ignored"))););
+          /* warn the slave SQL thread */
+          my_message(ER_SLAVE_IGNORED_TABLE,
+                     ER_THD(thd, ER_SLAVE_IGNORED_TABLE), MYF(0));
+          DBUG_RETURN(0);
+        }
+      }
+    }
+
+
     /* 
        Execute deferred events first
     */