Description:
I don't really know whether it's a bug of mysql or there's some kind of documentation
ambiguity.
http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-privileges.html
[cite]
The EXECUTE privilege is required to execute stored routines. However, this privilege is
granted automatically to the creator of a routine if necessary (and dropped when the
creator drops the routine). Also, the default SQL SECURITY characteristic for a routine
is DEFINER, which enables users who have access to the database with which the routine is
associated to execute the routine.
If the automatic_sp_privileges system variable is 0, the EXECUTE and ALTER ROUTINE
privileges are not automatically granted and dropped.
.................................
* `automatic_sp_privileges'
When this variable has a value of 1 (the default), the server
automatically grants the `EXECUTE' and `ALTER ROUTINE' privileges
to the creator of a stored routine, if the user cannot already
execute and alter or drop the routine. (The `ALTER ROUTINE'
privileges is required to drop the routine.) The server also
automatically drops those privileges when the creator drops the
routine. If `automatic_sp_privileges' is 0, the server does not
automatically add and drop these privileges. This variable was
added in MySQL 5.0.3.
[/cite]
according to documentation the behaviour of 'automatic_sp_privileges'/CREATE PROCEDURE
definitely restricted to privileges of _DEFINER_ but not any other privileges. So this
dump looks a bit strange for me... Whether a 'DROP PROCEDURE' operation should affect
grants of other users? Whether the 'automatic_sp_privileges' should affect behaviour of
'DROP PROCEDURE' in terms of other users?
How to repeat:
mysql> \u test
Database changed
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM `test`@`localhost`;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE "automatic_sp_privileges";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| automatic_sp_privileges | ON |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM `test`@`localhost`;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP PROCEDURE IF EXISTS test.simpleproc;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1
FROM t;
-> END; //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> SHOW CREATE PROCEDURE test.simpleproc\G
*************************** 1. row ***************************
Procedure: simpleproc
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(OUT param1
INT)
BEGIN SELECT COUNT(*) INTO param1 FROM t; END
1 row in set (0.00 sec)
mysql> GRANT EXECUTE ON PROCEDURE test.simpleproc TO 'test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR 'test'@'localhost';
+----------------------------------------------------------------------+
| Grants for test@localhost |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT EXECUTE ON PROCEDURE `test`.`simpleproc` TO 'test'@'localhost' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> DROP PROCEDURE IF EXISTS test.simpleproc;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL automatic_sp_privileges=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1
FROM t;
-> END; //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> GRANT EXECUTE ON PROCEDURE test.simpleproc TO 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'test'@'localhost';
+----------------------------------------------------------------------+
| Grants for test@localhost |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT EXECUTE ON PROCEDURE `test`.`simpleproc` TO 'test'@'localhost' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> DROP PROCEDURE IF EXISTS test.simpleproc;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'test'@'localhost';
+----------------------------------------------------------------------+
| Grants for test@localhost |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT EXECUTE ON PROCEDURE `test`.`simpleproc` TO 'test'@'localhost' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)
