Bug #77860 Prevent account management statements from injecting account attributes

• Submitted: 28 Jul 2015 15:21
• Modified: 6 Oct 2015 16:49
• Reporter: Todd Farmer (OCA)
• Status: Closed
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.7.8
• OS: Any
• CPU Architecture: Any

[28 Jul 2015 15:21] Todd Farmer

Description:
This is related to Bug#20996273, which provides a partial fix to this problem, but does not address all aspects.  A CREATE USER or ALTER USER command will inject arbitrary attributes not defined in the originating command.  Examples:

mysql> CREATE USER u1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

R:\ade\mysql-5.7.8-rc-winx64>bin\mysqlbinlog data\GRAPHITE-bin.000001
...
# at 3591
#150728  9:15:56 server id 1  end_log_pos 3656 CRC32 0xab847535         Anonymous_GTID  last_committed=13       sequence_number=14
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3656
#150728  9:15:56 server id 1  end_log_pos 3834 CRC32 0x49675e04         Query
thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1438096556/*!*/;
CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
/*!*/;

mysql> ALTER USER u1@localhost ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

R:\ade\mysql-5.7.8-rc-winx64>bin\mysqlbinlog data\GRAPHITE-bin.000002
...
# at 219
#150728  9:18:44 server id 1  end_log_pos 346 CRC32 0x6c62132d  Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1438096724/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/
;
/*!\C cp850 *//*!*/;
SET @@session.character_set_client=4,@@session.collation_connection=4,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
ALTER USER 'u1'@'localhost' PASSWORD EXPIRE DEFAULT ACCOUNT LOCK
/*!*/;

How to repeat:
Issue CREATE and ALTER USER commands, note that query rewrite injects attributes not defined in the original SQL.

Suggested fix:
Limit injection of attributes to authentication plugin when password or authentication_string is specified.  All other account attributes should be recorded to the binary log exactly as they were defined in the original SQL issued (or omitted if not part of original SQL).

[28 Jul 2015 15:29] Todd Farmer

Posted by developer:
 
One example of problems caused by this behavior:

mysql> CREATE USER u1@localhost PASSWORD EXPIRE INTERVAL 5 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER u1@localhost IDENTIFIED BY 'pwd';
Query OK, 0 rows affected (0.00 sec)

The CREATE USER statement is written to the binlog as follows:

SET TIMESTAMP=1438097065/*!*/;
CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' PASSWORD EXPIRE INTERVAL 5 DAY ACCOUNT UNLOCK
/*!*/;

As a result, both the master and slave will have the account u1@localhost with no password, and a password expiration policy of 5 days.  The ALTER USER command is rewritten to inject a password expiration policy that was not present in the ALTER USER command issued, and uses the default policy:

SET TIMESTAMP=1438097085/*!*/;
ALTER USER 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' PASSWORD EXPIRE DEFAULT
/*!*/;

As a result, the password policy on the slave is changed from requiring a password change every 5 days to whatever the server default is.

[6 Oct 2015 16:49] Paul DuBois

Noted in 5.7.9, 5.8.0 changelogs.

Binary logging of CREATE USER and statements could log the hash of
the password hash (rather than the hash itself) when
log_backward_compatible_user_definition was enabled. Binary logging
of ALTER USER statements could include attributes not present in the
original statements.

In consequence of the fix for these issues,
log_backward_compatible_user_definition has been renamed to
log_builtin_as_identified_by_password. If this variable is enabled,
binary logging for CREATE USER statements involving built-in
authentication plugins rewrites the statements to include an
IDENTIFIED BY PASSWORD clause, and SET PASSWORD statements are logged
as SET PASSWORD statements, rather than being rewritten to ALTER USER
statements.