Mysql View DEFINER and SQL Security

Taking backup from the DB server, you might got an issue of definer. Me also faced this type of issue, I would like to share here about my experience how i fix it. Error could be like as below:

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 is msilink user who created the view (on the server) and when we imported the database dump on local system then there is no user named ‘mslink’ in mysql.

Appearance of user in your dump file can not prevented whenever you take a dump from the DB. So, possible solution is to erase it from the dump file.

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

Above solutions worked for me.

Solution should be to create a view with sql security invoker like as below:

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, Run the following query:

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

That also solve your problem.