Bug #50172 The 'CREATE USER' statement is binlogged with plaintext password
Submitted: 8 Jan 2010 8:58 Modified: 25 Jan 2011 8:13
Reporter: Libing Song Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0, 5.1, 5.5.99 OS:Any
Assigned to: Libing Song CPU Architecture:Any

[8 Jan 2010 8:58] Libing Song
Description:
The 'CREATE USER' statement is binlogged with plaintext password.
In contrast, the 'SET PASSWORD FOR ...=PASSWORD(...)' is binlogged 
with cipher passoword.
It is better to binlogged those statement with cipher password.

How to repeat:
source include/have_log_bin.inc;

CREATE USER 'u1'@'localhost' IDENTIFIED BY 'abc';
SET PASSWORD FOR 'u1'@'localhost' = PASSWORD('123');
source include/show_binlog_events.inc;
[11 Jan 2010 7:07] Sveta Smirnova
Thank you for the report.

Verified as described.
[21 Feb 2010 6:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/100984

3175 Li-Bing.Song@sun.com	2010-02-21
      BUG#50172 The 'CREATE USER' statement is binlogged with plaintext password
      
      When executing 'CREATE USER' statements, the orignal queries user input 
      were binlogged directly. So the plaintext passwords user input were binlogged.
      
      Afther this patch, We create a new binlog query with ciphertext password.
[21 Feb 2010 9:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/100988

3175 Li-Bing.Song@sun.com	2010-02-21
      BUG#50172 The 'CREATE USER' statement is binlogged with plaintext password
      
      When executing 'CREATE USER' statements, the original queries user input 
      were binlogged directly. So the plain text passwords user input were binlogged.
      
      After this patch, We create a new binlog query with cipher text password.
[24 Feb 2010 2:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101280

3176 Li-Bing.Song@sun.com	2010-02-24
      BUG#50172 The 'CREATE USER' statement is binlogged with plaintext password
      
      When executing 'CREATE USER' statements, the original queries user input 
      were binlogged directly. So the plain text passwords user input were binlogged.
      
      After this patch, We create a new binlog query with cipher text password.
[1 Mar 2010 9:11] Libing Song
Pushed to mysql-5.1-rep+2´╝îmerged to mysql-5.1-rpl-merge and mysql-6.0-rpl-merge
[24 Mar 2010 8:15] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100324081249-yfwol7qtcek6dh7w) (version source revid:alik@sun.com-20100324081113-kc7x1iytnplww91u) (merge vers: 6.0.14-alpha) (pib:16)
[24 Mar 2010 8:18] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100324081159-5b8juv8ldiqwce8v) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (pib:16)
[25 Mar 2010 1:19] Paul DuBois
Noted in 6.0.14 changelog.

Passwords for CREATE USER statements were written to the binary log
in plaintext rather than in ciphertext.

Setting report to Need Merge pending push of Celosia to release tree.
[4 Aug 2010 8:05] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 8:20] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 10:53] Jon Stephens
Added 5.6.1 changelog entry.

Set NM status, waiting for merges to 5.1/5.5 trees.
[4 Aug 2010 14:03] Jon Stephens
5.6 version is 5.6.0 not 5.6.1 - fixed changelog entry. Status unchanged.
[3 Sep 2010 17:27] Jon Stephens
Shouldn't have been set to Need Merge, my bad. Closed.
[10 Jan 2011 3:22] Daogang Qu
This is not a complete fix. Please see the following test case:

binlog_create_user.test
-- source include/have_log_bin.inc
-- source include/have_binlog_format_statement.inc
CREATE USER plug IDENTIFIED WITH test_plugin_server;
CREATE USER user2 IDENTIFIED BY 'secret';
--source include/show_binlog_events.inc

binlog_create_user-master.opt
$PLUGIN_AUTH_OPT
$PLUGIN_AUTH_LOAD

Result:
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; CREATE USER 'plug'@'%'
master-bin.000001	#	Query	#	#	use `test`; CREATE USER 'user2'@'%' IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7'

Obviously, the 'CREATE USER plug IDENTIFIED WITH test_plugin_server' is not be
handled in the patch, so that it is binlogged wrongly.

To 'CREATE USER user2 IDENTIFIED BY 'secret'', the binlog is not looks good.
[10 Jan 2011 3:24] Daogang Qu
Change its status to 'verified' base on the above test case.
[10 Jan 2011 3:32] Daogang Qu
CREATE USER user_specification
    [, user_specification] ...

user_specification:
    user
    [
        IDENTIFIED BY [PASSWORD] 'password'
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
    ]
[25 Jan 2011 8:13] Libing Song
'IDENTIFIED WITH' is introduced by WL#1054 which was pushed later than the patch
for this bug. So I reported a new bug for this problem.
[25 Jan 2011 8:14] Libing Song
The new bug is Bug#59708.