Bug #84344 ALTER USER with empty password replicates incorrectly

• Submitted: 27 Dec 2016 8:29
• Modified: 6 Jan 2017 8:21
• Reporter: team phx (OCA)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.7, 5.7.17
• OS: Any
• CPU Architecture: Any
• Tags: password, replication

[27 Dec 2016 8:29] team phx

Description:
ALTER USER with empty password, for example, "ALTER USER 'test'@'%' IDENTIFIED BY '';", are replicated incorrectly in MySQL 5.7, which cause user's password expired in slave, but not in master. ALTER USER with a non-empty password will not trigger this bug.

It seems cause by: when the statement write to binlog, "IDENTIFIED BY ''" is missing, turn the statement into "ALTER USER 'test'@'%'", which will expire the password immediately.

How to repeat:
Server version: 5.7.17-log MySQL Community Server (GPL)

=== replication initialize ===

On master:
master> create user 'replica'@'%';
Query OK, 0 rows affected (0.00 sec)

master> grant replication slave on *.* to 'replica'@'%';
Query OK, 0 rows affected (0.00 sec)

On slave:
slave> change master to master_host='master_ip',master_port=3306,master_user='replica',master_password='',master_auto_position=1;   
Query OK, 0 rows affected, 2 warnings (0.01 sec)

slave> start slave;
Query OK, 0 rows affected (0.01 sec)

=== ALTER USER and check result ===

On master:
master> create user 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

master> alter user 'test'@'%' identified by '';
Query OK, 0 rows affected (0.00 sec)

master> select * from mysql.user where user='test'\G
*************************** 1. row ***************************
                  Host: %
                  User: test
           Select_priv: N
                 <CUT>
                plugin: mysql_native_password
 authentication_string: 
      password_expired: N
 password_last_changed: 2016-12-27 08:07:05
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

On slave:
slave> select * from mysql.user where user='test'\G
*************************** 1. row ***************************
                  Host: %
                  User: test
           Select_priv: N
                 <CUT>
                plugin: mysql_native_password
 authentication_string: 
      password_expired: Y
 password_last_changed: 2016-12-27 08:07:05
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

It is show that password_expired=N in master, but password_expired=Y in slave.

Use mysqlbinlog in master and slave, both binlogs contain:
ALTER USER 'test'@'%' IDENTIFIED WITH 'mysql_native_password'

Suggested fix:
Fix "IDENTIFIED BY ''" missing in binlog.

[27 Dec 2016 8:59] MySQL Verification Team

Hello team phx,

Thank you for the report.
Verified as described with 5.7.17 build.

Thanks,
Umesh

[6 Jan 2017 8:21] team phx

This patch should fix the bug. Patch is diff with mysql 5.7.17.

diff --git a/sql/auth/sql_user.cc b/sql/auth/sql_user.cc
index cb0e11b..9bb4268 100644
--- a/sql/auth/sql_user.cc
+++ b/sql/auth/sql_user.cc
@@ -176,6 +176,12 @@ void append_user_new(THD *thd, String *str, LEX_USER *user, bool comma= true)
         else
           append_query_string(thd, system_charset_info, &from_auth, str);
       }
+      else if (thd->lex->sql_command == SQLCOM_ALTER_USER)
+      {
+        /* ALTER USER must come with AS .., otherwise password will be marked expired */
+        if (user->uses_identified_by_clause)
+          str->append(STRING_WITH_LEN(" AS ''"));
+      }
     }
   }
 }