Bug #31501 Stored Routines: droping stored procedure revokes all assotiated privileges
Submitted: 10 Oct 2007 10:13 Modified: 26 Apr 2013 14:59
Reporter: Alexander Y. Fomichev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: privileges, PROCEDURE

[10 Oct 2007 10:13] Alexander Y. Fomichev
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)
[10 Oct 2007 10:22] Alexander Y. Fomichev
oops, sorry.

mysql> SHOW VARIABLES LIKE "version";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.0.45 |
+---------------+--------+
1 row in set (0.00 sec)
[10 Oct 2007 20:50] Sveta Smirnova
Thank you for the report.

Verified as described.
[12 Jun 2008 22:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47825
[21 Nov 2008 9:27] Sveta Smirnova
Bug #40777 was marked as duplicate of this one.
[10 Nov 2009 18:32] Leandro Morgado
I tested this on 5.0 and 5.1 and it still removes all privileges, not just the "creator" privileges.

The patch either needs to be backported to 5.0 and 5.1 or the Docs need to define "creator" properly. 

I actually like the current behaviour better but would make automatic_sp_priveleges OFF by *default*.

Versions tested:
5.1.37sp1-enterprise-gpl-pro-log MySQL Enterprise Server - Pro Edition (GPL)
5.0.83-enterprise-gpl MySQL Enterprise Server (GPL)
[26 Apr 2013 14:59] MySQL Verification Team
I would vote that DROP of the routine leads to the loss of EXECUTE privileges on all existing users who have it, regardless of SQL SECURITY setting. Problem that arises here is what happens when you DROP and then CREATE a procedure with the same name. In that case, I think that we should document and urge our customers / users to use ALTER instead.

I do not think that this should be done in GA versions as it represents a change in behavior.
[9 Sep 2015 13:53] Ridwan Siddiqui
Same issue is faced on MySQL 5.6.19, please let us know if there is any solution of this?