MySql Administrator and editing stored procedures

[jez]

Hi Everyone,

I am trying to make some changes and additions to an existing database.

I have access to the server its running on and i have root access to the database.
It is running on Solaris, so any changes made on the server have to be done at the command line or i connect with the Admin GUI (remotely).

When you create a stored proc with the GUI it puts in a a DEFINER i the form of root@machinename.domain.com
This is fine for new procs, but when i try and edit an existing one which has been created on a different machine i get an error about not being a SUPER user and then deletes the stored procedure from view (i hope not from the DB!!).

So, can anyone tell me how to edit these stored procedures?
I have tried running GRANT ALL ON [db name] to root@mymachine
but that does not work, if i try and add SUPER then i get an error about bad usage of grant command.


I have access to the system to change things, but it seems this situation is caused by the GUI being used to create procedures. Is this a flaw in the GUI or should things be this tied down normally? I can understand why there is the definer option, but should it be used always?

Thanks.

 

[jez]

Workaround....

Dump the DB to a sql file, find and replace the definer on each SP to root@'%' although this does not allow for editing of them.

I do like MySql, but with the intro of SPs, Triggers and Views, dumps trip over when re-importing, SPs cannot be edited in any useful way and all round its become a rather poor version of Oracle.

 

[DonQuichote]

Do you connect using machinename.domain.com? Or using "localhost" or an internal IP address? It may be that your "root" user is not the same as the "root" user that has created the routines.

so any changes made on the server have to be done at the command line or i connect with the Admin GUI (remotely)

If you connect remotely through an SSH tunnel, you can effectively come from 127.0.0.1 (which is NOT the same as localhost in MySQL).


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[jez]

It may be that your "root" user is not the same as the "root" user that has created the routines.

Yep that is exactly the problem.
I connect to the db with simply [machinename] which works as it is one the same domain.

The user i put into the dialog box is simply root.
It is the Administrator program itself that adds the definer, automatically giving me the full username with [domainname].com

This has not been a problem before as the creator of the DB and associated system was doing everything.
I have now taken over and this issue has come to light.

I am happy to use the mysql shell, but not so much for SPs as some of them are very long and i have only a small change to make (e.g. additional columns to retrieve).

Another workaround would be to use the Query browser, then i could definer a definer myself, making it more generic to the server and unrelated to my local machine.

 

[DonQuichote]

I never define a "definer" when writing a stored routine. What I meant was, the "root" user you now use may not have the "super" privilege, whereas your predecessor's account does. If you can log in as root@localhost from the server's command-line, you may be able to grant your new root account super privileges.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[jez]

Oh i see.

One of the things i am trying to do is to simplify and standardise the system. So if i remove all definer's from the stored procedures then they will run as the user that connects to the database from the web system. (not root).

This will mean that i can set the appropriate access to that user and that will be maintained in the SPs.
e.g. the account used by web system will not have CREATE or GRANT privileges, and then any SP trying to do those will fail, (which is a good thing).

Thanks for your help.