Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
-
OS: CloudLinux release 7.6 (Vladimir Lyakhov)
Kernel: 3.10.0-962.3.2.lve1.5.24.7.el7.x86_64
Arch: x86_64
Environment: kvm
CPU: Intel Xeon E312xx (Sandy Bridge) w/ 4 core(s)
Description
- This is likely a round 3 for
MDEV-7985
DetailsMariaDB 10.2 introduced the 'SHOW CREATE USER' syntax: https://mariadb.com/kb/en/library/show-create-user/
This was added via MDEV-7978: https://github.com/MariaDB/server/commit/5e873141a57fcf8e0f5bc8dd4e5434073e78f43f#diff-dca2f11b2511ceff9960dc3bcd972d04
The issue that this commit presents is that neither 'SHOW GRANTS FOR $user' or 'SHOW CREATE USER $user' will show a valid 'IDENTIFIED BY' line for a user if the 'plugin' column is set to 'mysql_native_password' AND the 'authentication_string' column is NULL.
For example:
# mysql mysql -e "select User,Host,plugin,Password,authentication_string from user where User='cptest_testuser';"
|
+-----------------+-----------+-----------------------+-------------------------------------------+-----------------------+
|
| User | Host | plugin | Password | authentication_string |
|
+-----------------+-----------+-----------------------+-------------------------------------------+-----------------------+
|
| cptest_testuser | localhost | mysql_native_password | *4AAD86FE22D73A14522B16350DAE469EDC645BE0 | |
|
|
|
# mysql -e "show grants for 'cptest_testuser'@'localhost';"
|
+-----------------------------------------------------------------------------+
|
| Grants for cptest_testuser@localhost |
|
+-----------------------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO 'cptest_testuser'@'localhost' |
|
| GRANT ALL PRIVILEGES ON `cptest\_testdb`.* TO 'cptest_testuser'@'localhost' |
|
+-----------------------------------------------------------------------------+
|
|
|
# mysql -e "show create user 'cptest_testuser'@'localhost';"
|
+-------------------------------------------+
|
| CREATE USER for cptest_testuser@localhost |
|
+-------------------------------------------+
|
| CREATE USER 'cptest_testuser'@'localhost' |
|
+-------------------------------------------+
|
- This could easily lead someone to believe that the user does not have a password set when in fact it does have a valid password set
- It is possible to run into this issue when updating from 10.1 to 10.2
Suggested Resolution / Expected Behavior- either 'SHOW GRANTS FOR' or 'SHOW CREATE USER' should provide the proper 'IDENTIFIED BY' line for the user's password if the user has a valid password
- add_user_parameters should be looking at the Password column as well as the authentication_string column when the mysql_native_plugin is enabled
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
My apologies. On second look, this issue appears to present itself in 10.1 rather than 10.2
The issue is that SHOW GRANTS does not expose the password for the user in 10.1 under the conditions where the password field is set, auth_string field is empty, and plugin=mysql_native_password.
The behavior change can be seen when upgrading from 10.0 to 10.1, and the issue can be replicated with the upgrade path of mysql 5.6 -> mariadb 10.0 -> mariadb 10.1.
-----------
mysql 5.6
[root@host-172-16-0-61 ~]# mysql --version
|
mysql Ver 14.14 Distrib 5.6.42, for Linux (x86_64) using EditLine wrapper
|
[root@host-172-16-0-61 ~]# mysql -e "create user 'cp56' identified by PASSWORD '*F7AAD85AA7E564892EB4BDBD7B5BD80E9653309F';"
|
[root@host-172-16-0-61 ~]# mysql mysql -e "select User,Host,plugin,Password,authentication_string from user where User='cp56'"
|
+------+------+-----------------------+-------------------------------------------+-----------------------+
|
| User | Host | plugin | Password | authentication_string |
|
+------+------+-----------------------+-------------------------------------------+-----------------------+
|
| cp56 | % | mysql_native_password | *F7AAD85AA7E564892EB4BDBD7B5BD80E9653309F | |
|
+------+------+-----------------------+-------------------------------------------+-----------------------+
|
[root@host-172-16-0-61 ~]# mysql -e "show grants for 'cp56';"
|
+-----------------------------------------------------------------------------------------------------+
|
| Grants for cp56@% |
|
+-----------------------------------------------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO 'cp56'@'%' IDENTIFIED BY PASSWORD '*F7AAD85AA7E564892EB4BDBD7B5BD80E9653309F' |
|
+-----------------------------------------------------------------------------------------------------+
|
mariadb 10.0
[root@host-172-16-0-61 ~]# mysql --version
|
mysql Ver 15.1 Distrib 10.0.37-MariaDB, for Linux (x86_64) using readline 5.1
|
[root@host-172-16-0-61 ~]# mysql mysql -e "select User,Host,plugin,Password,authentication_string from user where User='cp56'"
|
+------+------+-----------------------+-------------------------------------------+-----------------------+
|
| User | Host | plugin | Password | authentication_string |
|
+------+------+-----------------------+-------------------------------------------+-----------------------+
|
| cp56 | % | mysql_native_password | *F7AAD85AA7E564892EB4BDBD7B5BD80E9653309F | |
|
+------+------+-----------------------+-------------------------------------------+-----------------------+
|
[root@host-172-16-0-61 ~]# mysql -e "show grants for 'cp56';"
|
+-----------------------------------------------------------------------------------------------------+
|
| Grants for cp56@% |
|
+-----------------------------------------------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO 'cp56'@'%' IDENTIFIED BY PASSWORD '*F7AAD85AA7E564892EB4BDBD7B5BD80E9653309F' |
|
+-----------------------------------------------------------------------------------------------------+
|
[root@host-172-16-0-61 ~]#
|
mariadb 10.1
[root@host-172-16-0-61 ~]# mysql --version
|
mysql Ver 15.1 Distrib 10.1.37-MariaDB, for Linux (x86_64) using readline 5.1
|
[root@host-172-16-0-61 ~]# mysql mysql -e "select User,Host,plugin,Password,authentication_string from user where User='cp56'"
|
+------+------+-----------------------+-------------------------------------------+-----------------------+
|
| User | Host | plugin | Password | authentication_string |
|
+------+------+-----------------------+-------------------------------------------+-----------------------+
|
| cp56 | % | mysql_native_password | *F7AAD85AA7E564892EB4BDBD7B5BD80E9653309F | |
|
+------+------+-----------------------+-------------------------------------------+-----------------------+
|
[root@host-172-16-0-61 ~]# mysql -e "show grants for 'cp56';"
|
+----------------------------------+
|
| Grants for cp56@% |
|
+----------------------------------+
|
| GRANT USAGE ON *.* TO 'cp56'@'%' |
|
+----------------------------------+
|
[root@host-172-16-0-61 ~]#
|
Actually, the described problem upon upgrade from MySQL (5.6 at least, I didn't try 5.7+) exists in all 10.1+ versions, there is no argument about it, I was only wondering how you were getting it upon upgrade from 10.1, since you didn't mention MySQL at first.
So, assuming that a user was created in MySQL 5.6 like this:
MySQL [test]> select @@version; |
+--------------+ |
| @@version |
|
+--------------+ |
| 5.6.39-debug |
|
+--------------+ |
1 row in set (0.00 sec) |
|
|
MySQL [test]> create user foo@localhost identified by 'bar'; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MySQL [test]> select user, host, plugin, authentication_string from mysql.user where user='foo'; |
+------+-----------+-----------------------+-----------------------+ |
| user | host | plugin | authentication_string | |
+------+-----------+-----------------------+-----------------------+ |
| foo | localhost | mysql_native_password | |
|
+------+-----------+-----------------------+-----------------------+ |
1 row in set (0.00 sec) |
then direct live upgrade to 10.x causes the following results:
- 10.0: OK before and after mysql_upgrade:
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' |
+------------------------------------------------------------------------------------------------------------+
- 10.1, 10.2, 10.3: Bad before and after mysql_upgrade:
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'localhost' |
+-----------------------------------------+
- 10.4: OK before mysql_upgrade, bad after mysql_upgrade:
before mysql_upgrade
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' |
+------------------------------------------------------------------------------------------------------------+
after mysql_upgrade
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'localhost' |
+-----------------------------------------+
What needs to be done to get this issue when upgrading from 10.1?