Create view without SUPER privileges in phpMyAdmin

Question

I am trying, through the SQL tab in 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’t get SUPER privileges from my hosting company, so do get around this anyway?

Thanks in advance: -)

Answer 1

From the documentation:

If you specify a DEFINER clause, you cannot set a value for any user other than your own unless you have the SUPER privilege. These rules define the legal meanings of DEFINER:

• * If you do not have the SUPER privilege, the only legal value of the user is your own account, indicated literally or using CURRENT_USER. You cannot set a qualifier for any 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 a new view with the DEFINER clause using an account granted with the SUPER privilege.
• Do not use the DEFINER clause in CREATE VIEW, in this case MySQL will create the DEFINER = CURRENT_USER view.