Bug #76911 ALTER USER rewrite causes differences on slave

• Submitted: 1 May 2015 20:13
• Modified: 13 May 2015 15:43
• Reporter: Todd Farmer (OCA)
• Status: Closed
• Category: MySQL Server: Security: Privileges
• Severity: S2 (Serious)
• Version: 5.7.7
• OS: Any
• CPU Architecture: Any

[1 May 2015 20:13] Todd Farmer

Description:
MySQL Server rewrites CREATE and ALTER USER statements and appends clauses not found in the originally-issued statement.  This can cause problems in replicated environments, and these appended clauses are not derived from the existing state of the user account being altered.  For example:

CREATE USER t@localhost ACCOUNT LOCK;
ALTER USER t@localhost PASSWORD EXPIRE;

The above ALTER USER command results in the following binary log entry:

#150501 14:07:30 server id 1  end_log_pos 2789 CRC32 0xd79a8401         Query
thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1430510850/*!*/;
ALTER USER 't'@'localhost' PASSWORD EXPIRE ACCOUNT UNLOCK
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;

Note the ACCOUNT UNLOCK clause is added, resulting in the account on the slave being unlocked unintentionally.

This will also adversely affect replication configurations where accounts on downstream slaves intentionally have different properties.

How to repeat:
CREATE USER t@localhost ACCOUNT LOCK;
ALTER USER t@localhost PASSWORD EXPIRE;

Examine binary logs.

Suggested fix:
Do not rewrite ALTER (and perhaps CREATE) statements to include clauses not found in the original command.

[13 May 2015 15:43] Paul DuBois

Noted in 5.7.8, 5.8.0 changelogs.

For logging to the binary log, the server could rewrite CREATE USER
and ALTER USER statements, adding an ACCOUNT UNLOCK clause not 
present in the original statement. This could unlock locked accounts.
The clause is no longer written unless present in the original
statement.

[13 May 2015 15:45] Paul DuBois

Revised changelog entry (mentions replication implication):

For logging to the binary log, the server could rewrite CREATE USER
and ALTER USER statements, adding an ACCOUNT UNLOCK clause not
present in the original statement. This could unlock locked accounts
and cause differences between master and slave servers. The clause is
no longer written unless present in the original statement.