Description:
The behavior of this statement seems to have changed a bit recently. It appears that the main body of procedures, and the characteristics aren't returned to any user other than the creator/definer. In build 5.0.22 the Create Procedure column returns nothing at all unless the statement is called by the definer. In 5.0.25 this column returns NULL.
It is not apparent from the manual what, if anything should be returned to users who aren't the definer, but possess CREATE PROCEDURE, or ALTER PROCEDURE grants. It seems like that information would sensibly be provided to a user possessing ALTER PROCEDURE.
How to repeat:
CREATE DATABASE fooDB;
connect (root, localhost, root,,);
GRANT CREATE ROUTINE ON fooDB.* TO 'create'@'localhost'
IDENTIFIED BY 'create' WITH GRANT OPTION;
GRANT CREATE ROUTINE ON fooDB.* TO 'create2'@'localhost'
IDENTIFIED BY 'create2' WITH GRANT OPTION;
GRANT ALTER ROUTINE, SELECT ON fooDB.* TO 'alter'@'localhost'
IDENTIFIED BY 'alter' WITH GRANT OPTION;
connect (alter, localhost, alter, alter, fooDB);
connect (create2, localhost, create2, create2, fooDB);
connect (create, localhost, create, create, fooDB);
--echo connected as 'create'
DELIMITER //;
CREATE PROCEDURE fooDB.alter_proc() SQL SECURITY DEFINER
BEGIN
SELECT "Inside alter_proc()";
END//
DELIMITER ;//
SHOW CREATE PROCEDURE fooDB.alter_proc;
--echo connected as 'alter'
connection alter;
SHOW CREATE PROCEDURE fooDB.alter_proc;
--echo conneced as 'create2'
connection create2;
SHOW CREATE PROCEDURE fooDB.alter_proc;
connection root;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create2'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter'@'localhost';
DROP USER 'create'@'localhost',
'create2'@'localhost',
'alter'@'localhost';
DROP PROCEDURE fooDB.alter_proc;
DROP DATABASE fooDB;
##############
OUTPUT FROM 5.0.22
# AS THE DEFINER
SHOW CREATE PROCEDURE fooDB.alter_proc;
Procedure sql_mode Create Procedure
alter_proc CREATE DEFINER=`create`@`localhost` PROCEDURE `alter_proc`()
BEGIN
SELECT "Inside alter_proc()";
END
# AS ANOTHER USER WITH CREATE PROCEDURE OR ALTER PROCEDURE 5.0.22
SHOW CREATE PROCEDURE fooDB.alter_proc;
Procedure sql_mode Create Procedure
alter_proc
# AS ANOTHER USER WITH CREATE PROCEDURE OR ALTER PROCEDURE 5.0.25-log
SHOW CREATE PROCEDURE fooDB.alter_proc;
Procedure sql_mode Create Procedure
alter_proc NULL
Suggested fix:
If the rule is that ONLY the definer can see this information than this needs to be documented in the manual section for the command SHOW CREATE PROCEDURE.
Description: The behavior of this statement seems to have changed a bit recently. It appears that the main body of procedures, and the characteristics aren't returned to any user other than the creator/definer. In build 5.0.22 the Create Procedure column returns nothing at all unless the statement is called by the definer. In 5.0.25 this column returns NULL. It is not apparent from the manual what, if anything should be returned to users who aren't the definer, but possess CREATE PROCEDURE, or ALTER PROCEDURE grants. It seems like that information would sensibly be provided to a user possessing ALTER PROCEDURE. How to repeat: CREATE DATABASE fooDB; connect (root, localhost, root,,); GRANT CREATE ROUTINE ON fooDB.* TO 'create'@'localhost' IDENTIFIED BY 'create' WITH GRANT OPTION; GRANT CREATE ROUTINE ON fooDB.* TO 'create2'@'localhost' IDENTIFIED BY 'create2' WITH GRANT OPTION; GRANT ALTER ROUTINE, SELECT ON fooDB.* TO 'alter'@'localhost' IDENTIFIED BY 'alter' WITH GRANT OPTION; connect (alter, localhost, alter, alter, fooDB); connect (create2, localhost, create2, create2, fooDB); connect (create, localhost, create, create, fooDB); --echo connected as 'create' DELIMITER //; CREATE PROCEDURE fooDB.alter_proc() SQL SECURITY DEFINER BEGIN SELECT "Inside alter_proc()"; END// DELIMITER ;// SHOW CREATE PROCEDURE fooDB.alter_proc; --echo connected as 'alter' connection alter; SHOW CREATE PROCEDURE fooDB.alter_proc; --echo conneced as 'create2' connection create2; SHOW CREATE PROCEDURE fooDB.alter_proc; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create'@'localhost'; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create2'@'localhost'; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter'@'localhost'; DROP USER 'create'@'localhost', 'create2'@'localhost', 'alter'@'localhost'; DROP PROCEDURE fooDB.alter_proc; DROP DATABASE fooDB; ############## OUTPUT FROM 5.0.22 # AS THE DEFINER SHOW CREATE PROCEDURE fooDB.alter_proc; Procedure sql_mode Create Procedure alter_proc CREATE DEFINER=`create`@`localhost` PROCEDURE `alter_proc`() BEGIN SELECT "Inside alter_proc()"; END # AS ANOTHER USER WITH CREATE PROCEDURE OR ALTER PROCEDURE 5.0.22 SHOW CREATE PROCEDURE fooDB.alter_proc; Procedure sql_mode Create Procedure alter_proc # AS ANOTHER USER WITH CREATE PROCEDURE OR ALTER PROCEDURE 5.0.25-log SHOW CREATE PROCEDURE fooDB.alter_proc; Procedure sql_mode Create Procedure alter_proc NULL Suggested fix: If the rule is that ONLY the definer can see this information than this needs to be documented in the manual section for the command SHOW CREATE PROCEDURE.