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

Wrong binlog entries for a multi-user CREATE USER statement

Details

    Description

      I create a test file with this content:

      --source include/not_embedded.inc
      		 
      --source include/have_binlog_format_statement.inc
      		 
       
      		 
      if (!$SIMPLE_PASSWORD_CHECK_SO) {
      		 
        skip No SIMPLE_PASSWORD_CHECK plugin;
      		 
      }
      		 
       
      		 
      INSTALL SONAME "simple_password_check";
      		 
      SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS
      		 
      WHERE PLUGIN_NAME='simple_password_check';
      		 
       
      		 
       
      		 
      RESET MASTER; # get rid of previous tests binlog
      		 
       
      		 
      --error ER_NOT_VALID_PASSWORD
      		 
      CREATE USER
      		 
        user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85',
      		 
        user2@localhost IDENTIFIED BY 'bsg9#d.cem#!85';
      		 
       
      		 
      DROP USER user1@localhost;
      		 
       
      		 
      --let $binlog_file = LAST
      		 
      source include/show_binlog_events.inc;
      		 
       
      		 
      UNINSTALL PLUGIN simple_password_check;

      and run the test. It prints this output:

      INSTALL SONAME "simple_password_check";
      		 
      SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS
      		 
      WHERE PLUGIN_NAME='simple_password_check';
      		 
      PLUGIN_NAME
      		 
      simple_password_check
      		 
      RESET MASTER;
      		 
      CREATE USER
      		 
      user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85',
      		 
      user2@localhost IDENTIFIED BY 'bsg9#d.cem#!85';
      		 
      ERROR HY000: Your password does not satisfy the current policy requirements
      		 
      DROP USER user1@localhost;
      		 
      include/show_binlog_events.inc
      		 
      Log_name	Pos	Event_type	Server_id	End_log_pos	Info
      		 
      master-bin.000001	#	Gtid	#	#	GTID #-#-#
      		 
      master-bin.000001	#	Query	#	#	use `test`; CREATE USER
      		 
      user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85',
      		 
      user2@localhost IDENTIFIED BY 'bsg9#d.cem#!85'
      		 
      master-bin.000001	#	Gtid	#	#	GTID #-#-#
      		 
      master-bin.000001	#	Query	#	#	use `test`; DROP USER user1@localhost
      		 
      UNINSTALL PLUGIN simple_password_check;
      		 
      plugins.binlog-AAA                       [ pass ]     62

      Note, user2 was not actually created because of the password policy, however it got printed into the binary log. This can cause problems with replication.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14031 Password policy causes replication failure

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21721 Partially successful multi-user DROP breaks filtered replication

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment - - edited

            The same problem is repeatable without simple_password_check plugin:

            --source include/not_embedded.inc
            		 
            --source include/have_binlog_format_statement.inc
            		 
             
            		 
            RESET MASTER; # get rid of previous tests binlog
            		 
             
            		 
            --error ER_PASSWD_LENGTH
            		 
            CREATE USER
            		 
              user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85',
            		 
              user2@localhost IDENTIFIED BY PASSWORD 'xxx';
            		 
             
            		 
            DROP USER user1@localhost;
            		 
             
            		 
            --let $binlog_file = LAST
            		 
            source include/show_binlog_events.inc;


            RESET MASTER;
            		 
            CREATE USER
            		 
            user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85',
            		 
            user2@localhost IDENTIFIED BY PASSWORD 'xxx';
            		 
            ERROR HY000: Password hash should be a 41-digit hexadecimal number
            		 
            DROP USER user1@localhost;
            		 
            include/show_binlog_events.inc
            		 
            Log_name	Pos	Event_type	Server_id	End_log_pos	Info
            		 
            master-bin.000001	#	Gtid	#	#	GTID #-#-#
            		 
            master-bin.000001	#	Query	#	#	use `test`; CREATE USER
            		 
            user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85',
            		 
            user2@localhost IDENTIFIED BY PASSWORD 'xxx'
            		 
            master-bin.000001	#	Gtid	#	#	GTID #-#-#
            		 
            master-bin.000001	#	Query	#	#	use `test`; DROP USER user1@localhost

            Note, user2 was not created due to a wrong password hash, however it was logged.

            It should be fixed:

            • either to log only successfully created users
            • or do changes atomically: if one of the users has a bad password, then don't create any users at all
            bar Alexander Barkov added a comment - - edited The same problem is repeatable without simple_password_check plugin: --source include/not_embedded.inc --source include/have_binlog_format_statement.inc   RESET MASTER; # get rid of previous tests binlog   --error ER_PASSWD_LENGTH CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85' , user2@localhost IDENTIFIED BY PASSWORD 'xxx' ;   DROP USER user1@localhost;   --let $binlog_file = LAST source include/show_binlog_events.inc; RESET MASTER; CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85', user2@localhost IDENTIFIED BY PASSWORD 'xxx'; ERROR HY000: Password hash should be a 41-digit hexadecimal number DROP USER user1@localhost; include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85', user2@localhost IDENTIFIED BY PASSWORD 'xxx' master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; DROP USER user1@localhost Note, user2 was not created due to a wrong password hash, however it was logged. It should be fixed: either to log only successfully created users or do changes atomically: if one of the users has a bad password, then don't create any users at all
            elenst Elena Stepanova added a comment -

            bar, did it happen in the scope of MDEV-7288 , as my comment in MDEV-14031 suggests, or is it unrelated?

            elenst Elena Stepanova added a comment - bar , did it happen in the scope of MDEV-7288 , as my comment in MDEV-14031 suggests, or is it unrelated?
            bar Alexander Barkov added a comment - - edited

            elenst, it did not happen in the scope of MDEV-7288.
            This problem should be repeatable in earlier versions.
            Sorry, cannot test 10.0 right now, it does not compile for me.

            bar Alexander Barkov added a comment - - edited elenst , it did not happen in the scope of MDEV-7288 . This problem should be repeatable in earlier versions. Sorry, cannot test 10.0 right now, it does not compile for me.

            People

              Elkin Andrei Elkin
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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