Loosing Grant Editing Stored Procedure

[ema]

Editing a stored procedure (SP) by right clicking on “Alter Stored Procedure…” on it and then saving changes by clicking on “Execute All Queries (Shift+F9)” make the procedure loose execute rights given by following command:

grant execute on procedure foo to myuser;

This is quite logical, because the “Execute All Queries” button execute the sql code shown in the editor, where there's no any grant command. So this is not a bug…

But it's not a good way to perform changes on SP: it would be boring to manually take note of grants privileges on the SP before the editing and then reassing them manually after the changes.

Have you something to suggest?

I installed a 30-days tryal of SQLyog, version 8.55

Thanxs!

PS. Sorry for my bad english 😉

[peterlaursen]

Please tell: did you rename the SP when editing?  Or are you telling that (temporarily) DROPPING it will remove the privilege to it?

[ema]

'peterlaursen' wrote on '10:

Please tell: did you rename the SP when editing?  Or are you telling that (temporarily) DROPPING it will remove the privilege to it?

I didn't rename the SP.

I say that: we know there's no a real ALTER command for SP.

The standar way i know to alter object like SP is, in practice, doing 2 steps: drop the SP (the drop of one object implies the delete of its grants) and create the modified version.

I used in the past other editing tools (for different dataserver than MySQL): while editing an object like SP, they automatically remember grants existing before the drop and re-apply them after the create.

That is, the sequence of sql statements they execute to alter a SP includes “grant execute” command too, at the end (if grants did exist previously).

I don't know if it's possible with SQLyog too. If so, i didn't find the way.

Thanks for your interest!

[vishal.pr]

Hi,

We understood your requirement. Currently there is no way you can do this with SQLyog. Why because, there may be many users having privileges on the same SP. Then while altering we many need to generate/execute the grant statements for all those users also, which is not safe and can fail if the user trying to alter the SP don't have GRANT privilege.

Thank you,

Vishal P.R

[peterlaursen]

ALTER PROCEDURE statement cannot be used, refer to:

http://www.webyog.com/faq/content/26/175/en/i-have-full-privileges-to-a-database-but-cannot-alter-a-routine-why.html

[ema]

'vishal.pr' wrote on '11:

Hi,

We understood your requirement. […] privilege.

Thanks to you, even though the bad news 🙂

I think I could add a suggestion in the appropriate forum branch…

Making the feature optional, I think it would not be so unsafe as you wrote.

[ema]

'peterlaursen' wrote on '11:

ALTER PROCEDURE statement cannot be used, refer to:

http://www.webyog.com/faq/content/26/175/en/i-have-full-privileges-to-a-database-but-cannot-alter-a-routine-why.html

It is not the answer to my question. Maybe, I couldn't explain very well. 😀

Anyway thanks!

[Author]

Posts