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 projects@localhost 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
The sed
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.
On Linux machines you can use this one-liner to redefine definer for existing views:
mysql -uuser -ppwd -A –skip-column-names -e”SELECT CONCAT(‘SHOW CREATE VIEW ‘,table_schema,’.’,table_name,’;’) FROM information_schema.tables WHERE engine IS NULL and table_schema like ‘mydb%'” | mysql -uuser -ppwd -A –skip-column-names | sed -rn ‘s/.*?VIEW ([^s]+?) (AS .*?)s([^s]+?)s([^s]+?)/DROP VIEW 1;nCREATE VIEW 1 2;/p’ | mysql -uuser -ppwd -A –skip-column-names
You have only to replace strings in bold with your DB user credentials and database name / like pattern.
More info here: http://blog.novoj.net/2014/05/16/recreate-mysql-views-without-definer-one-liner-solution-linux/
veggen is right. Views with SQL SECURITY DEFINER is a mechanism to provide data to users that otherwise wouldn’t have the privileges to execute the SELECT query that defines the VIEW.
In other words, the VIEW’s SELECT query (or anything within a FUNCTION or PROCEDURE) is executed with (typically) elevated privileges.
I don’t think you got this right. Using INVOKER security context means that the view/procedure/function will execute with the privileges of the invoker. On the other hand, using DEFINER security context, means the view will execute with definer’s privileges so, in this case, invoker’s privileges are irrelevant.