Import mysql dump files with view definer and sql security
Views and mysqldump
A little back I tried to backup a database from my webhost and restore it on my localhost mysql. It had a few views. The import of the sql file seemed to work fine. But on accessing the views in phpmyadmin mysql gave the error :
#1449 - There is no 'projects'@'localhost' registered
The problem is with a security feature for views. It defines which user created a view and which user can view it or open it. Now the thing is, for views to correctly import and work on other systems, the same username much exist. This is not possible in all situations. So had to look for methods to fix the issue.
The dump file had lines like this which defined the definer of the views
/*!50013 DEFINER=`projects`@`localhost` SQL SECURITY DEFINER */
This line appeared above every view definition. So the simple reason was that [email protected] was the 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.
Mysql would add this line to the dumps created by mysqldump always, and there is no way to prevent this. So the only solution we are left with, is to erase this from the dump file. This can be done in a number of ways.
Using the sed command
sed '/^/*!50013/d' backup.sql > backup_without_50013.sql
Process with php
The dump file may be processed inside php using the preg_replace function.
$contents = file_get_contents('backups/backup.sql'); $contents = preg_replace('@/*!50013 DEFINER.*?*/@' , '' , $contents); file_put_contents('backups/backup.sql' , $contents);
The above solutions worked.
But I was still looking for a technique where I didnt have to modify the dump file.
The solution is to create a view with sql security invoker like this :
CREATE SQL SECURITY invoker VIEW my_view as select .......
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.