PLSQL: Package stored procedure cannot call exe imediate statement

Question

I'm working with oracle 11g R2.

Issue:

I am unable to preform dynamic SQL in SchemaB package against SchemaA tables. SchemaB is being called by the owner of SchemaB.

Background:

I have a package/procedure that has the rights to preform a selection statement on another schema's table.

So I can call the below with no issues.

select * from SchemaA.TableA where blah

I need to be able to run this call from dynamic SQL, because the table from SchemaA could be any of 10. I created the below SQL. I get an error

ORA-01031: insufficient privilegeswhen I run the same statement but withexecute immediate

Code:

v_query varchar2(2000); 
v_query := 'select id, value1, value2 from SchemaA. ' || TableName || ' where id = ' || id;
execute immediate v_query ;

I know that the package does not get the user roles, so I even put the following code into the signature of my package AUTHID CURRENT_USER ; which changed nothing.

What other permissions do I need to set in order to do this?

OR if you have another solution for dynamic table selection, that would be great too.

• Justin Cave: Are you saying that you can do the static SQL statement in the procedure successfully? Or that you can do the static SQL statement outside the procedure at the command line successfully?
• Philᵀᴹ: Are you creating the stored procedure in another schema (let's call it SchemaB), and executing it as the user SchemaB, or something else?

Answer 1

You're doing something wrong. Test case follows...

Create schemaa and schemab:

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 21 14:28:47 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> create user schemaa identified by "schemaa";
SP2-0640: Not connected
SQL> conn / as sysdba
Connected.
SQL> create user schemaa identified by "schemaa";
User created.
SQL> grant connect, resource to schemaa;
Grant succeeded.
SQL> create user schemab identified by schemab;
User created.
SQL> grant connect, resource to schemab;
Grant succeeded.

Login as schemaa, create two tables & grant select on the first to schemab, but don't grant anything on the 2nd table:

SQL> conn schemaa/schemaa;
Connected.
SQL> create table satable ( aaa integer ) ;
Table created.
SQL> insert into satable values ( 1 );
1 row created.
SQL> grant select on satable to schemab;
Grant succeeded.
SQL> create table satablenograntsforb( aaa integer ) ;
Table created.

Login as schemab, create the stored procedure & then test it with the table we have permission to select from:

SQL> conn schemab/schemab;
Connected.
SQL> CREATE OR REPLACE PROCEDURE schemabproc(TableName IN VARCHAR2)
 2 IS
 3 v_query varchar2(2000);
 4 BEGIN
 5
 6 v_query := 'select aaa from schemaa. ' || TableName ;
 7 execute immediate v_query ;
 8
 9 END;
 10 /
Procedure created.
SQL> exec schemabproc('satable');
PL/SQL procedure successfully completed.
SQL>

As you can see, it executed ok.

Let's try on the table we don't have permission on:

SQL> exec schemabproc('satablenograntsforb');
BEGIN schemabproc('satablenograntsforb'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCHEMAB.SCHEMABPROC", line 7
ORA-06512: at line 1

Let's grant and re-test:

SQL> conn schemaa/schemaa
Connected.
SQL> grant select on satablenograntsforb to schemab;
Grant succeeded.

Now we should be able to select from the 2nd table without error:

SQL> conn schemab/schemab 
SQL> exec schemabproc('satablenograntsforb');
PL/SQL procedure successfully completed.
SQL>

Conclusion: There's something else happening. Note that the procedure above runs with AUTHID DEFINER, which is the default.

• Omnia9: Thanks for this. I reset all the permissions and things are working well know.