MySQL Bugs: #34787: TRIGGER definition should support SQL SECURITY = definer|invoker

Bug #34787	TRIGGER definition should support SQL SECURITY = definer\|invoker
Submitted:	24 Feb 2008 13:15	Modified:	15 Jan 2014 15:47
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.1.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
TRIGGER privilege is sufficient to SET global priveleges.

I think this is a mistake form when TRIGGER was introduced (replacing SUPER) as a privilege to create and execute trigger.

This report is a 'side effect' of http://bugs.mysql.com/?id=34780

How to repeat:
-- as root do;

show grants for 'root'@'localhost';
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' blabla

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `test`.`tester`$$

CREATE TRIGGER `test`.`tester` AFTER INSERT on `test`.`tester`
FOR EACH ROW BEGIN

SET GLOBAL event_scheduler = off;

END$$

DELIMITER ;

SET GLOBAL event_scheduler = on;

CREATE TABLE `tester` (               
          `id` bigint(20) DEFAULT NULL        
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

insert into tester values (1);

show global variables like 'event_scheduler';  -- returns off
SET GLOBAL event_scheduler = on;

show grants for 'tester1'@'localhost';
-- GRANT INSERT, TRIGGER ON *.* TO 'tester1'@'localhost'

-- as tester1 do;
insert into tester values (2);

-- as root do;
show global variables like 'event_scheduler'; -- returns off

Suggested fix:
What applies for EVENTs should also apply for TRIGGERS:

"In addition, if (added by PL: and only if) the event's definer has the SUPER privilege, that event may read and write global variables. As granting this privilege entails a potential for abuse, extreme care must be taken in doing so."

.. and by the way also for TRIGGERS I would consider SQL SECURITY option. That would make all 'stored programs' consistent in this respect!

first line should read "TRIGGER privilege is sufficient to SET global VARIABLES".

changed from 'triggers' to 'privileges' sub-category.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about what TRIGGER priviledge is for at http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html

Also please not you created TRIGGER with definer root who has all necessary privileges to set global variables:

select DEFINER  from information_schema.triggers where TRIGGER_SCHEMA='test' and TRIGGER_NAME='tester';
DEFINER
root@localhost

I reopen and change category to 'triggers' and set severity "S4" (feature request)

I created the TRIGGER as root but executed as a non-privileged user (2nd time)

Feature request is:
TRIGGER definition should support SQL SECURITY = definer|invoker

to avoid this silly and dangorous situations!

Also synopsis was changed!

Thank you for the reasonable feature request.

This feature req got new status to verified (for book keeping).

WOW .. after 6 years.  I'll have to admit that I completely forgot this!

And 6 more!  This just bit me because the user who originally created the trigger got deleted.  I usually use SQL SECURITY INVOKER to avoid that becoming a problem with VIEWS, FUNCTIONS, and stored procedures, so I just expected it to work here.