RE: Help with ORA-01039

  • From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 29 Oct 2009 16:59:52 -0400

Mark is correct;  I would change slightly part of his explanation.  The
way I think of it is that privileges granted via roles are not available
for use in stored objects--packages, procedures, functions, views (and
possibly others).
 

Paul Baumgartel 
CREDIT SUISSE 
Information Technology 
Prime Services Databases Americas 
One Madison Avenue 
New York, NY 10010 
USA 
Phone 212.538.1143 
paul.baumgartel@xxxxxxxxxxxxxxxxx 
www.credit-suisse.com 

 


________________________________

        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
        Sent: Thursday, October 29, 2009 4:54 PM
        To: cboyle@xxxxxxxxxxxxxxxxxxxxxx;
debaditya.chatterjee@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
        Subject: 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/subprogra
ms.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


=============================================================================== 
 Please access the attached hyperlink for an important electronic 
communications disclaimer: 
 http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html 
 
=============================================================================== 
 

Other related posts: