[MDEV-18118] MySQL users can break if using mysql_native_plugin in version 10.2 Created: 2019-01-02  Updated: 2019-01-23  Resolved: 2019-01-23

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.1, 10.2, 10.3
Fix Version/s: 10.1.38, 10.2.22, 10.3.13

Type: Bug Priority: Minor
Reporter: Travis Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

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)

Issue Links:
Relates
relates to MDEV-18119 upgrading from 10.3 to 10.4 can resul... Closed

 Description   
Details

MariaDB 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

 Comments   
Comment by Elena Stepanova [ 2019-01-03 ]

What needs to be done to get this issue when upgrading from 10.1?

Comment by Travis [ 2019-01-06 ]

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 ~]#

Comment by Elena Stepanova [ 2019-01-07 ]

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' |
    		 
    +-----------------------------------------+

Comment by Sergei Golubchik [ 2019-01-23 ]

Fixed by commit c2a4bfad2239

Generated at Thu Feb 08 08:41:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.