How to preseve Execute permissions for MySQL procedure with alter procedure (Drop/Create)

Question

In MySQL you can not alter procedure rather you have to perform drop/create procedure to alter the body of the procedure. The problem is doing drop/create also removes the execute permissions given on that selected procedure.

Is there any way of giving permanent execute permissions that remains even after drop/create procedure.

A sample code to verify is as below

delimiter //
create procedure t1 ()
select 1;
//

grant execute on procedure t1 to test1@'%';

call t1() works well, but if you run the above code after doing drop procedure first it gives permissions error.

Any help is much appreciated

Answer 1

Best is to grant execute at the DB level:

grant execute on db.* to test1@'10.%'; (don't use %, too risk, use ip subnet) This way you don't need to grant permission in SP.

Answer 2

I think there are 2 things controlling it:

CREATE PROCEDURE ... SECURITY INVOKER (or DEFINER)

And

GRANT ... EXECUTE ... TO 'user'@'host';

If your recreation of the proc fails to include all the flags (DETERMINISTIC, SECURITY, etc), then that is lost. And if you build it as root, with DEFINER, then other users cannot execute it.

I doubt if the GRANT is lost due to the DROP PROCEDURE -- after all, the EXECUTE is general, not specific to the proc.

Answer 3

Found that this could be achieved by following steps

turn off variable automatic_sp_privileges

set global automatic_sp_privileges  = 0

Now give the execute grants on procedure to different users

drop and re-create the procedure again and the grants are still present, have also added this to ini file to make this change permanent.

Thanks all for the help