Mysql View DEFINER and SQL Security

An article by Binary Tides    2 Comments

A little back I tried to backup a database from my webhost and restored it on my localhost mysql. It had a few views. On accessing the views in phpmyadmin mysql gave the error :

#1449 – There is no ‘projects’@'localhost’ registered

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

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

This line appeared in 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.

There appeared to be no way by which this line could be prevented from coming up in the dump file. So a possible solution was to erase it from the dump file.

On ubuntu like this :

sed ‘/^\/\*\!50013/d’ backup.sql > backup_without_50013.sql

in PHP I tried this :

$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.

Popularity: 14% [?]