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

[11 Sep 2014 13:58] Hartmut Holzgraefe
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=...;
[11 Sep 2014 14:03] Hartmut Holzgraefe
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 ...
[11 Sep 2014 18:10] Miguel Solorzano
Thank you for the bug report.
[12 Sep 2014 6:53] Ståle Deraas
Posted by developer:
 
This is a valid feature request.
[13 Sep 2014 20:54] Hartmut Holzgraefe
PHP workaround script to replace a certain definer user on all object types
[13 Sep 2014 21:00] Hartmut Holzgraefe
https://github.com/hholzgra/change_definer
[24 Jun 2016 9:34] Umesh Shastry
Bug #81994 marked as duplicate of this
[30 May 2017 18:15] Alden Weddleton
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;