MySQL Bugs: #59478: mysqldbexport claiming root does not have permission to SELECT from the database

Bug #59478	mysqldbexport claiming root does not have permission to SELECT from the database
Submitted:	13 Jan 2011 18:19	Modified:	9 Jul 2012 19:10
Reporter:	Andrew Morgan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	WB 5.2.31	OS:	Any (Windows 7)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
MySQL is running on 192.168.1.8; Workbench on 192.168.1.19.

Permission has been granted for root to access all databases/tables on the server from 192.168.1.19.

Confirm that I can see the tables and data in the clusterdb database through WB but when I try exporting the data it claims root doesn't have permissions:

mysqldbexport --server=root@192.168.1.8 -f T clusterdb;
# Source on 192.168.1.8: ... connected.
ERROR: User root on the Source server does not have permissions to read all objects in clusterdb;. User needs SELECT privilege on clusterdb;.

How to repeat:
mysqldbexport --server=root@192.168.1.8 -f T clusterdb;

Please include all permissions for the root user. A simple 'SHOW GRANTS FOR root@192.168.1.8' would do.

I think what is happening is the code is trying to match 'root@192.168.1.8' (user and host) and not finding a corresponding privilege for clusterdb. You can test this by granting all to root@192.168.1.8 on clusterdb. If that works, we must decide if this is normal behavior. 

Also, what happens when you run the same command from 192.168.1.8 using 'root@localhost'?

As a reminder, MySQL is running on 192.168.1.8 and WB on 192.168.1.19.

root@192.168.1.19 had the appropriate permissions, root@192.168.1.8 did not. I can query this data from within the WB GUI (which is using the root account). Should the utilities not be dependent on the same permissions as they should be running from the WB host rather than the mysqld's host?

Experimented a little more (as before MySQL host = 192.168.1.8 & WB host = 192.168.1.19). 

0) Confirm that root@192.168.1.19 has been granted privileges:
  mysql> SHOW GRANTS FOR root@192.168.1.19;
+------------------------------------------------------+
| Grants for root@192.168.1.19                         |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.19' |
+------------------------------------------------------+

1) Make sure that root@192.168.1.8 does not have permissions
  mysql> revoke all privileges, grant option from 'root'@'192.168.1.8';

2) Run command (from 192.168.1.19)
  C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\utilities>mysqldbexport --server=root@192.168.1.8 -f T clusterdb;
# Source on 192.168.1.8: ... connected.
ERROR: User root on the Source server does not have permissions to read all obje
cts in clusterdb;. User needs SELECT privilege on clusterdb;.

3) Confirm that can read clusterdb database through WB (on 192.168.1.19)

4) Grant permissions to root@192.168.1.8 (shouldn't be needed)
  mysql> grant all on *.* to 'root'@'192.168.1.8';

5) Repeat command - different error this time
  C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\utilities>mysqldbexport --server=root@192.168.1.8 -f T clusterdb;
# Source on 192.168.1.8: ... connected.
ERROR: Source database does not exist - clusterdb;

It turns out this is more widespread than mysqldbexport also affected are mysqldbcopy and mysqlimport.

Patch ready for review

Attachment: 59478.bundle (application/octet-stream, text), 25.24 KiB.

Clarification of the problem as seen from the original report:

When the user root@192.168.1.19 attempted to export data from a database residing on 192.168.1.8, mysqldbexport used the user@host combination of root@192.168.1.8 to check privileges on 192.168.1.8. Since the root user did not have specific grants for root@192.168.1.8, the export failed.

What should have happened is mysqldbexport checked privileges for root@192.168.1.19 which, in this case, that user@host combination did have the correct privileges.

This defect therefore is the code is not forming the correct user@host pairing for remote access privilege checking during export.

Latest patch with updated commit message

Attachment: 59478.bundle (application/octet-stream, text), 25.37 KiB.

Fixed in 1.0.1.