I'm writing an sp which creates a proxy table, processes the data and then drops the table. To ensure I don't get clashes if multiple users run the sp, the proxy is created with the user as owner i.e. myuser.ptable
However, the sp fails if I use select * from ptable, it insists on the owner name (unless the owner is DBA). To get it wo work I have to build the query into a varchar ('set @mycmd = select * from ' || current user || '.ptable;') and execute it.
Not ideal, especially with long convoluted queries. I thought the system would default to the owner as current user unless otherwise directed.
Am I missing something?
asked 22 Mar '12, 08:08
Might there be a mismatch between the owner and the caller of the stored procedure? What username is used to create the proxy table - is that the procedure owner (I don't think so, as it would not resolve the name clashing)? If not, the procedure owner won't be able to access the proxy table without owner qualification.
With v11 and above, the SQL SECURITY INVOKER clause may be helpful.