[MDEV-14716] Wrong binlog entries for a multi-user CREATE USER statement Created: 2017-12-20  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Replication
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Andrei Elkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-14031 Password policy causes replication fa... Closed
relates to MDEV-21721 Partially successful multi-user DROP ... Confirmed

 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.

 Comments   
Comment by Alexander Barkov [ 2017-12-20 ]

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
Comment by Elena Stepanova [ 2017-12-20 ]

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

Comment by Alexander Barkov [ 2017-12-27 ]

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.

Generated at Thu Feb 08 08:15:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.