Bug #13198 SP executes if definer does not exist
Submitted: 14 Sep 2005 19:31 Modified: 10 Mar 2006 18:09
Reporter: Alexander Nozdrin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Alexander Nozdrin CPU Architecture:Any

[14 Sep 2005 19:31] Alexander Nozdrin 
Description:
A stored procedure is executed under the authorization of the creator (definer) of the stored procedure. If invoker doesn't exist at the moment of invocation of a procedure, the procedure should not be executed.

The problem is that MySQL executes a stored procedure with non-existent definer under
the authorization of the invoker.

How to repeat:
Let's assume, we have a new (clean) data-dir.

--
-- Connect as root, i.e.:
--   $prefix/client/mysql -u root mysql
--

mysql> CREATE DATABASE db1;
mysql> CREATE USER u1;
mysql> CREATE USER u2;

mysql> DELETE FROM user WHERE User = '';
-- This is required to remove anonymous users and
-- to allow to connect w/o specifying hostname.

mysql> GRANT ALL ON db1.* TO u1;
mysql> GRANT ALL ON db1.* TO u2;

--
-- Restart server to use new privileges;
--
-- Connect as u1, i.e.:
--   $prefix/client/mysql -u u1 db1
--

mysql> delimiter //                
mysql> CREATE PROCEDURE p1()
    -> BEGIN
    ->   SELECT 1;
    -> END//

-- Let's test p1():

mysql> CALL p1();
+---+
| 1 |
+---+
| 1 |
+---+

--
-- Connect as u2 and check that we are able to call p1(), i.e.:
--   $prefix/client/mysql -u u2 db1
--

mysql> CALL p1();
+---+
| 1 |
+---+
| 1 |
+---+

--
-- Connect as root, i.e.:
--   $prefix/client/mysql -u root mysql
--

mysql> DROP USER u1;

--
-- Restart server to use new privileges;
--
-- Connect as u2 and check that we still can execute p1(), i.e.:
--   $prefix/client/mysql -u u2 db1
--

mysql> CALL p1();
+---+
| 1 |
+---+
| 1 |
+---+
[14 Sep 2005 20:08] Jorge del Conde 
I was able to reproduce this using a recent bk clone
[19 Sep 2005 21:57] 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/internals/30068
[10 Oct 2005 11:15] 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/internals/30856
[30 Nov 2005 14:13] Konstantin Osipov 
Valeriy,
in 5.0 we cannot implement CASCADE/RESTRICT semantics of DROP statements.
The approach we took is to make sure that an invalid object at least can not be used.
[20 Feb 2006 11:52] Alexander Nozdrin 
BUG#17573 has been marked as a duplicate of this bug.
[7 Mar 2006 19:37] Alexander Nozdrin 
Pushed into 5.0, currently tagged 5.0.20.
[9 Mar 2006 21:03] Alexander Nozdrin 
Pushed into 5.1 tree, currently tagged 5.1.8-beta.
[10 Mar 2006 18:09] Paul DuBois 
Noted in 5.0.20, 5.1.8 changelogs.

The server would execute stored routines that had a 
non-existent definer. (Bug #13198)