RE: Help with ORA-01039
- From: "Christopher Boyle" <cboyle@xxxxxxxxxxxxxxxxxxxxxx>
- To: <debaditya.chatterjee@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Oct 2009 16:34:44 -0400
I think that you might be after Invokers Rights vs Definers rights.
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/subprograms.h
tm#LNPLS00809
but I haven't tested it quite like that yet.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Debaditya Chatterjee
Sent: Thursday, October 29, 2009 4:14 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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:
