Re: "ANY command denied to user" while creating a view

[Dmitry Shurupov]

> Let's see the permissions on the Stored Procedure. It sounds like it was not "INVOKER".

Thank you for answering.

The stored procedure has been created by the same user it was invoked. Also, we've tried to play with SQL SECURITY & DEFINER values but nothing seemed to change.

This works only if "users" is a real table:
set @st = 'CREATE OR REPLACE VIEW users_view as SELECT * FROM users';
PREPARE stmt FROM @st;
EXECUTE stmt;

However, if the target ("users") is not a real table but view ("users2")...

This works (without wrapping into Stored Procedure):
CREATE OR REPLACE VIEW users2_view as SELECT * FROM users2;

This doesn't work:
set @st = 'CREATE OR REPLACE VIEW users2_view as SELECT * FROM users2';
PREPARE stmt FROM @st;
EXECUTE stmt;
ERROR 1142 (42000): ANY command denied to user 'MY_USER'@'%' for table '/tmp/#sql_446b_0'

"users2" is like this:
CREATE ALGORITHM=UNDEFINED DEFINER=`MY_USER`@`%` SQL SECURITY DEFINER VIEW `users2` AS select ... from users;

We've tried to change SQL SECURITY of "users2" to INVOKER but the same error appeared.
Also, we've tried to add SQL SECURITY INVOKER to the "CREATE OR REPLACE VIEW" command in @st but the same error appeared.