MySql View DEFINER and SQL Security

Hi,

Some time back, I tried to backup a database from my server and restored it on my localhost mysql server. It had a few views. On taking backup from server i always getting issue i.e. :

mysqldump: Got error: 1449: The user specified as a definer ('msilink'@'localhost') does not exist when using LOCK TABLES

So I opened the dump file and found that the error was being caused by a line which looks like this :

/*!50013 DEFINER=`msilink`@`localhost` SQL SECURITY DEFINER */

This line appeared in every view definition. So the simple reason was that msilink@localhost, user which had created the view (on the server) and when importing the database on local system there was no user named ‘projects’ in MySql.

For that i just open the file and update the line: "50013 DEFINER=`msilink`@`localhost`" with "50013 DEFINER=`root`@`localhost`"

with the following command(In Linux VI Editor):

:%s/50013 DEFINER=`msilink`@`localhost`/50013 DEFINER=`root`@`localhost`/g


then save and close the file. The above solutions worked for me.

Solution is to create a view with SQL security invoker like this :

CREATE
SQL SECURITY INVOKER
VIEW system_users AS SELECT host,user,password FROM mysql.user;

The above statement allows this view to be viewed by any user who invokes the view.
By default the SQL SECURITY is DEFINER which means the definer user can only view it.


:: Alternative Solution ::
Create another user that have all the privileges like as root, viw the following query:

mysql > grant all PRIVILEGES on *.* to msilink@localhost IDENTIFIED BY 'root' WITH GRANT OPTION;

In General:
mysql > grant on to @localhost IDENTIFIED BY '' WITH GRANT OPTION;

That also solve your problem.