MySQL Bugs: #20763: dropping a DB doesn't remove grants relating to routines in that DB

Bug #20763	dropping a DB doesn't remove grants relating to routines in that DB
Submitted:	28 Jun 2006 21:03	Modified:	4 Jul 2006 9:26
Reporter:	Erica Moss	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.12-beta-log	OS:	Linux (Fedora core 5)
Assigned to:		CPU Architecture:	Any

Description:
When we drop a routine, all grants both implicitly, and explicitly made to that routine are revoked.  This might be inconvenient, but it is the safest thing to do.

However as the mysql-test-run script below demonstrates, we do no follow this precedent when a database that contains a routine is dropped.  Execute/Alter grants given to the definer remain, as do explicit grants made to Execute/Alter.  This will have effects that are also demonstrated in the script.  The script demonstrates procedures but in all likelihood this effects functions well.

How to repeat:
connect (root, localhost, root,,);
CREATE DATABASE procDB;
GRANT CREATE ROUTINE ON procDB.* TO 'create'@'localhost'
            IDENTIFIED BY 'create';
GRANT EXECUTE ON PROCEDURE procDB.p1 TO 'execute'@'localhost'
            IDENTIFIED BY 'execute';
GRANT ALTER ROUTINE ON PROCEDURE procDB.p1 TO 'alter'@'localhost'
            IDENTIFIED BY 'alter';
connect (create, localhost, create, create, procDB);
CREATE PROCEDURE procDB.p1() SELECT VERSION();
SHOW GRANTS;
DROP PROCEDURE procDB.p1;

connection root;
SHOW GRANTS FOR 'create'@'localhost';
SHOW GRANTS FOR 'execute'@'localhost';
SHOW GRANTS FOR 'alter'@'localhost';
--echo ***this is  correct***
--echo ***but try it again this way***
--echo have to make grants again;
GRANT EXECUTE ON PROCEDURE procDB.p1 TO 'execute'@'localhost';
GRANT ALTER ROUTINE ON PROCEDURE procDB.p1 TO 'alter'@'localhost';

connect (root, localhost, root,,);
CREATE DATABASE procDB;
GRANT CREATE ROUTINE ON procDB.* TO 'create'@'localhost'
            IDENTIFIED BY 'create';
GRANT EXECUTE ON PROCEDURE procDB.p1 TO 'execute'@'localhost'
            IDENTIFIED BY 'execute';
GRANT ALTER ROUTINE ON PROCEDURE procDB.p1 TO 'alter'@'localhost'
            IDENTIFIED BY 'alter';
connect (create, localhost, create, create, procDB);
CREATE PROCEDURE procDB.p1() SELECT VERSION();
SHOW GRANTS;
DROP PROCEDURE procDB.p1;

connection root;
SHOW GRANTS FOR 'create'@'localhost';
SHOW GRANTS FOR 'execute'@'localhost';
SHOW GRANTS FOR 'alter'@'localhost';
--echo ***this is  correct***
--echo ***but try it again this way***
--echo have to make grants again;
GRANT EXECUTE ON PROCEDURE procDB.p1 TO 'execute'@'localhost';
GRANT ALTER ROUTINE ON PROCEDURE procDB.p1 TO 'alter'@'localhost';

--echo cleanup as root;
connection root;
disconnect create;
disconnect new_create;
disconnect execute;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'new_create'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'execute'@'localhost';
DROP USER 'create'@'localhost', 'new_create'@'localhost', 'execute'@'localhost';
DROP DATABASE procDB;
disconnect root;

Suggested fix:
revoke all grants to objects contained in dropped databases, either explicit, or implicit.

No other object in MySQL has associated grants removed when it is dropped.  Why do SP do this but not others?  I don't care what the answer is, but it should be consistent either way it is decided to go.

I just noticed that I inadvertently pasted in the working example twice.  This is what I intended to paste as the second example:

connect (root, localhost, root,,);
CREATE DATABASE procDB;
GRANT CREATE ROUTINE ON procDB.* TO 'create'@'localhost'
            IDENTIFIED BY 'create';
GRANT EXECUTE ON PROCEDURE procDB.p1 TO 'execute'@'localhost'
            IDENTIFIED BY 'execute';
GRANT ALTER ROUTINE ON PROCEDURE procDB.p1 TO 'alter'@'localhost'
            IDENTIFIED BY 'alter';
connect (create, localhost, create, create, procDB);
CREATE PROCEDURE procDB.p1() SELECT VERSION();

connection root;
DROP DATABASE procDB;
SHOW GRANTS FOR 'create'@'localhost';
SHOW GRANTS FOR 'execute'@'localhost';
SHOW GRANTS FOR 'alter'@'localhost';

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'execute'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter'@'localhost';
DROP USER 'create'@'localhost', 'execute'@'localhost', 'alter'@'localhost';

Sorry, but this is not a bug. This behaviour is intended and documented. Please, read the manual (http://dev.mysql.com/doc/refman/5.1/en/grant.html:

"Important: MySQL does not automatically revoke any privileges when you drop a table or database. However, if you drop a routine, any routine-level privileges granted for that routine are revoked."