December 7th, 2012, 09:16 AM
Why do Views need to be associated with a user?
I have been using MySQL for years, but have only recently had to use views.
A development company has sent me a schema dump that includes a view definition like:
CREATE ALGORITHM=UNDEFINED DEFINER=`pluto`@`localhost` SQL SECURITY DEFINER VIEW `order_management` AS select `orders`.`ID` AS `ID`,`farms`.`ID` AS `FarmID`,`farms`.`name` AS `FarmName`,`order_types`.`description` AS `OrderType`,`orders`.`start` AS `OrderStartDate`,`orders`.`end` AS `OrderEndDate`,`orders`.`total_req` AS `QuantityRequested`,`orders`.`accepted` AS `QuantityAccepted`,coalesce(sum(`order_worker`.`worker_accepted`),0) AS `worker_approved`,coalesce(sum(`order_worker`.`hops_accepted`),0) AS `hops_approved`,coalesce(sum(`order_worker`.`printed`),0) AS `docs_printed`,`orders`.`memo` AS `OrderMemo`,`orders`.`status` AS `Status` from (((`orders` left join `order_types` on((`orders`.`type` = `order_types`.`ID`))) left join `farms` on((`orders`.`farmID` = `farms`.`ID`))) left join `order_worker` on((`orders`.`ID` = `order_worker`.`orderID`))) group by `orders`.`ID`;
(Which seems over complicated to me!)
My question is why does this view need to have the DEFINER=`user` clause?
Before someone says RTFM, I have been through the MySQL docs, under creating views, the DEFINER clause, and the "Access Control for Stored Programs and Views”. However it did not make much sense to me.
This database will only ever be accessed by a single web server account, views and tables alike, so surely the DEFINER is irrelevant? Or am I missing something?
Any help will be appreciated.
December 7th, 2012, 12:21 PM
It doesn't need that, it's just part of how it was created. That's the result of a SHOW CREATE VIEW.
Just strip it out. All you really need is CREATE VIEW `name` AS...
December 9th, 2012, 11:23 AM
Ah! That's good news thanks