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 privileges
when I run the same statement but with
execute 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.
Best Answer
You're doing something wrong. Test case follows...
Create
schemaa
andschemab
:Login as
schemaa
, create two tables & grantselect
on the first toschemab
, but don't grant anything on the 2nd table:Login as
schemab
, create the stored procedure & then test it with the table we have permission toselect
from:As you can see, it executed ok.
Let's try on the table we don't have permission on:
Let's
grant
and re-test:Now we should be able to
select
from the 2nd table without error:Conclusion: There's something else happening. Note that the procedure above runs with
AUTHID DEFINER
, which is the default.