asked on

MySQL triggers and stored procedures, automatic created user

Hello,

each time we add triggers and stored procedures, MySQL add automatically the user who execute the CREATE Procedure/Trigger as new recored (without permissions) to the "mysql.user" table with a host entry matching to the MySQL Workbench Cllient IP, so next time we connect with this user from this client to the database we have no permissions. How to fix this problem, what is the best practice for this?

regards
Ricardo

PantherMedia

ASKER

addition comment:

It happens only for stored procedures! MySQL Version 5.1.45 on Redhat Linux.
We tried now also with MySQL Version 5.1.49 on Debian and it works witout problems.

Bug or Feature? ;-)

We will try till monday also on Redhat with MySQL 5.1.49 or rather on both with 5.1.51

Kevin Cross

Hmmm. I am not sure I have ever seen that behavior, but I have only used MySQL on Ubuntu, SuSE and CentOS flavors of Linux. You can try to specify the definer, like:
( http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html )

CREATE DEFINER = 'root'@'localhost' PROCEDURE proc()
BEGIN
  -- your procedure body here
END;

Open in new window

PantherMedia

ASKER

Hi mwvisa1,

thanks for you comment, but it is defined!

We tried now the 5.1.51 on Debian successfully. No problems for my case on Debian like with version 5.1.49. I will give here Feedback after we tried the version 5.1.51 on Redhat.

Kevin Cross

What do you mean it is defined. You said it was automatically choosing the creating user. I am suggesting you explicitly use the DEFINER syntax shown and set it to 'root' or admin user. This should eliminate the issue of it writing a record for the current user who created the procedure which is what it would do if you didn't specific DEFINER = '' as shown above.

PantherMedia

ASKER

Hello mwvisa1,
as I write it happens only on MySQL Version 5.1.45 on Redhat Linux, with definer or without make no difference.

Kevin Cross

Interesting. Yes that definitely sounds like a bug. Please post back after the upgrade. If I run across a similar situation and a fix, I will post back; otherwise, I look forward to hearing the updated results from you. If you want additional input, you can ask the Moderators via Request Attention to call for Experts; however, sounds like you have a handle on this being a version / OS specific oddity and have a plan of action to test next.

PantherMedia

ASKER

Status Update:

it happen also on MySQL Version 5.1.51 on Redhat Linux
on MySQL Version 5.1.51 on Debian Linux it works without this problem!

PantherMedia

ASKER

I've requested that this question be deleted for the following reason:

was really a bug on RedHat

Kevin Cross

Please consider posting details of the bug, i.e., a thread with RedHat on the topic OR information on what you did to resolve -- as in what version of RedHat you upgraded from/to. Then you can accept that post as the answer. It may help someone else on RedHat using MySQL in the future.

Kevin

ASKER CERTIFIED SOLUTION

PantherMedia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

Wonderful! I am posting an official 'Objection' so you can accept http:#36967408 as the answer.
Best regards and happy coding,

Kevin