MySQL Bugs: #96787: mysqldbcopy uses incorrect DDL to clone stored procedure call permission

Bug #96787	mysqldbcopy uses incorrect DDL to clone stored procedure call permission
Submitted:	7 Sep 2019 19:55	Modified:	9 Sep 2019 6:41
Reporter:	Pavel Yazev	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.6.4, 1.6.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DDL, grant, mysqldbcopy, permission

Description:
I run 5.7.27 MySQL Community Server (GPL) and trying to use mysqldbcopy utility to create a copy of my production db for development activities.

There is a problem when it comes to GRANTS transfer.

I see that mysqldbcopy process terminates with an error during "GRANT EXECUTE" statement:

# Copying GRANTS from Schema_A
GRANT Execute ON `Schema_B`.Procedure_A TO 'User_A'@'%'
ERROR: Cannot operate on GRANT object. Error: Query failed. 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

How to repeat:
1. Create a schema, let's say Schema_A with a stored procedure Procedure_A:

CREATE SCHEMA `Schema_A`;

USE `Schema_A`;
DROP procedure IF EXISTS `Procedure_A`;

DELIMITER $$
USE `Schema_A`$$
CREATE PROCEDURE `Procedure_A`()
BEGIN

END$$

DELIMITER ;

2. Grant EXECUTE permission to an user:

GRANT EXECUTE ON PROCEDURE `Schema_A`.Procedure_A TO 'User_A'@'%';

3. Run the DB Copy tool:

# mysqldbcopy --verbose --source=./.source.cnf --destination=./.destination.cnf Schema_A:Schema_B
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database Schema_A renamed as Schema_B
# Copying PROCEDURE Schema_A.Procedure_A
CREATE PROCEDURE `Procedure_A`()
BEGIN

END
# Copying GRANTS from Schema_A
GRANT Execute ON `Schema_B`.Procedure_A TO 'User_A'@'%'
ERROR: Cannot operate on GRANT object. Error: Query failed. 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

Suggested fix:
I have checked the MySQL GRANT syntax, and it seems that the correct statement would include "PROCEDURE" or "FUNCTION":

GRANT Execute ON PROCEDURE `Schema_B`.Procedure_A TO 'User_A'@'%'.

Need to fix the statement generation algorithm of the tool.

I have also checked the latest version of the tool (1.6.5) and see that the problem persists here as well.

The below code is erroneous:
[./mysql/utilities/common/database.py]
 391         # Tables are not supported
 392         if obj_type == _TABLE and self.cloning:
 393             return None
 394         # Grants are a different animal!
 395         if obj_type == _GRANT:
 396             if obj[3]:
 397                 create_str = "GRANT %s ON %s.%s TO %s" % \
 398                              (obj[1], self.q_new_db, obj[3], obj[0])
 399             else:
 400                 create_str = "GRANT %s ON %s.* TO %s" % \
 401                              (obj[1], self.q_new_db, obj[0])

It could be fixed as shown below (lines w/o numbers are added by me):
 391         # Tables are not supported
 392         if obj_type == _TABLE and self.cloning:
 393             return None
 394         # Grants are a different animal!
 395         if obj_type == _GRANT:
 396             if obj[3]:
  +                  if obj[5] in ['PROCEDURE', 'FUNCTION']:
  +                      create_str = "GRANT %s ON %s %s.%s TO %s" % \
  +                                   (obj[1], obj[5], self.q_new_db, obj[3], obj[0])
  +                  else:
 397                     create_str = "GRANT %s ON %s.%s TO %s" % \
 398                                  (obj[1], self.q_new_db, obj[3], obj[0])
 399             else:
 400                 create_str = "GRANT %s ON %s.* TO %s" % \
 401                              (obj[1], self.q_new_db, obj[0])

Thus the PROCEDURE/FUNCTION keyword is added into the statement.

Hello Pavel Yazev,

Thank you for the report and feedback.

regards,
Umesh