[Brian Hopkins]

Vishal, thank you for your prompt response & the links to mysql documentation. This is an idiotic ommission on mysql's part to handle the altering of stored procedures like they do. I will now need to set the execute persmission on the database rather than the procedure. I don't like setting this on a wholesale level like this, but apparently I have no choice.

It would be very handy if webyog made up for this “feature” of mysql.

Anyway – thanks again for your thorough response.

Brian

[Brian Hopkins]

'vishal.pr' wrote on '15:

Hi Brian,

Its not SQLyog that revokes the privileges, but it is MySQL server that revokes the privileges.

Please refer to http://dev.mysql.com/doc/refman/5.0/en/alter-procedure.html

Which says, You cannot change the parameters or body of a stored procedure using ALTER PROCEDURE statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE.

Now refer to http://dev.mysql.com/doc/refman/5.0/en/grant.html

which says, MySQL does not automatically revoke any privileges when you drop a database or table. However, if you drop a routine, any routine-level privileges granted for that routine are revoked.

I hope that explains what is happening in your case. Since ALTER PROCEDURE statement cannot be used to alter the SP parameter/body, SQLyog generates statements to drop and recreate the SP. And on dropping the SP MySQL revokes any privilege applied on that.

However, we had a similar report in the past asking for SQLyog to generate the necessary statements to retain the privileges while altering a stored procedure and it is recorded in our issue tracker.

Please refer to http://code.google.com/p/sqlyog/issues/detail?id=1438

Currently the priority is not set, but let us discuss this in the next team meeting.

Thank you.

Vishal P.R

[Author]

Posts