Background
MySQL 5.6 and 5.7 both support the following CREATE USER syntax:
CREATE USER 'user'@'host' IDENTIFIED BY 'password';
This syntax create a user with the default authentication plugin (mysql_native_password unless configured otherwise) and the password provided. The IDENTIFIED BY syntax is also supported for GRANT command.
Both major versions also support the following syntax:
CREATE USER 'user'@'host' IDENTIFIED WITH mysql_native_password AS 'hash_string';
The password hash for "passw0rd" is "*74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3", you might then expect that the effects of the following two commands will be equivalent:
CREATE USER 'joe'@'%' IDENTIFIED BY 'passw0rd'; CREATE USER 'joe'@'%' IDENTIFIED WITH mysql_native_password AS '*74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3';
Let's confirm.
MySQL 5.7.17
mysql> create user 'joe'@'%' identified by 'passw0rd';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, host, plugin, authentication_string from mysql.user where user = 'joe'\G
*************************** 1. row ***************************
user: joe
host: %
plugin: mysql_native_password
authentication_string: *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3
1 row in set (0.00 sec)
$ mysql -htest-57.xxx -ujoe -ppassw0rd -e 'select "success!"'
Warning: Using a password on the command line interface can be insecure.
+----------+
| success! |
+----------+
| success! |
+----------+
mysql> drop user 'joe'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> select password('passw0rd');
+-------------------------------------------+
| password('passw0rd') |
+-------------------------------------------+
| *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3 |
+-------------------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> create user 'joe'@'%' identified with mysql_native_password as '*74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3';
Query OK, 0 rows affected (0.01 sec)
mysql> select user, host, plugin, authentication_string from mysql.user where user = 'joe'\G
*************************** 1. row ***************************
user: joe
host: %
plugin: mysql_native_password
authentication_string: *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3
1 row in set (0.00 sec)
$ mysql -htest-57.xxx -ujoe -ppassw0rd -e 'select "success!"'
Warning: Using a password on the command line interface can be insecure.
+----------+
| success! |
+----------+
| success! |
+----------+
MySQL 5.6.27
mysql> create user 'joe'@'%' identified by 'passw0rd';
Query OK, 0 rows affected (0.01 sec)
mysql> select user, host, plugin, password, authentication_string from mysql.user where user = 'joe'\G
*************************** 1. row ***************************
user: joe
host: %
plugin: mysql_native_password
password: *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3
authentication_string:
1 row in set (0.00 sec)
$ mysql -htest-56.xxx -ujoe -ppassw0rd -e 'select "success!"'
Warning: Using a password on the command line interface can be insecure.
+----------+
| success! |
+----------+
| success! |
+----------+
mysql> drop user 'joe'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> select password('passw0rd');
+-------------------------------------------+
| password('passw0rd') |
+-------------------------------------------+
| *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3 |
+-------------------------------------------+
1 row in set (0.02 sec)
mysql> create user 'joe'@'%' identified with mysql_native_password as '*74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, host, plugin, password, authentication_string from mysql.user where user = 'joe'\G
*************************** 1. row ***************************
user: joe
host: %
plugin: mysql_native_password
password:
authentication_string: *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3
1 row in set (0.00 sec)
$ mysql -htest-56.xxx -ujoe -ppassw0rd -e 'select "success!"'
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'joe'@'172.31.62.244' (using password: YES)
$ mysql -htest-56.xxx -ujoe -e 'select "success!"'
+----------+
| success! |
+----------+
| success! |
+----------+
Notes:
- Note that MySQL 5.7.6+ no longer uses the "password" column to store the password hash. The "authentication_string" column is used instead.
- In MySQL 5.6, the mysql_native_password plugin stores the password hash in the "password" column. Other plugins, such as sha256_password, can use the "authentication_string" column.
Observations:
- Both syntaxes work in MySQL 5.6 and 5.7. No errors, no warnings.
- In 5.7, both syntaxes yield the same result. The user is created, password hash is placed in "authentication_string" column and the user can subsequently log in without issues.
- In 5.6, the IDENTIFIED WITH ... AS syntax produces a different result. The password hash is stored in "authentication_string" column instead of "password" column. Subsequently, the user cannot log in with his password.
- Most importantly, in 5.6 the mysql_native_password still tries to find the hash in the "password" column and if this column is empty, the plugin allows the user to log in without a password.
The conclusion is that 5.6's behavior depends on your syntax. Both syntaxes are documented and valid, but the IDENTIFIED WITH ... AS syntax produces an incorrect result.
The Bug Report
This issue was originally reported 2 years ago by Jesper wisborg Krogh in Bug #78033. The bug was marked as fixed in 5.6.31 and closed. Let's test that.
mysql> select @@version; +------------+ | @@version | +------------+ | 5.6.35-log | +------------+ 1 row in set (0.00 sec) mysql> create user 'joe'@'%' identified with mysql_native_password as '*74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3'; Query OK, 0 rows affected (0.00 sec) mysql> select user, host, plugin, password, authentication_string from mysql.user where user = 'joe'; +------+------+-----------------------+----------+-------------------------------------------+ | user | host | plugin | password | authentication_string | +------+------+-----------------------+----------+-------------------------------------------+ | joe | % | mysql_native_password | | *74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3 | +------+------+-----------------------+----------+-------------------------------------------+ 1 row in set (0.00 sec) $ mysql -htest-5635.xxx -ujoe -ppassw0rd -e 'select "success!"' Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'joe'@'172.31.62.244' (using password: YES) $ mysql -htest-5635.xxx -ujoe -e 'select "success!"' +----------+ | success! | +----------+ | success! | +----------+
Not quite fixed, is it? Question is: what went into 5.6.31 if the issue isn't actually resolved? We can look at the code commit and find out:
- Open the MySQL 5.6.31 changelog.
- Find bug description for #78033 and copy the 2nd bug ID (#21616496).
- Open MySQL code repository and look for commits that contain 21616496.
- Review the bugfix description and code.
Here's what the bugfix says:
"
(...) Fix: The bug is reclassified as a password validation plugin bug. The fix is to throw an error, "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements", when a user is created with 'mysql_native_password'/ 'mysql_old_password' as plugin and using "IDENTIFIED WITH plugin AS 'hash_string'" as syntax. The error is thrown only if the "validate_password" plugin is installed.
"
We can confirm that by looking at the code change, which boils down to throwing an error message if validate_password plugin is installed.
Bottom line: the bug is not resolved. You'll get an error message if and only if you use the password validation plugin (which is not a solution), otherwise the behavior remains unchanged (not even an attempt at fixing the issue).
Why is this a problem?
Someone might say: "this is not even a problem, just use the IDENTIFIED BY syntax that works in both versions.". However, this issue is not merely a nuance or minor inconvenience. To see why, let's look at how the CREATE USER statement is recorded in the binary log.
MySQL 5.6.27
mysql> show master status; +----------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-changelog.000217 | 204 | | | | +----------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> create user 'joe'@'%' identified by 'passw0rd'; Query OK, 0 rows affected (0.01 sec) $ mysqlbinlog --read-from-remote-server -htest-5635.xxx -uxxx -pxxx --start-position=204 -v mysql-bin-changelog.000217 |grep CREATE CREATE USER 'joe'@'%' IDENTIFIED BY PASSWORD '*74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3'
MySQL 5.7.17
mysql> show master status; +----------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-changelog.000242 | 521 | | | | +----------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> create user 'joe'@'%' identified by 'passw0rd'; Query OK, 0 rows affected (0.01 sec) $ mysqlbinlog --read-from-remote-server -htest-57.xxx -uxxx -pxxx --start-position=521 -v mysql-bin-changelog.000242 |grep CREATE CREATE USER 'joe'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3'
Observations:
- MySQL 5.6 binlog contains CREATE USER command in the IDENTIFIED BY syntax.
- MySQL 5.7 binlog uses the IDENTIFIED WITH ... AS syntax.
- Note that this behavior also affects the GRANT statement when used to implicitly create an account.
Result:
- Replication from 5.6 to 5.6 will work fine.
- Replication from 5.6 to 5.7 will work fine as well.
- Replication from 5.7 to 5.6 will not work as expected. You will not receive any replication errors, but all replicated user accounts will have empty passwords, therefore creating a security hole in the replication slave.
Now, MySQL documentation officially states that replication is supported from a major version to the next higher major version. In my opinion however, this statement is not a good enough excuse in this particular case. Imagine the following situation:
- You perform a migration from MySQL 5.6 to 5.7. You use replication to minimize migration downtime.
- Something goes wrong during or shortly after the migration and you're forced to roll back to 5.6. You also use replication in order to minimize rollback downtime. You do not receive any replication errors, so you're convinced that everything is in order.
- During replication-assisted rollback to 5.6, some CREATE or GRANT statement are executed on the 5.7 master replicated to the 5.6 slave.
- These new user accounts will end up being password-less on the 5.6 machine. Anyone can log into these accounts without password.
There are situations when replication cannot and will not be backwards compatible. I still think this particular server behavior should be treated as a bug, even though it only appears in a replication scenario that's not officially supported. Why?
- The behavior can be corrected.
- It causes security issues silently.
- The scenario may not be officially supported, but it's related to a real life use case (migration).
I decided to open a new bug report #86664 and re-qualified it as a security issue. Hopefully MySQL will come up with a better solution this time around.
No comments:
Post a Comment