MySQL Bugs: #46220: User cannot fully SHOW CREATE PROCEDURE

Bug #46220	User cannot fully SHOW CREATE PROCEDURE
Submitted:	16 Jul 2009 13:38	Modified:	16 Jul 2009 13:51
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.35, 5.1.36, 5.0.83	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A user with ALL privileges to a ROUTINE and/or a database cannot fetch the SHOW CREATE PROCEDURE statement (no matter if SQL SECURITY is DEFINER or INVOKER - don't know if this is relevant).

How to repeat:
-- as root

SHOW GRANTS FOR peter@localhost;
/*
Grants for peter@localhost                             
-------------------------------------------------------
GRANT USAGE ON *.* TO 'peter'@'localhost'              
GRANT ALL PRIVILEGES ON `test`.* TO 'peter'@'localhost'
*/

SHOW CREATE PROCEDURE test.blyh;

/* returns create statement in the 'Create Procedure' column of result
CREATE DEFINER=`root`@`localhost` PROCEDURE `test´.`blyh`() SQL SECURITY INVOKER
BEGIN
 select 9;
END
*/

-- as peter

/* There is no 'Create Procedure' column of result

Procedure  sql_mode  Create Procedure  character_set_client  collation_connection  Database Collation
---------  --------  ----------------  --------------------  --------------------  ------------------
blyh                 (NULL)            utf8                  utf8_general_ci       utf8_general_ci   
*/

I am only able to get the 'Create Procedure' column `returned for users with
GRANT ALL on *.*

Suggested fix:
Isn't this a bug? If user has ALL grants to the ROUTINE and/or the database, why reject him the SHOW CREATE statment (at least with SQL SECURITY INVOKER).

I think our manual clearly explains what privileges are required. Please, read http://dev.mysql.com/doc/refman/5.1/en/show-create-procedure.html:

"Both statements require that you be the owner of the routine or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL."

This is how it works by design, so - not a bug. Please, check.

oops .. 

http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html

"Both statements require that you be the owner of the routine or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL."

.. so not a bug (if documentation is specification). I still do not like this!

who came first? :-)

This is a bug as the solution is a security hole.  The only solution requires granting access to ALL procedures (in mysql.proc), even those that are associated to a database that the user does not have access to.  

 It should be made possible to give users access to procedures only in specific databases.