Bug #72655 mysqluserclone creates user on destination host, even after error
Submitted: 15 May 2014 9:49 Modified: 16 Jul 2014 0:57
Reporter: Shahriyar Rzayev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Utilities Severity:S1 (Critical)
Version:1.4.2 OS:Linux (CentOS 6.5)
Assigned to: CPU Architecture:Any

[15 May 2014 9:49] Shahriyar Rzayev
Description:
Dear all, the problem is very interesting for me so decide to report as bug.
Problem description:

On SOURCE host i have following users:

mysql> select user, host from mysql.user;
+---------+--------------+
| user    | host         |
+---------+--------------+
| remote  | %            |
| repl    | %            |
| ssluser | %            |
| root    | 127.0.0.1    |
| root    | 192.168.1.88 |
| root    | ::1          |
| root    | localhost    |
+---------+--------------+
7 rows in set (0,00 sec)

mysql> show grants for 'remote'@'%';
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for remote@%                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'remote'@'%' IDENTIFIED BY PASSWORD '*4DAE1A09DE8DE8D1A55DF55DE9556461EC87FDE2' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

On DESTINATION host i have following users:

mysql> select user, host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| remote | %         |
| root   | 127.0.0.1 |
| root   | ::1       |
| root   | localhost |
+--------+-----------+
4 rows in set (0,00 sec)

Tried to clone user:

[root@linuxsrv3 ~]# mysqluserclone --source=root:12345@localhost --destination=remote:12345@192.168.1.88 'remote'@'%' xxx2:12345@localhost
# Source on localhost: ... connected.
# Destination on 192.168.1.88: ... connected.
# Cloning 1 users...
# Cloning remote@% to user xxx2:12345@localhost 
ERROR: Query failed. 1045 (28000): Access denied for user 'remote'@'%' (using password: YES)

So the first problem is related to error message: (using password: YES) -> i did not type any password here but it shows with "YES"

Second problem is, even the error occured on source side, the xxx2@localhost user is created on destination host:

mysql> select user, host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| remote | %         |
| root   | 127.0.0.1 |
| root   | ::1       |
| root   | localhost |
| xxx2   | localhost |
+--------+-----------+
5 rows in set (0,00 sec)

And the another terrible thing is that, the user have not grants from source user:

mysql> show grants for 'xxx2'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for xxx2@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xxx2'@'localhost' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

How to repeat:
Create remote@% user on source host with all privileges.
Create remote@% user on destination host with all privileges for connecting from source host.
Use following command to clone remote@% user from source host to xxx2@localhost on destination host:

mysqluserclone --source=root:12345@localhost --destination=remote:12345@192.168.1.88 'remote'@'%' xxx2:12345@localhost

Suggested fix:
Prevent user creation on destination host if there was any error on source side.
Unclear error message:
ERROR: Query failed. 1045 (28000): Access denied for user 'remote'@'%' (using password: YES)
[15 May 2014 10:59] MySQL Verification Team
Hello Shahriyar,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[15 May 2014 11:01] MySQL Verification Team
Before cloning

//Source
mysql> select user,host,`password` from mysql.user;
+---------------+--------------+-------------------------------------------+
| user          | host         | password                                  |
+---------------+--------------+-------------------------------------------+
| root          | localhost    |                                           |
| ushastry      | %            | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ushastry      | localhost    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

//Destination user privileges

mysql> show grants for bug@localhost;
+---------------------------------------------------------------------------------------------------------------------+
| Grants for bug@localhost                                                                                            |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'bug'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

// Try to clone

 mysql-utilities-1.4.2]#  scripts/mysqluserclone --source=root@localhost --destination=bug:123@localhost ushastry@localhost ShudNotBThere@localhost --force # Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 1 users...
# Cloning ushastry@localhost to user ShudNotBThere@localhost
ERROR: Query failed. 1045 (28000): Access denied for user 'bug'@'localhost' (using password: YES)

After failed attempt(interestingly, destination user doesn't have GRANT OPTION, and that's the reason for error) user is cloned

| root          | localhost    |                                           |
| bug           | localhost    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ShudNotBThere | localhost    |                                           |

Imho - It should not create user on destination host at all considering the fact that user used for connecting remote server doesn't have GRANT option
And/Or the error should be more meaningful than just access denied...
[16 Jul 2014 0:57] Philip Olson
Fixed as of the upcoming MySQL Utilities/Fabric 1.4.4 release, and here's the changelog entry:

When cloning a user, the "mysqluserclone" utility did not check if the
user of the destination server had the required privileges for a
successful operation.

Thank you for the bug report.