Description:
The operations for retrieving meta-data of triggers (SHOW TRIGGERS; SELECT *
FROM INFORMATION_SCHEMA.TRIGGERS) should be allowed only for the users having
SUPER privilege.
Now, USAGE privilege is enough to retrieve this information.
How to repeat:
(station)[~/MySQL/devel/5.0-bug7787]> ./client/mysql -u root test
mysql> CREATE TABLE t1 (c INT);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW SET @a = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT USAGE ON *.* TO u1@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR u1@localhost;
+----------------------------------------+
| Grants for u1@localhost |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'u1'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> Bye
(station)[~/MySQL/devel/5.0-bug7787]> ./client/mysql -u u1 test
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| u1@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> SHOW TRIGGERS;
+---------+--------+-------+------------+--------+---------+----------+----------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer |
+---------+--------+-------+------------+--------+---------+----------+----------------+
| trg1 | INSERT | t1 | set @a = 1 | AFTER | NULL | | root@localhost |
+---------+--------+-------+------------+--------+---------+----------+----------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM information_schema.triggers;
[cut]
1 row in set (0.01 sec)
