no sql security definer / please
Have you ever had it happen that you removed a MySQL user and suddently parts of your application stopped working? Not because you removed the user that was connecting, but because you removed the user that defined the particular view or function that you were using.
I have, and it was quite stressful ;-)
We moved a slave machine to a different IP, I updated the mysql.user
host column, and BAM, the application running on the master mysql
stopped working.
The cause: some views and functions had been defined through a connection that originated on the slave machine. This caused the DEFINER= to be set to that user. This user did not exist anymore for that particular IP. The result?
mysql> SELECT * FROM my_view;
ERROR 1449 (HY000): There is no 'walter'@'old-host' registered
It turns out that MySQL default view/function creation is in “SQL SECURITY DEFINER” mode, for some unfathomable reason. This is like having SETUID on all your binaries by default.
The solution is to not do that. You will have to re-create your
views and functions, adding SQL SECURITY INVOKER to the definition SQL
(DDL).
Run this in your cron every night to pester you that you still have some
work to do:
warn-mysql-definer.sh
(view) is a
little script that checks the security type of your views and functions
and spits out a warning if you’re using DEFINER-type security.
Run this before or after your MySQL backup (view) and take care of the problem before it bites you.
P.S.
The MySQL docs have this to say about the unfathomable:
The standard says that the definer of the view, which is the same as the owner of the view’s schema, gets applicable privileges on the view (for example, SELECT) and may grant them. MySQL has no concept of a schema “owner”, so MySQL adds a clause to identify the definer.
I think I would know it when I remove a schema owner. Removing a random user that may have had write powers in the past is not something that should trigger breakage.