1

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

0

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.

  • want to know for specific sp only. – Nawaz Sohail Aug 30 '15 at 3:34
  • I don't think it will work to add a GRANT. That would add a row to mysql.db. But only one entry is used for checking grants. That is, you cannot add a grant for '10.%' as extra info. – Rick James Sep 1 '15 at 1:00
  • There is no way around. If you grant at the SP level, permission is stored in procs_priv. If you drop the SP, the permission is removed. To prevent this you need to grant at the DB level. Yes, that accoun will be able to execute all SP, but if this is done thru an App, it's pretty safe. (he/she would have to guess name of all SP and what param to pass). I've always granted execute at DB level and never had issues. – greenlitmysql Sep 1 '15 at 17:33
0

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.

  • 1
    it is lost if it is given to one procedure only, it remains intact if given on DB level. Can you please suggest something to control this behavior? – Nawaz Sohail Sep 8 '15 at 14:14
  • What is lost? Consider filing a feature request at bugs.mysql.com – Rick James Sep 8 '15 at 16:08
  • I think have to do the this.. anyways @Rick James thanks for your input – Nawaz Sohail Sep 8 '15 at 16:32
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.