cannot select dynamic table created via stored procedure
Inside the stored procedure i created dynamic temp tables with system time. table created with name like this 'tempccdata20140904101131_2' I want to give SELECT permission for some of my SQL Users for these TEMP tables which was dynamically created by the above SP's.
For the above req, I tried the below query from a ROOT user to give permission for the table with the prefix temp
GRANT ALL PRIVILEGES ON dbname .`Temp%` TO 'TEST'@'%' WITH GRANT OPTION;
But, still the above privileges for the user 'TEST' can't able to SELECT these TEMP tables and it gives below error:
SELECT command denied to user 'TEST'@'localhost' for table 'temp_ccdata_20140904101131_2'
pls anyone suggest if there are any possibilities to grant SELECT access for the dynamic tables created by a Stored Procedure