RE: Help with ORA-01039

Actually, I think he'd want definers rights, In this case, and, since definers 
rights is the default, I don't think that's the issue.

The problem is, when you call from anonymous PL/SQL block, you're able to 
execute by virtue of privileges granted through roles.  When you execute from 
named procedure, all roles are disabled, and the explain plan fails.  The 
solution is to make sure that the owner of the procedure has the appropriate 
privileges granted directly, and not through a role.

-Mark

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Christopher Boyle
Sent: Thursday, October 29, 2009 4:35 PM
To: debaditya.chatterjee@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Help with ORA-01039

I think that you might be after Invokers Rights vs Definers rights.

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/subprograms.htm#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: