Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ONstaging.* TO 'user'@'10.%' WITH GRANT OPTION

Is it possible to create a view within a stored procedure?

share|improve this question
1  
Which version of mysql do you use? –  Flo Doe Mar 27 at 21:28
    
The version I'm testing on is 5.5.35, the production server this will hopefully migrate to is running percona 5.5.24 –  Steve Buzonas Mar 27 at 22:38
    
Actually, it was the percona server that it was failing on, it's working on 5.5.35 –  Steve Buzonas Mar 27 at 22:43
    
Have you double checked that you have also the proper permissions on the tables from the eestaging schema? –  Flo Doe Mar 28 at 7:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.