Bug #16992 Events: information_schema troubles
Submitted: 1 Feb 2006 3:07 Modified: 28 Jun 2006 6:56
Reporter: Peter Gulutzan Email Updates:
Status: Closed 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

[1 Feb 2006 3:07] Peter Gulutzan
Description:
I can see information_schema.events, thanks to the fix for bug#16642.
These troubles remain:

The event_body column always begins with a space.

The information_schema.events.event_body column isn't
equivalent to information_schema.routines.routine_body
(which has 'SQL'). Instead it has the event body.

The privilege needed for seeing a row in information_schema.events
is: I must be the definer. This is not consistent with SHOW EVENTS
(which also requires that I have the event privilege), and it's
too restrictive -- what if the user gets dropped?

How to repeat:
I have a database named 'db1' which contains no events.

As user root, say:

use db1;
create event e on schedule every 1 day do set @a=5;
select event_name, event_body from information_schema.events where event_schema='db1' and event_body like ' %';
The result will include event 'e', which shows that there's a ' ' at the start of event_body.
Notice also that the event_body column is not 'SQL'.

As user root, say:
grant event on *.* to newuser@localhost;

As newuser@localhost, start mysql, and say:
use db1;
create event e2 on schedule every 1 day do set @a=5;
select event_name from information_schema.events where event_schema='db1';
show events in db1;
The result will be 'e2', which shows that a user can see what the user created.

As user root, say:
select event_name from information_schema.events;
The result will include 'e' but not 'e2', which shows that a user can't see what
any other user created -- even though root has privileges on everything.

As user root, say:
revoke event on *.* from newuser@localhost;

As newuser@localhost (after quitting and restarting), say:
/* no 'use db1' here */
select event_name from information_schema.events where event_schema='db1';
show events in db1;
The SELECT will succeed, the SHOW will fail, which shows
that SELECT and SHOW aren't doing the same privilege checking.

Suggested fix:
A few days ago, I wrote to Andrey:
"I think -- very uncertainly, I haven't thought much --
that only the event's DEFINER should be able to see
a row in INFORMATION_SCHEMA.EVENTS."
But now that I see how it works, I see that's no good.

WL#1034 said that it should be possible to grant a usage/monitor
privilege "on" an event. I advocate: implement the WL#1034 requirement.
[1 Feb 2006 10:37] Valeriy Kravchuk
Thank you for a problem report. Yes, the behaviour is strange and inconsistent. Although, I've got slightly different results on ChangeSet@1.2099, 2006-01-31 01:37:48+01:00:

openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.6-alpha

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

mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> use db1;
Database changed
mysql> create event e on schedule every 1 day do set @a=5;
Query OK, 1 row affected (0.05 sec)

mysql> select event_name, event_body from information_schema.events where
    -> event_schema='db1' and event_body like ' %';
+------------+------------+
| event_name | event_body |
+------------+------------+
| e          |  set @a=5  |
+------------+------------+
1 row in set (0.01 sec)

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

mysql> exit
Bye
openxs@suse:~/dbs/5.1> bin/mysql -unewuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.6-alpha

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

mysql> use db1;
ERROR 1044 (42000): Access denied for user 'newuser'@'localhost' to database 'db1'
mysql> exit
Bye
openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.6-alpha

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

mysql> grant select on db1.* to newuser@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.1> bin/mysql -unewuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.6-alpha

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

mysql> use db1;
Database changed
mysql> create event e2 on schedule every 1 day do set @a=5;
ERROR 1044 (42000): Access denied for user 'newuser'@'localhost' to database 'db
1'
mysql> select event_name from information_schema.events where event_schema='db1';
+------------+
| event_name |
+------------+
| e          |
+------------+
1 row in set (0.00 sec)

mysql> show events in db1;
ERROR 1044 (42000): Access denied for user 'newuser'@'localhost' to database 'db1'

So, some inconsistencies are really present.
[1 Feb 2006 11:53] Andrey Hristov
Valeriy just told me that he did not uprgade his privilege tables. He will retest with a clean checkout. Though the question is open about installation with old priv tables....Scenario which I haven't tested.
[1 Feb 2006 12:42] Valeriy Kravchuk
On clean installation (ChangeSet@1.2119.1.1, 2006-02-01 13:28:45+03:00) I've got exactly the same results as Peter:

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

mysql> create event e on schedule every 1 day do set @a=5;
Query OK, 1 row affected (0.01 sec)

nmysql> select event_name, event_body from information_schema.events wh
ere
    -> event_schema='db1' and event_body like ' %';
Empty set (0.01 sec)

mysql> use db1;
Database changed
mysql> create event e on schedule every 1 day do set @a=5;
Query OK, 1 row affected (0.01 sec)

mysql> select event_name, event_body from information_schema.events where event_schema='db1' and event_body like ' %';
+------------+------------+
| event_name | event_body |
+------------+------------+
| e          |  set @a=5  |
+------------+------------+
1 row in set (0.01 sec)

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

mysql> exit
Bye
openxs@suse:~/dbs/5.1> bin/mysql -unewuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.6-alpha

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

mysql> use db1;
Database changed
mysql> create event e2 on schedule every 1 day do set @a=5;
Query OK, 1 row affected (0.00 sec)

mysql> select event_name from information_schema.events where event_schema='db1';
+------------+
| event_name |
+------------+
| e2         |
+------------+
1 row in set (0.00 sec)

mysql> show events in db1;
+-----+------+-------------------+-----------+------------+----------------+----
------------+---------------------+---------------------+---------+
| Db  | Name | Definer           | Type      | Execute at | Interval value | Int
erval field | Starts              | Ends                | Status  |
+-----+------+-------------------+-----------+------------+----------------+----
------------+---------------------+---------------------+---------+
| db1 | e2   | newuser@localhost | RECURRING | NULL       |              1 | INT
ERVAL_DAY   | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | ENABLED |
+-----+------+-------------------+-----------+------------+----------------+----
------------+---------------------+---------------------+---------+
1 row in set (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.6-alpha

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

mysql> revoke event on *.* from newuser@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.1> bin/mysql -unewuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.6-alpha

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

mysql> select event_name from information_schema.events where event_schema='db1';
+------------+
| event_name |
+------------+
| e2         |
+------------+
1 row in set (0.01 sec)

mysql> show events in db1;
ERROR 1044 (42000): Access denied for user 'newuser'@'localhost' to database 'db1'
[16 Feb 2006 16:41] Andrey Hristov
The one space problem in EVENT_BODY is fixed. So, the problem with the privileges has to be sorted now.
[18 Feb 2006 22:33] Andrey Hristov
When this is fixed check bug#17492 whether still persist.
[19 Feb 2006 1:07] 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/2869
[19 Feb 2006 1:40] 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/2873
[20 Feb 2006 16:42] 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/2923
[24 Mar 2006 14:45] 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/4122
[19 Apr 2006 13:35] 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/5142
[19 Apr 2006 15:47] 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/5154
[19 Apr 2006 17:07] Andrey Hristov
Back to patch pending since last commit was doing some refactoring.
[5 May 2006 8:20] Andrey Hristov
This one is obsoleted by the patch for bug #17394 Events namespace is wrong
[5 Jun 2006 15:49] Peter Gulutzan
This detail is not obsolete: the SQL statement is in a column named event_body. In information_schema.routines, routine_body is always 'SQL' and the SQL statement is in routine_definition.
[6 Jun 2006 10:46] Andrey Hristov
So, what is the proposal? Haven event_definition in information_schema.events ?
[22 Jun 2006 11:01] 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/8063
[27 Jun 2006 16:16] Andrey Hristov
The change to I_S.EVENTS will appear in 5.1.12
EVENT_BODY will become SQL, always
EVENT_DEFINITION will be what EVENT_BODY was till 5.1.11
This is to be consistent with I_S.ROUTINES
[28 Jun 2006 6:56] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

Documented I_S.EVENTS changes in 5.1.12 changelog, and in 5.1 Manual's Events and I_S chapters. Closed.