Execute grant for MySQL stored procedure revoked when stored procedure is altered

Question

I have created the following stored procedure in a MySQL database (MySQL 5.5).

DELIMITER $$

CREATE DEFINER=`remote_admin`@`%` PROCEDURE `usp_insert_deal_and_segment`()
BEGIN

    "code here"

END$$

This stored procedure is then used by multiple different scripts to insert data into the database.

There are a number of external scripts calling this stored procedure. These scripts use a different user when accessing the database (i.e. 'script_tradecap'@'127.0.0.1' rather than 'remote_admin'@'%').

So that the scripts are able to access this stored procedure the user 'script_tradecap'@'127.0.0.1' is given the following grants.

mysql> show grants for `script_tradecap`@`127.0.0.1`;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'script_tradecap'@'127.0.0.1' IDENTIFIED BY PASSWORD '*59195B949A078D5B540E25E9800C85AF83344E9D' |
| GRANT EXECUTE ON PROCEDURE `gudrun`.`usp_insert_deal_and_segment` TO 'script_tradecap'@'127.0.0.1'                     |
+------------------------------------------------------------------------------------------------------------------------+ 

Note that I specifically want to limit this user so that it can only execute the usp_insert_deal_and_segment stored procedure. I don't want the user to be able to execute other stored procedures.

After that the scripts are able to access the stored procedure via this user.

The problem occurs when I alter the stored procedure. This results in the execute on procedure grant for usp_insert_deal_and_segment being revoked from the 'script_tradecap'@'127.0.0.1' user.

Why is this happening and is it possible to prevent the grant being revoked if the stored procedure is altered?

Thanks,

Gerard

Answer 1

This happens because the stored procedure is actually dropped and recreated. When the procedure is dropped it loses all its permissions. Therefore, when the 'altered' version is created there are no permissions attached.

Your process to update the procedure needs to include a re-grant of the needed permissions.

I don't know your full environment, but here was a 2013 forum discussion for one MySQL tool set: http://code.google.com/p/heidisql/issues/detail?id=3300