SQLyog FAQ

You experience that you have full privileges to a database (described in GRANT syntax like GRANT ALL PRIVILEGES ON some_database.* TO 'me'@'somehost') but cannot ALTER a routine (Stored Procedure or Stored Function) from SQLyog GUI. You are able to CREATE a routine and DROP same, and you may even have noticed that SQLyog does not generate an ALTER statement, but instead DROPs the existing routine and CREATEs it again.

So the question can be written like this: I can CREATE and DROP in two seperate operations but not DROP+CREATE in one operation. To understand this there are two important things to notice:

1) We do not use the ALTER routine syntax because it is not possible to change the routine body (or the 'code' if you like) with such ALTER statement. MySQL documentation describes here: http://dev.mysql.com/doc/refman/5.1/en/alter-procedure.html

So if we want to let user change the routine body there is only one way: DROP + (re)CREATE. And that is what we do.

2) We also need to display the current routine body for user. In order to get this information from the server we execute SHOW CREATE PROCEDURE .. (or FUNCTION). However that only returns the routine body if user has SELECT privilege to ´mysql´.´proc´ table.  MySQL documentation describes here: http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html

So in conclusion ALL PRIVILEGES ON some_database.* TO 'me'@'somehost' is not enough for a user to ALTER a routine - also SELECT ON ´mysql´.´proc´ TO 'me'@'somehost' is required for 'me'@'somehost' to be able to use SQLyog ALTER routine functionality.