Fixing definers for users that do not exist

Far too often, I get called to troubleshoot a problem because writes or some functionality is no longer working. When you manage thousands of database servers with thousands of databases all with different applications, you run into this problem now and again. When a developer or other DBA is terminated, their MySQL user will also get dropped on all our databases. Sometimes they have created views, events, stored procedures, functions, triggers and the definer for those objects became the user that created them by default. Unless you have monitoring step up to let you know when this happens or some kind of process to fix or prevent it, this can become very wide spread.

Fixing stored procedures, events and functions can be very easy because you can directly manipulate the values in the mysql.events table and mysql.proc table.


 UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='me@%';

 UPDATE `mysql`.`event` p SET definer = 'root@localhost' WHERE definer='me@%';

https://dev.mysql.com/doc/refman/5.6/en//stored-routines-privileges.html

The MySQL manual warns against this with this text:

"The server manipulates the mysql.proc table in response to statements that create, alter, or drop stored routines. It is not supported that the server will notice manual manipulation of this table."

I've tested this by creating a basic stored procedure and changing the definer:

------------------------------------------------------------------
GRANT ALL PRIVILEGES ON *.* TO 'me'@'%' IDENTIFIED BY 'testPASS!';

DROP TABLE t1;
CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS `WriteToTable`;

DELIMITER ;;
CREATE DEFINER=`me`@`%` PROCEDURE `WriteToTable`()
BEGIN
INSERT INTO `t1` (`id`, `timestamp`) VALUES (NULL, NULL);
END;;
DELIMITER ;

call WriteToTable();

DROP USER 'me'@'%';

SELECT * FROM `mysql`.`proc` p  WHERE  name = 'WriteToTable';

UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='me@%' AND name = 'WriteToTable';

call WriteToTable();

------------------------------------------------------------------

When I run this in the same session that was used to update the mysql.proc table, it fails with error because me@% does not exist anymore. However, if I close the session and log back in again then it works fine.

For updating triggers and views, there isn't an easy way to do it without dropping and re-creating and object.