Create view without SUPER privileges in phpMyAdmin [duplicate]

Question

I am trying to via SQL tab within the phpmyadmin to create/import a view like this:

CREATE ALGORITHM=UNDEFINED DEFINER=`byname`@`localhost` SQL SECURITY DEFINER VIEW `wr_averages` AS select `nf_users`.`id` AS `id`,(`nf_users`.`points` / `nf_users`.`played`) AS `average_points` from `nf_users` where (`nf_users`.`played` > 24);

I get this error:

#1227 - Access denied; you need the SUPER privilege for this operation

I can not get SUPER privileges at my hosting company, so is there anyway to get around this?

Thanks in advance :-)

Answer 1

From the documentation:

If you specify the DEFINER clause, you cannot set the value to any user but your own unless you have the SUPER privilege. These rules determine the legal DEFINER user values:

• *If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.*
• If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated.

Check your MySQL account, it is not byname@localhost.

Solutions:

• Create new view with DEFINER clause using account that granted with SUPER privilege.
• Do not use DEFINER clause in CREATE VIEW, in this case MySQL will create view DEFINER = CURRENT_USER.