Bug #34676 Stored procedures should never be orphanable via DROP USER
Submitted: 19 Feb 2008 20:35 Modified: 19 Mar 2008 21:49
Reporter: Chad MILLER Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: drop user, stored procedures

[19 Feb 2008 20:35] Chad MILLER
Description:
(Related: Bug#33464, Bug#12252, Bug#17573)

When we drop a user, we should also drop all routines defined by that user. Reasons:

- This is the behavior that is most like all other DBMSes (though our user/schema model doesn't map directly to the other DBMSes).

- Storing the SECURITY CONTEXT DEFINER value as a string and looking up the user based on that string at routine-execution-time is a big gaping security problem.   Heirs of a username shouldn't automatically and silently be heirs of executing code.

How to repeat:
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
flush privileges;

connect (connbug, localhost, user_1, , db_storedproc_);
--source suite/funcs_1/include/show_connection.inc

DROP PROCEDURE IF EXISTS sp3;
DROP FUNCTION IF EXISTS fn1;

CREATE PROCEDURE sp3(v1 char(20))
  BEGIN
    SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
  END
//
delimiter //;

CREATE FUNCTION fn1() returns char(50) SQL SECURITY DEFINER
  BEGIN
     return 2;
  END
//
delimiter ;//

USE db_storedproc_1;

drop user 'user_1'@'localhost';

SELECT fn1(), sp3('asdf');
--replace_column 5 date 6 date
SHOW FUNCTION STATUS WHERE DEFINER='user_1@localhost';

grant create routine on db_storedproc_1.* to 'user_1'@'localhost'; #different user
flush privileges;

SELECT fn1(), sp3('asdf');
--replace_column 5 date 6 date
SHOW FUNCTION STATUS WHERE DEFINER='user_1@localhost';

DROP PROCEDURE sp3;
DROP FUNCTION fn1;

Suggested fix:
> DROP USER 'foo';
Warning ----: User 'foo' owns stored procedures 'fib'.
Warning ----: User 'foo' owns function 'towers'.
Error ER_CANNOT_USER: User 'foo' cannot be dropped.
> DROP USER 'foo' CASCADE;
Ok.
[19 Feb 2008 21:49] Sveta Smirnova
Thank you for the report.

Could you please provide working test case? SELECT fn1(), sp3('asdf'); fails with syntax error and I can not guess which queries should I replace it with.
[20 Mar 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".