Help with ORA-01039

All,

I have a setup where some users query a set of tables owned by another user
using views. As they do not have access to the base tables they cannot do an
explain of their queries. I was hoping to create a procedure that does the
explain plan through another user (IT_PERF which has select any table
privilege) and grant execute on that procedure to these users.  However I am
running into a problem as described below


As you can see below the anonymous block executes the explain plan without
any issues but when I put the code in a procedure it fails with ORA-01039.
What is so special about the procedure that it runs into the error ?  Any
known tricks to workaround this issue ?

Appreciate your response.

Thanks
Deba


SQL> begin
  2  execute immediate 'explain plan set statement_id =
'||''''||'test1'||''''||' for select * from USER1.SOME_VIEW';
  3  end;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.65
SQL> create procedure test
  2  as
  3  begin
  4  execute immediate 'explain plan set statement_id =
'||''''||'test1'||''''||' for select * from USER1.SOME_VIEW';
  5  end;
  6  /

Procedure created.

Elapsed: 00:00:00.55
SQL> exec test;
BEGIN test; END;

*
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view
ORA-06512: at "IT_PERF.TEST", line 4
ORA-06512: at line 1

Other related posts: