MySQL Bugs: #16424: Events: event remains if user is dropped

Bug #16424	Events: event remains if user is dropped
Submitted:	12 Jan 2006 4:17	Modified:	17 Sep 2009 19:05
Reporter:	Peter Gulutzan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.6-alpha-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Andrey Hristov	CPU Architecture:	Any

Description:
The event body always executes with DEFINER privileges,
or at least it should. That is, if the user who created
the event has INSERT privilege on table t, then the
event can contain an "INSERT INTO t" statement which
will work. But what if we drop that user? Then the
privilege ceases to exist, and the event shuld be
cancelled -- no privileges exist. Make sure nothing
happens for RENAME.

How to repeat:

As user root, say:
mysql> create table t_32 (s1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> grant event on * to y@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant insert on t_32 to y@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> set global event_scheduler = 1;
Query OK, 0 rows affected (0.00 sec)

As user y@localhost, say:
mysql> create event e_32 on schedule every 1 second do insert into db1.t_32 values (0);
Query OK, 1 row affected (0.01 sec)

As user root, say:
mysql> drop user y@localhost;
Query OK, 0 rows affected (0.01 sec)

Notice that the event continues to be executed.

Now, it's being executed but the following is happening :
060221  4:34:01 [ERROR] SCHEDULER: [y@localhost][test.e_32] There is no 'y'@'localhost' registered]
060221  4:34:01 [ERROR] SCHEDULER: [y@localhost][test.e_32] Access denied for user '(null)'@'' to database 'test']
060221  4:34:01 [Note] SCHEDULER: Executed event test.e_32 of y@localhost  [EXPR:1]. RetCode=-99

The end result is that nothing is being inserted but the event is executed :( . Will be fixed. Relies on the fix of bug#16992, which introduces second index on mysql.event

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/7219

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/7360

Andrey, Events behaviour should be consistent with the rest of the server.
Currently we do not delete SUID stored procedures when a user is dropped, see an example below:

kostja@bodhi:~> mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23-valgrind-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create user u1 identified by '';
Query OK, 0 rows affected (0.01 sec)

mysql> create database u1_db;
Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on u1_db.* to u1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
kostja@bodhi:~> mysql -uu1 u1_db;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.23-valgrind-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create procedure p1() sql security definer begin end;
Query OK, 0 rows affected (0.02 sec)

mysql> call p1();
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
kostja@bodhi:~> mysql5 -uroot
zsh: command not found: mysql5
kostja@bodhi:~> mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.23-valgrind-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop user u1;
Query OK, 0 rows affected (0.00 sec)

mysql> call u1_db.p1();
Query OK, 0 rows affected (0.00 sec)

mysql> select routine_name from information_schema.routines where definer='u1@localhost';
+--------------+
| routine_name |
   +--------------+
| p1           |
+--------------+
1 row in set (0.00 sec)

Conclusion: to be consistent with the rest of  MySQL functionality, you should not drop dependent objects when an object is dropped. Instead, you should check privileges during execution, and if a user does not exist, return an error.

In accordance with the arguments above, closing the bug as "Won't fix" (we already perform privilege checks on every execution of event).