Bug – Stored Procedures Lose Priviledges

[Brian Hopkins]

Hello, we have users whose only priviledges are to execute various stored procedures. This works fine… UNTIL we edit & save the stored procedure. SQLYOG then clears all user priviledges associated with the procedure.

We use procedures extensively. Every change we make to a procedure we have to remember which user has priviledges to the procedure & go back and reset them. This is causing A LOT of hassle & causing us to investigate other solutions.

Please let me know how to remedy this problem.

Thanks

[vishal.pr]

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

[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

[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

[peterlaursen]

Well .. if you DROP an object I think it is pretty fair also to DROP related privileges.  But there should be some option to ALTER PROCEDUREBODY without losing privileges.

But thanks for your understanding!

[Author]

Posts