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

[Dmitry Shurupov]

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.