who is “Definer” in terms of MySQL

Today I was playing with our database settings and trying to grant/ cut permissions of users from the development DB and production DB, changing passwords and so on.
Using our primitive hosting’s control panel I wanted to remove the main production DB user from Development DB, which I ended up loosing the user completely.
I created a new user/pass and in few seconds I changed the configurations. Evertyhing looked fine till I found that something is missing and not working properly.

The problem was on one of our scripts and the error was :

The user specified as a definer : (‘no_such_user’@’no_such_host’) is invalid or unregistered

And thank to our primitive control panel, I was not able to recreate the user as it contains “_” [underscore] in the username.

All the settings were fine and I was wondering what’s causing the problem. At first rush I thought that it could be a cache, as I had requested recently to have xcache on our server.

But it wasn’t the case. The error was much more stupid than even one can imagine. That specific script uses a stored procedure to insert / fetch data to/from MySQL. The user who had created the sp was the one who was deleted. And that was the problem. The term “Definer” in terms of MySQL is the one who creates the stored procedure and for the stored procedure to be executed that user must exists.

A quick solution for that was dropping the old stored procedure and re-create it while logged in to MySQL with the new user.

Voila, it solved our issue. Not the best solution but at least I figured out what “Definer” means and it must exists when a sp is being called.

Advertisements

8 thoughts on “who is “Definer” in terms of MySQL

  1. This is really cool, I tried with replacing the user from the ‘routine’ which has sp/function, didnot work. But the above solution works fine.

  2. I have been writing Stored Procedures for 20 years, Sybase before SQL Server. In my most humble opinion this mySQL definer parameter is a complete broken concept. IT blocks portability of procedures. I know mySQL is new to the SP world, but I hope they will stick with some basic syntax that has been in use for decades.

    • I totally agree, this “Definer” concept completely blocks the portability of mySQL SPs.
      Also makes it much harder for moving around the backup files to restore in different machines.

  3. Additionally, the use of “SQL SECURITY INVOKER” is a better solution than fiddling with the “DEFINER”. It also leaves the security and the table level, which is ‘mo betta’ than security being defined at each procedure.

  4. Hi, i am facing #42000SELECT command denied user@

    Finally i came to know that its due to DEFINER in SP
    for your information i am using godaddy shared hosting, when i create new SP, DEFINER is automatically creating. How to remove DEFINER?

    • As far as I know you can not remove the DEFINER.
      You either have to create the SP with the exact same user who queries the DB, or GRANT EXECUTE privileges to the user which runs the query.
      Let me know if that worked.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s