Description:
In mysql, we currently have two functions to check user context. user() gives the user for the connection. current_user() gives the user context of the block of code it's called in. a function to get the user context of the sql that caused that code to execute would be useful, calling_user() or somesuch. i.e., table A has a trigger that inserts into to table B, table B should be able to tell that the insert statement is run as trigger A's definer, but the existing functions will only give you the connected user (who ran whatever wrote to trigger A) or the definer of the trigger on table B.
How to repeat:
CREATE TABLE `map_article_ticker` (
`article_id` int(10) unsigned NOT NULL,
`ticker_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`article_id`,`ticker_id`),
KEY `ticker_id` (`ticker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `map_user_ticker` (
`user_id` int(10) unsigned NOT NULL,
`ticker_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`user_id`,`ticker_id`),
KEY `ticker_id` (`ticker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `mv_user_article` (
`user_id` int(10) unsigned NOT NULL,
`article_id` int(10) unsigned NOT NULL,
`modifier` varchar(64) NOT NULL,
`modifier2` varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY (`user_id`,`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE DEFINER=`root`@`localhost` TRIGGER `mat_ins_mv` AFTER INSERT ON `map_article_ticker` FOR EACH ROW insert ignore into mv_user_article (user_id,article_id)
select mut.user_id,NEW.article_id
from map_user_ticker mut
where mut.ticker_id = NEW.ticker_id;
CREATE DEFINER=`root`@`localhost` TRIGGER `mat_upd_err` BEFORE UPDATE ON `map_article_ticker` FOR EACH ROW signal sqlstate '45000' set message_text='You cannot update this table.';
CREATE DEFINER=`root`@`localhost` TRIGGER `mat_del_mv` AFTER DELETE ON `map_article_ticker` FOR EACH ROW delete mv
from map_user_ticker mut
join mv_user_article mv on mv.user_id=mat.user_id
where mv.article_id=OLD.article_id
and mut.ticker_id=OLD.ticker_id;
CREATE DEFINER=`root`@`localhost` TRIGGER `mut_ins_mv` AFTER INSERT ON `map_user_ticker` FOR EACH ROW insert ignore into mv_user_article (user_id,article_id)
select NEW.user_id,mat.article_id
from map_article_ticker mat
where mat.ticker_id = NEW.ticker_id;
CREATE DEFINER=`root`@`localhost` TRIGGER `mut_upd_err` BEFORE UPDATE ON `map_user_ticker` FOR EACH ROW signal sqlstate '45000' set message_text='You cannot update this table.';
CREATE DEFINER=`root`@`localhost` TRIGGER `mut_del_mv` AFTER DELETE ON `map_user_ticker` FOR EACH ROW delete mv
from map_article_ticker mat
join mv_user_article mv on mv.article_id=mat.article_id
where mv.user_id=OLD.user_id
and mat.ticker_id=OLD.ticker_id;
delimiter ;;
CREATE DEFINER=`root`@`localhost` TRIGGER `mv_ins_err` BEFORE INSERT ON `mv_user_article` FOR EACH ROW
begin
set NEW.modifier=USER(),NEW.modifier2=CURRENT_USER();
/*
if SUBSTRING_INDEX(CURRENT_USER(),'@',1)!='root' then
signal sqlstate '45000' set message_text='This table is automatically maintained.';
end if;
*/
end;;
delimiter ;
insert into map_article_ticker values (1,1);
insert into map_user_ticker values (1,1);
insert into mv_user_article values (2,2);
select * from mv_user_article;
Suggested fix:
add a new information function for the user context of the sql that called the trigger/procedure/etc. this would apply to procedures calling other procedures - the trigger example here is just one I had on hand.
