MySQL Forums
Forum List  »  Stored Procedures

Re: "ANY command denied to user" while creating a view
Posted by: Dmitry Shurupov
Date: October 24, 2013 01:01AM

> 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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: "ANY command denied to user" while creating a view
3701
October 24, 2013 01:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.