RE: Help with ORA-01039
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "cboyle@xxxxxxxxxxxxxxxxxxxxxx" <cboyle@xxxxxxxxxxxxxxxxxxxxxx>, "debaditya.chatterjee@xxxxxxxxx" <debaditya.chatterjee@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Oct 2009 16:53:54 -0400
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:
