Details
-
Type:
Task
-
Status: Open
-
Priority:
Major
-
Resolution: Unresolved
-
Fix Version/s: 10.2
-
Component/s: None
-
Labels:None
Description
http://bugs.mysql.com/bug.php?id=73894
The question came up in the context of "DROP USER, how do I fix procedures afterwards?"
A naive expectation 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=...;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Note that this cannot be done via a stored procedure, because CREATE PROCEDURE and similar statements are not allowed in a procedure body. If stored procedures were more powerful, implementing this feature in SQL would be simple.