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