Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28294

set default role bypasses Replicate_Wild_Ignore_Table: mysql.%

    XMLWordPrintable

    Details

      Description

      Replicate_Wild_Ignore_Table: mysql.% can't make bypass SET DEFAULT ROLE '<role>' FOR 'user';

      On master

      MariaDB [(none)]> create user testuser@'%';
      Query OK, 0 rows affected (0.002 sec)
       
      MariaDB [(none)]> CREATE ROLE journalist;
      Query OK, 0 rows affected (0.002 sec)
       
      MariaDB [(none)]> grant journalist to testuser@'%';
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> select * from mysql.roles_mapping;
      +-----------+----------+-----------------------+--------------+
      | Host      | User     | Role                  | Admin_option |
      +-----------+----------+-----------------------+--------------+
      | localhost | root     | role_workflow_portals | Y            |
      | localhost | root     | journalist            | Y            |
      | %         | testuser | journalist            | N            |
      +-----------+----------+-----------------------+--------------+
      3 rows in set (0.001 sec)
       
      MariaDB [(none)]> SET DEFAULT ROLE journalist for testuser@'%';
      Query OK, 0 rows affected (0.001 sec)
      

      on slave:

      MariaDB [(none)]> show slave status\G
      *************************** 1. row ***************************
                      Slave_IO_State: Waiting for master to send event
                         Master_Host: 192.168.254.14
                         Master_User: repl
                         Master_Port: 3306
                       Connect_Retry: 60
                     Master_Log_File: m2-bin.000005
                 Read_Master_Log_Pos: 1077
                      Relay_Log_File: mariadb-relay-bin.000002
                       Relay_Log_Pos: 1220
               Relay_Master_Log_File: m2-bin.000005
                    Slave_IO_Running: Yes
                   Slave_SQL_Running: No
                     Replicate_Do_DB: 
                 Replicate_Ignore_DB: 
                  Replicate_Do_Table: 
              Replicate_Ignore_Table: 
             Replicate_Wild_Do_Table: 
         Replicate_Wild_Ignore_Table: mysql.%
                          Last_Errno: 1959
                          Last_Error: Error 'Invalid role specification `journalist`' on query. Default database: ''. Query: 'SET DEFAULT ROLE 'journalist' FOR 'testuser'@'%''
                        Skip_Counter: 0
                 Exec_Master_Log_Pos: 924
                     Relay_Log_Space: 1684
                     Until_Condition: None
                      Until_Log_File: 
                       Until_Log_Pos: 0
                  Master_SSL_Allowed: No
                  Master_SSL_CA_File: 
                  Master_SSL_CA_Path: 
                     Master_SSL_Cert: 
                   Master_SSL_Cipher: 
                      Master_SSL_Key: 
               Seconds_Behind_Master: NULL
       Master_SSL_Verify_Server_Cert: No
                       Last_IO_Errno: 0
                       Last_IO_Error: 
                      Last_SQL_Errno: 1959
                      Last_SQL_Error: Error 'Invalid role specification `journalist`' on query. Default database: ''. Query: 'SET DEFAULT ROLE 'journalist' FOR 'testuser'@'%''
         Replicate_Ignore_Server_Ids: 
                    Master_Server_Id: 3
                      Master_SSL_Crl: 
                  Master_SSL_Crlpath: 
                          Using_Gtid: Slave_Pos
                         Gtid_IO_Pos: 3-3-5,0-3-2
             Replicate_Do_Domain_Ids: 
         Replicate_Ignore_Domain_Ids: 
                       Parallel_Mode: optimistic
                           SQL_Delay: 0
                 SQL_Remaining_Delay: NULL
             Slave_SQL_Running_State: 
                    Slave_DDL_Groups: 5
      Slave_Non_Transactional_Groups: 0
          Slave_Transactional_Groups: 0
      

      MTR test case for reproducing

      source include/master-slave.inc;
       
      call mtr.add_suppression("Slave: Invalid role specification `journalist` Error_code: 1959");
       
      connection master;
      SET @@GLOBAL.replicate_wild_ignore_table="mysql.%";
       
      connection slave;
      source include/stop_slave.inc;
      SET @@GLOBAL.replicate_wild_ignore_table="mysql.%";
      source include/start_slave.inc;
       
      connection master;
      CREATE ROLE journalist;
      CREATE USER testuser@localhost IDENTIFIED by '';
       
      GRANT journalist to testuser@localhost;
       
      SELECT  * from mysql.roles_mapping;
       
      SET DEFAULT ROLE journalist for testuser@localhost;
       
      connection slave;
      --let $slave_sql_errno= 1959
      --let $slave_timeout=5
      source include/wait_for_slave_sql_error.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
      let $repl_wild_filter= query_get_value(SHOW SLAVE STATUS, Replicate_Wild_Ignore_Table, 1);
      --echo Replicate_Wild_Ignore_Table = $repl_wild_filter
       
      # Clean up
      source include/stop_slave_io.inc;
      SET @@GLOBAL.replicate_wild_ignore_table="";
      RESET SLAVE;
      --let $rpl_only_running_threads= 1
       
      connection master;
      SET @@GLOBAL.replicate_wild_ignore_table="";
      DROP ROLE journalist;
      DROP USER testuser@localhost;
       
      --source include/rpl_end.inc
      

      Reproduced with:
      10.2 9b2d36660bbb4f93c6b9e0761c91d57d47f59196
      10.3 b208030ef5b4274fd66cc9667a2dc96f6e63db81
      10.4 d16c3aca3c3ecd1050b2527c213f4fdf1959d9e2
      10.5 1a66e3f861a722689a39f83919d1f1a5466f64bf
      10.6 cad792c6862546a13b83f05209e7842a95bdc5fa
      10.7 9e314fcf6e666ce6fdbdd8ca1ae23d6c4b389b21
      10.8 6948abb94c6739101320d12ddec1d2daae929cc2
      10.9 72a125058527126ae31c02851db0d1ca186706f7

        Attachments

          Activity

            People

            Assignee:
            bnestere Brandon Nesterenko
            Reporter:
            allen.lee@mariadb.com Allen Lee
            Votes:
            1 Vote for this issue
            Watchers:
            8 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.