MySQL Bugs: #16992: Events: information

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

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.

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.

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.

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'

The one space problem in EVENT_BODY is fixed. So, the problem with the privileges has to be sorted now.

When this is fixed check bug#17492 whether still persist.

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

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

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

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

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

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

Back to patch pending since last commit was doing some refactoring.

This one is obsoleted by the patch for bug #17394 Events namespace is wrong

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.

So, what is the proposal? Haven event_definition in information_schema.events ?

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

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

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.