#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Why do Views need to be associated with a user?


    Hi,

    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.

    Chris.
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,965
    Rep Power
    9397
    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...
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0
    Ah! That's good news thanks

IMN logo majestic logo threadwatch logo seochat tools logo