Bug #20235 Unable to SHOW CREATE PROCEDURE without SELECT grant
Submitted: 2 Jun 2006 18:03 Modified: 30 Jan 2008 16:35
Reporter: Erica Moss Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.0.21-community-nt OS:Microsoft Windows (win32 - XP SP2, Fedora core 5)
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[2 Jun 2006 18:03] Erica Moss
Description:
As the below mysql-test-run script demonstrates, unless a user is granted SELECT to the mysql.proc table that user is unable to get a complete return from the command SHOW CREATE 'procedure name'

This would seem to be rather inconvenient for someone who has been granted the ALTER ROUTINE priv.  Surely this user would want to examine the routine before and after modifying it.

How to repeat:
# CODE
connect (root, localhost, root,,);
DROP DATABASE IF EXISTS privDB;
CREATE DATABASE privDB;
use privDB;
CREATE TABLE t1 (c1 INT);
DELIMITER //;
CREATE PROCEDURE privDB.proc()
BEGIN
   SELECT * FROM t1;
END//
DELIMITER ;//

SHOW CREATE PROCEDURE privDB.proc;

GRANT ALTER ROUTINE ON privDB.* TO 'alter_routine'@'localhost'
         IDENTIFIED BY 'alter_routine';
GRANT ALTER ROUTINE, SELECT ON privDB.* TO 'alter_sel_db'@'localhost'
         IDENTIFIED BY 'alter_sel_db';
GRANT ALTER ROUTINE ON privDB.* TO 'alter_sel_proc'@'localhost'
         IDENTIFIED BY 'alter_sel_proc';
GRANT SELECT ON mysql.proc TO 'alter_sel_proc'@'localhost';

--echo connecting as user 'alter_routine';
connect (alter_routine, localhost, alter_routine, alter_routine,);
--echo connected;
SHOW CREATE PROCEDURE privDB.proc;

--echo connecting as user 'alter_sel_db';
connect (alter_sel_db, localhost, alter_sel_db, alter_sel_db,);
--echo connected;
SHOW CREATE PROCEDURE privDB.proc;
ALTER PROCEDURE privDB.proc SQL SECURITY INVOKER;
SHOW CREATE PROCEDURE privDB.proc;

--echo connecting as user 'alter_sel_proc';
connect (alter_sel_proc, localhost, alter_sel_proc, alter_sel_proc,);
--echo connected;
SHOW CREATE PROCEDURE privDB.proc;

#clean-up
connection root;
DROP PROCEDURE privDB.proc;
DROP DATABASE privDB;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter_routine'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter_sel_db'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter_sel_proc'@'localhost';
DROP USER 'alter_routine'@'localhost',
          'alter_sel_db'@'localhost',
          'alter_sel_proc'@'localhost';

# OUTPUT
## As root:
SHOW CREATE PROCEDURE privDB.proc;
Procedure       sql_mode        Create Procedure
proc            CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`()

## As 'alter_routine'
SHOW CREATE PROCEDURE privDB.proc;
Procedure       sql_mode        Create Procedure
proc

## AS 'alter_sel_db'
SHOW CREATE PROCEDURE privDB.proc;
Procedure       sql_mode        Create Procedure
proc

## AS 'alter_sel_proc'
SHOW CREATE PROCEDURE privDB.proc;
Procedure       sql_mode        Create Procedure
proc            CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`()
    SQL SECURITY INVOKER

Suggested fix:
There are a number of instances in our priv system where a large granted priv can not be used unless a smaller, but prerequisite priv has also been granted.  Rather than try to implicitly give these smaller grants to users who have been granted the larger privileges, is it possible to associate these smaller grants with the commands themselves?  

For example, when someone is granted ALTER ROUTINE, don't automatically hand them SELECT ON mysql.proc.  Instead give the commands SHOW CREATE PROCEDURE, and SHOW CREATE FUNCTION, the SELECT grant for that table.  That way any user that had the right to run the command would get SELECT, but only while running that command.  The user wouldn't also be able to issue a statement like SELECT * FROM mysql.proc
[9 Jan 2007 16:38] Peter Andrews
I am using MySql 5.1.14-beta and Connector/J 5.04 and the bug appears to be slightly different. You must have the select grant specifically for the 'mysql' table.

I am using dynamic sql in a stored procedure to do my grants (hence my need for 5.1 beta). 

I grant 'execute' permission at the table level. If I add select at the table level, my java jdbc call to create a callablestatement (connection.prepareCall()) fails with a null pointer exception (and no stack trace). If I grant SELECT for the mysql table, the call succeeds.

Note: strangeness -- when the select for 'mysql' is granted the connector/j friver sends 'SHOW CREATE PROCEDURE'. This succeeds even though I did not grant the user CREATE or ALTER ROUTINE permissions.
[9 Jan 2007 16:52] Peter Andrews
MySql Bug #20235 may be related to this bug.
[9 Jan 2007 16:53] Peter Andrews
Dang! I meant to say MySql Bug #20465 may be related to this bug.
[9 Jan 2007 16:56] Peter Andrews
How embarasssing: The bug I meant to reference (really!) is: MySql Bug #24065  	'Connect/J prepareCall gives null pointer exception'