MySQL Bugs: #73894: Can't easily change DEFINER on existing views, stored routines, triggers, events

Bug #73894	Can't easily change DEFINER on existing views, stored routines, triggers, events
Submitted:	11 Sep 2014 13:58	Modified:	13 Sep 2014 21:00
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.20, all?	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The question came up in the context of "DROP USER, how do I fix procedures afterwards?"

A naive expection would be that one could simply say

  ALTER PROCEDURE name DEFINER=...;

but this isn't really supported on any of the four object types:

* ALTER EVENT can change the DEFINER, but only if at least one other 
  attribute is changed at the same time

  e.g. "ALTER DEFINER=foo@bar EVENT e1 COMMENT='';" works
  but just "ALTER DEFINER=foo@bar EVENT e1" doesn't

* ALTER VIEW allows to change the definer, but you have to
  repeat the "AS SELECT ..." part

* ALTER PROCEDURE / FUNCTION doesn't support changing the
  definer at all, but you can at least work around it by
  changing it in the mysql.proc table and restarting the
  server afterwards

* ALTER TRIGGER ... well there is no ALTER TRIGGER statement
  at all to begin with ...

How to repeat:
Try to change the definer of existing EVENTs, VIEWs, PROCEDUREs, FUNCTIONs and TRIGGERs

Suggested fix:
Provide ALTER variants for all object types that have a DEFINER to easily change the definer with just

  ALTER $OBJECTTYPE $name DEFINER=...;

PS: the manual page on DROP USER mentions stored routines and views
but not EVENTs and TRIGGERS:

  DROP USER does not automatically drop or invalidate databases or 
  objects within them that the old user created. This includes stored 
  programs or views for which the DEFINER attribute names the dropped user.

  <http://dev.mysql.com/doc/refman/5.6/en/drop-user.html>

IMHO it should not just say "this includes" and then just listing half
the affected object types, but say "this affects" and list all of them ...

Thank you for the bug report.

Posted by developer:
 
This is a valid feature request.

PHP workaround script to replace a certain definer user on all object types

https://github.com/hholzgra/change_definer

Bug #81994 marked as duplicate of this

Here is a bit dynamic sql that I used.  It worked for me for the quick thing I needed, but it is probably buggy.  I'm not sure if the comments need to be escaped, for example.

SET SESSION group_concat_max_len = 100000000000;
SELECT CONCAT('delimiter //\n' ,GROUP_CONCAT(q.`sql` SEPARATOR '\n')) AS `sql`
FROM (
	SELECT CONCAT('\nDROP PROCEDURE IF EXISTS',' `',r.ROUTINE_SCHEMA,'`.`',r.ROUTINE_NAME,'`\n//\n','CREATE DEFINER=`new_definer`@`%` ',r.ROUTINE_TYPE,' `',r.ROUTINE_SCHEMA,'`.`',r.ROUTINE_NAME,'`(
	   ',IFNULL(GROUP_CONCAT(DISTINCT '\t',p.PARAMETER_MODE,' `',p.PARAMETER_NAME,'` ',p.DATA_TYPE SEPARATOR ',\n\t'),''),'
	)
	LANGUAGE ',r.ROUTINE_BODY,'
	',IF(r.IS_DETERMINISTIC = 'NO','NOT ',''),'DETERMINISTIC
	',r.SQL_DATA_ACCESS,'
	SQL SECURITY ',r.SECURITY_TYPE,'
	COMMENT ''',r.ROUTINE_COMMENT,'''
	',r.ROUTINE_DEFINITION,'
//') AS `sql`
	FROM `information_schema`.`ROUTINES` r 
	LEFT JOIN information_schema.PARAMETERS p ON p.SPECIFIC_NAME = r.SPECIFIC_NAME AND p.SPECIFIC_SCHEMA = r.ROUTINE_SCHEMA
	WHERE r.`DEFINER` IN ('old_definer@%')
	GROUP BY r.SPECIFIC_NAME
) q;