MySQL Bugs: #17580: Triggers meta-data should be protected by TRIGGER privilege

Bug #17580	Triggers meta-data should be protected by TRIGGER privilege
Submitted:	20 Feb 2006 13:05	Modified:	11 Sep 2006 9:47
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1-BK/5.0 BK	OS:	Any (all)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
The operations for retrieving meta-data of triggers (SHOW TRIGGERS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS) should be allowed only for the users having TRIGGER privilege.

Now, USAGE privilege is enough to retrieve this information.

How to repeat:
(station)[~/MySQL/devel/5.1-tree]> ./client/mysql -u root test
mysql> CREATE TABLE t1(c INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW SET @b = 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.1-tree]> ./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 @b = 1 | AFTER  | NULL    |          | root@localhost |
+---------+--------+-------+------------+--------+---------+----------+----------------+
1 row in set (0.00 sec)

mysql> use information_schema
Database changed

mysql> SELECT * FROM triggers;
[cut]
1 row in set (0.01 sec)

mysql> Bye

Thank you for the bug report.

No, they should not. The standard analog of 'SHOW TRIGGERS' is INFORMATION_SCHEMA.TRIGGERS and according to the standard everybody is allowed to select from it. But one can only see triggers on tables for which he has at least one non-SELECT privilege on at least one column of the table.

So, TRIGGER privilege is not required. But USAGE is wrong either.