Bug #20241 Access to procedures/functions is lost if the definer is dropped
Submitted: 3 Jun 2006 5:08 Modified: 5 Dec 2007 18:55
Reporter: Erica Moss Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:WIN -5.0.21 LIN 5.0.22 OS:Any (MS win32 - XP SP2, Fedora core 5)
Assigned to: CPU Architecture:Any

[3 Jun 2006 5:08] Erica Moss
Description:
In the event that a definer of a routine has been dropped from the system it is not immediately apparent how that routine can ever be used again.  As you can see from the below code, I made a number of attempts to try to restore the procedure to a functional state.

I found a few links from people asking how it is possible to change the definer of a routine after its definition, but found no answers.  It's not possible to do via ALTER ROUTINE, and it doesn't appear possible to manually update tables in the information_schema.

How to repeat:
connect (root, localhost, root,,);
DROP DATABASE IF EXISTS privDB;
CREATE DATABASE privDB;
GRANT CREATE ROUTINE ON privDB.* TO 'user'@'localhost'
           IDENTIFIED BY 'user';

connect (user, localhost, user, user,);
CREATE PROCEDURE privDB.foo()
   SELECT VERSION();

connection root;
#### Fire user@localhost ###
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost';
DROP USER 'user'@'localhost';

#  query 'CALL privDB.foo()' failed: 1449: There is no 'user'@'localhost' registered
--error 1449
CALL privDB.foo();

# Try to fix the problem manually
UPDATE mysql.proc
SET definer='root@localhost'
WHERE definer='user@localhost';
SELECT * FROM mysql.proc WHERE name='foo';

# Doesn't work
#  query 'CALL privDB.foo()' failed: 1449: There is no 'user'@'localhost' registered
--error 1449
CALL privDB.foo();

# failed: Access denied for user 'root'@'localhost' to database 'information_schema'
--error 1044
UPDATE information_schema.routines
SET definer='root@localhost'
WHERE definer='user@localhost';

# one last try
GRANT CREATE ROUTINE ON privDB.* TO 'user'@'localhost'
        IDENTIFIED BY 'user';

# execute command denied to user 'user'@'localhost' for routine 'privDB.foo'
--error 1370
CALL privDB.foo();

CALL privDB.foo();
DROP PROCEDURE privDB.foo;
DROP DATABASE privDB;

Suggested fix:
It seems like we need to add DEFINER to the list of modifiable characteristics for ALTER PROC/FUNC.  In keeping with the current design, perhaps that characteristic modification ability could be limited to users with SUPER grant.
[27 Aug 2006 21:02] Sergei Golubchik
One can drop an offending routine, or one can change the definer by modifying mysql.proc directly - just as you did. Note that every connection has a local cache of compiled stored routines, you need to reconnect to see the effect of the UPDATE.

implementing ALTER ROUTINE ... DEFINER ... is possible, but it's definitely a new feature, not a bugfix.