MySQL triggers and stored procedures, automatic created user

PantherMedia
PantherMedia used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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 CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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

Author

Commented:
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.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.

Author

Commented:
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 CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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.

Author

Commented:
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!

Author

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

was really a bug on RedHat
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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
Newer version of MySQL for RedHat solved that problem!

!!! MySQL 5.1.57 !!!
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

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

Kevin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial