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

[13 Jan 2011 18:19] Andrew Morgan
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;
[14 Jan 2011 15:30] Chuck Bell
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'?
[24 Jan 2011 8:39] Andrew Morgan
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?
[25 Jan 2011 14:55] Andrew Morgan
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;
[28 Jan 2011 16:06] Chuck Bell
It turns out this is more widespread than mysqldbexport also affected are mysqldbcopy and mysqlimport.
[28 Jan 2011 18:56] Chuck Bell
Patch ready for review

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

[10 Feb 2011 14:57] Chuck Bell
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.
[10 Feb 2011 14:57] Chuck Bell
Latest patch with updated commit message

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

[9 Jul 2012 19:10] Chuck Bell
Fixed in 1.0.1.