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 ===============================================================================