Bug #78846 TRIGGER privilege documentation is incorrect, and dangerous
Submitted: 15 Oct 2015 12:00 Modified: 18 Feb 2016 20:12
Reporter: Service Desk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version:5.6 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: mysqldump, trigger privilege

[15 Oct 2015 12:00] Service Desk
Description:
the description of "trigger" privilege is incorrect :-
"You must have this privilege for a table to create, drop, or execute triggers for that table."
In order to "execute" a trigger, it is necessary to be able to perform the action which initiates the trigger (ie update table) and the action performed by the trigger (ie insert into table_history ...). 

The trigger privilege seems to allow CREATE TRIGGER, DROP TRIGGER and SHOW TRIGGERS, and it enables the "--triggers" parameter in mysqldump

The TRIGGER privilege overrides the "SELECT ON *.*" privilege, because it even hides INFORMATION_SCHEMA.TRIGGERS rows from being selected (which is unintuitive). 
Even worse, TRIGGER='N' hides these rows without warning, such that mysqldump -triggers silently ignores the "--triggers" parameter

How to repeat:
CREATE TABLE TEST.R1 (id int);
CREATE TABLE TEST.R2 (id int, dt datetime);
CREATE TRIGGER TEST.R1_R2 AFTER INSERT ON TEST.R1 FOR EACH ROW INSERT INTO TEST.R2 VALUES (OLD.id, NOW());
GRANT INSERT ON TEST.* TO R_USER@'localhost';
REVOKE TRIGGER ON *.* FROM R_USER@'localhost';

# At this point, if R_USER inserts, the trigger "executes" and creates a record in R2
# However, if R_USER performs "SELECT * FROM INFORMATION_SCHEMA.TRIGGERS", it returns 0 rows
# "mysqldump -u R_USER --triggers -d TEST" returns the DROP/CREATE information for the TEST database, but silently ignores the "CREATE TRIGGER R1_R2" ... so the data from mysqldump does NOT reproduce the TEST database 

** Security Issue
mysqldump should warn, if --triggers parameter is chosen with user which cannot show triggers

Suggested fix:
SHOW TRIGGER, and "mysqldump --triggers" should be dependent on REFERENCES and/or SELECT privileges. 
The TRIGGER privilege should control CREATE TRIGGER and DROP TRIGGER

The documentation in http://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html#priv_trigger should reflect reality - ie delete ",or execute " from the documentation
[18 Feb 2016 19:02] Paul Dubois
I have addressed the documentation aspects of this bug report (to indicate that the TRIGGER privilege also confers ability to display triggers, and that trigger execution requires the trigger DEFINER to possess the TRIGGER privileges.)

Reclassifying this bug report from a docs bug to a server feature request for possible change to mysqldump --triggers behavior as indicated in the report. (Might also apply to mysqlpump --{include,exclude}-triggers.)
[18 Feb 2016 20:12] MySQL Verification Team
This bug is now only feature request, so it is appropriately being handled ....