cannot select dynamic table created via stored procedure

October 23, 2014 782 views

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

Be the first one to answer this question.