Changing the DEFINER of MySQL stored routines etc.

The question seemed easy enough:

We’ve dropped a user, now we want to change the DEFINER on all database objects that currently have it set to this dropped user?

This should be possible by checking the INFORMATION_SCHEMA tables of the appropriate object types (routines, triggers, views and events) and performing an ALTER on each of them that just modifies the DEFINER but nothing else, right?

Unfortunately it isn’t that easy, or at least not yet (see http://bugs.mysql.com/73894 and https://mariadb.atlassian.net/browse/MDEV-6731 ).

Events are closest to this as ALTER EVENT does support changing the definer, but it also requires that at least one other event attribute gets changed at the same time, e.g.:

ALTER DEFINER='user'@'host' EVENT e1 COMMENT '';

ALTER VIEW also allows to change the definer, but here you also need to repeat the views AS SELECT.

Fortunately these extra settings can be extracted from the information schema, so while a bit of extra work is required it is at least easily scriptable.

Stored routines are a bit more tricky as ALTER PROCEDURE and ALTER FUNCTION do not provide a way to change the definer. And triggers don’t even have an ALTER TRIGGER at all.

So here we have to fall back to extracting the objects CREATE statement with SHOW CREATE, replace the DEFINER (its fortunately in a fixed position within the SHOW CREATE output), drop the routine or trigger, and then recreate it with the modified CREATE statement right away.

This DROP/CREATE approach obviously opens up the opportunity for a race condition. With triggers we can take care of this by locking the triggers base table with LOCK TABLES … WRITE, so preventing table data changes and so also trigger invocations.

For procedures and functions there’s a small time span between DROP and CREATE where invocations of these routines may fail though, and I don’t know any way to prevent this …

And along the way we need to make sure that we take care of setting the sql_mode, character_set_client and collation_connection variables to the same values they had when the objects were originally created. These are stored along with the objects and exposed in their respective information schema tables, and it turned out that on DROP/CREATE (obviously) and ALTER (less obvious) the current sessions settings override the stored ones.

All in all I ended up with the following quick and dirty PHP script for what should only have been as simple as:

  foreach view, event, trigger, routine as object
    foreach object with definer=old_user 
      ALTER object DEFINER=new_user

The PHP script source lives on GitHub at https://github.com/hholzgra/change_definer.