MySQL Bugs: #16597: SUPER needed for triggered tables

Bug #16597	SUPER needed for triggered tables
Submitted:	18 Jan 2006 10:06	Modified:	5 Mar 2006 10:19
Reporter:	Ben Mulder	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Windows (win-xp)
Assigned to:		CPU Architecture:	Any

Description:
'normal' user can not update a table with a trigger on it.
I tested it from Mysql query browser an MS-Access.
message: Access denied; you need the SUPER privilege for this operation
MS-access message :[MySQL][ODBC 3.51 Driver][mysqld-5.0.18-nt]Access denied; you need the SUPER privilege for this operation (#1227)

When logged in as root or administrator: everything works fine.

Priviliges user(mysql.db):
'%', 'test', 'test_usr', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'Y'

How to repeat:
just make a user, who has restricted rights as above.
login like this user to update a table with trigger like:

CREATE TABLE `defaults` (
  `default_id` int(10) unsigned NOT NULL auto_increment,
  `defaultname` varchar(50) NOT NULL,
  `defaultvalue` varchar(100) default NULL,
  `charvalue` varchar(100) default NULL,
  `datevalue` date default NULL,
  `timevalue` time default NULL,
  `integervalue` int(11) default NULL,
  `numericvalue` decimal(18,7) default NULL,
  `datatype` char(1) default NULL,
  `unsignedvalue` int(10) unsigned default '0',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`default_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TRIGGERS` (`TRIGGER_CATALOG`,`TRIGGER_SCHEMA`,`TRIGGER_NAME`,`EVENT_MANIPULATION`,`EVENT_OBJECT_CATALOG`,`EVENT_OBJECT_SCHEMA`,`EVENT_OBJECT_TABLE`,`ACTION_ORDER`,`ACTION_CONDITION`,`ACTION_STATEMENT`,`ACTION_ORIENTATION`,`ACTION_TIMING`,`ACTION_REFERENCE_OLD_TABLE`,`ACTION_REFERENCE_NEW_TABLE`,`ACTION_REFERENCE_OLD_ROW`,`ACTION_REFERENCE_NEW_ROW`,`CREATED`,`SQL_MODE`,`DEFINER`) VALUES 
 (NULL,'smitshoek','tr_i_default','INSERT',NULL,'smitshoek','defaults',0,NULL,' begin\r\ncase new.datatype\r\nwhen \'c\' then set new.defaultvalue = new.charvalue;\r\nwhen \'d\' then set new.defaultvalue = concat(new.datevalue,\' \');\r\nwhen \'t\' then set new.defaultvalue = concat(new.timevalue,\' \');\r\nwhen \'i\' then set new.defaultvalue = concat(new.integervalue,\' \');\r\nwhen \'n\' then set new.defaultvalue = concat(new.numericvalue,\' \');\r\nwhen \'u\' then set new.defaultvalue = concat(new.unsignedvalue,\' \');\r\nend case ;\r\nend','ROW','BEFORE',NULL,NULL,'OLD','NEW',NULL,'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION',''),
 (NULL,'smitshoek','tr_default','UPDATE',NULL,'smitshoek','defaults',0,NULL,' begin\r\ncase new.datatype\r\nwhen \'c\' then set new.defaultvalue = new.charvalue;\r\nwhen \'d\' then set new.defaultvalue = concat(new.datevalue,\' \');\r\nwhen \'t\' then set new.defaultvalue = concat(new.timevalue,\' \');\r\nwhen \'i\' then set new.defaultvalue = concat(new.integervalue,\' \');\r\nwhen \'n\' then set new.defaultvalue = concat(new.numericvalue,\' \');\r\nwhen \'u\' then set new.defaultvalue = concat(new.unsignedvalue,\' \');\r\nend case ;\r\nend','ROW','BEFORE',NULL,NULL,'OLD','NEW',NULL,'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION','');

Suggested fix:
broaden the insert and update grants with the right to execute triggers (think of extending rights to called stored procs in triggers)

it's documented here: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html 

"CREATE TRIGGER was added in MySQL 5.0.2. Currently, its use requires the SUPER  privilege."

This is a duplicate of Bug#9412 (fixed).

Any plans to port this fix to 5.0 ?

Please note, that problem applies NOT to CREATE TRIGGER statement, which may require SUPER priv, but to executing a query (INSERT, for example), which throws a trigger. 
Unfortunately this bug still exists.

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
"
  At trigger activation time, privileges are checked against the DEFINER user.
  This user must have these privileges:
    * The SUPER privilege.
    * The SELECT privilege for the subject table if references to table columns occur via OLD.col_name or NEW.col_name in the trigger definition.
    * The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger definition.
    * Whatever other privileges normally are required for the statements executed by the trigger.
"