I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change. When I call the procedure I end up with a permission denied issue. Here is the SQL:
DROP FUNCTION IF EXISTS EE_FIELD_NAME;
CREATE FUNCTION EE_FIELD_NAME (id INT)
RETURNS VARCHAR(32) NOT DETERMINISTIC
RETURN (SELECT field_name FROM exp_channel_fields WHERE field_id = id);
DROP PROCEDURE IF EXISTS CREATE_CHANNEL_VIEW;
DELIMITER $$
CREATE PROCEDURE CREATE_CHANNEL_VIEW (IN channame VARCHAR(40))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE f INT;
DECLARE fcur CURSOR FOR SELECT field_id FROM exp_channel_fields f INNER JOIN exp_channels c ON c.field_group = f.group_id WHERE channel_name = channame;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @field_names = '';
OPEN fcur;
field_loop: LOOP
FETCH fcur INTO f;
IF done = 1 THEN
LEAVE field_loop;
END IF;
SET @field_names = CONCAT(@field_names, '`field_id_', f, '` AS `', EE_FIELD_NAME(f), '`, ');
END LOOP;
CLOSE fcur;
SET @field_names = SUBSTRING(@field_names, 1, CHAR_LENGTH(@field_names) - 2);
SET @dv = CONCAT('DROP VIEW IF EXISTS cv_', channame);
PREPARE dstmt FROM @dv;
EXECUTE dstmt;
DEALLOCATE PREPARE dtmt;
SET @cv = CONCAT('CREATE VIEW staging.cv_', channame, ' AS SELECT entry_id, author_id, title, url_title, status, versioning_enabled, view_count_one, view_count_two, view_count_three, view_count_four, allow_comments, sticky, entry_date, year, month, day, expiration_date, comment_expiration_date, edit_date, recent_comment_date, comment_total, ', @field_names, " FROM staging.channel_entries e NATURAL JOIN staging.exp_channels c WHERE c.channel_name='", channame, "'");
PREPARE cstmt FROM @cv;
EXECUTE cstmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
The resulting error is Error Code: 1142, ANY command denied to user 'user'@'10.%' for table '/tmp/#sql_671_0'
. My assumption is that this is a result of the select
driving the create view
does not execute on the table I expect it to. SHOW GRANTS;
gives me GRANT ALL PRIVILEGES ON
staging.* TO 'user'@'10.%' WITH GRANT OPTION
Is it possible to create a view within a stored procedure?