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

[7 Sep 2019 19:55] Pavel Yazev
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.
[7 Sep 2019 21:16] Pavel Yazev
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.
[9 Sep 2019 6:41] MySQL Verification Team
Hello Pavel Yazev,

Thank you for the report and feedback.

regards,
Umesh

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.