MySQL Forums
Forum List  »  Stored Procedures

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

Yes, it is Stored Procedure. And its content is *really* huge :-) 900+ lines.

The main idea of it is:

CREATE DEFINER=`MY_USER`@`%` PROCEDURE `MY_PROCEDURE`(someId INT)
BEGIN

SET @table_some = CONCAT('some_report_', someId, '_somecriteria');
CALL EXEC(CONCAT('DROP TABLE IF EXISTS ', @table_some, ' cascade'));
CALL EXEC(CONCAT('create table ', @table_some, ' ', '(
id int not null primary key,
title text not null,
color varchar(7) not null
)'));

while something do

set @color = ...;
set @label = ...'

CALL EXEC(CONCAT('insert into ', @table_some, ' values(', @i ,', "', @label ,'", "', @color ,'")'));

end while;

CALL EXEC(CONCAT("CREATE OR REPLACE VIEW some_view_", someId, "_some_criteria AS
SELECT some1, some2, some3
FROM some_view_", someId, "_some_oldcriteria o
JOIN ", @table_some ," t ON o.id = t.criteria_id
GROUP BY some4, some5"));

END

What I have noticed: If I give GRANT SELECT ON *.* to MY_USER (the owner of this database, author of this stored function & all the views used here), then this procedure works ok for ANY user having GRANT ALL for this database. Looks like some "special" privileges stored here (because of ownership)? Changing the DEFINER value of the stored function doesn't matter.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: "ANY command denied to user" while creating a view
2211
October 25, 2013 12:19AM


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.