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.
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.