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
host column, and BAM, the application running on the master mysql
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
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.
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.