Accessing another user's table within an Oracle Stored Procedure
The owner of a procedure must be granted privilege to access the underlying objects directly, not through a role. To have the same level of access as your procedures, use the following commands:
SET ROLE NONE;
To access another table from a procedure, you need to be granted SELECT directly, not through a role:
GRANT SELECT ON TURAT03.GESCHAEFTE TO <your_user>;
This article by Tom Kyte contains additional info.
Related videos on Youtube
Thorsten
self employed developer who relaxes with endurance sports in sparetime
Updated on July 09, 2022Comments
-
Thorsten almost 2 years
I'm writing a stored procedure to copy data from one user's table to another schema. Basically, it is a series of INSERT .. SELECT statements such as this:
INSERT INTO GESCHAEFTE SELECT * FROM TURAT03.GESCHAEFTE WHERE kong_nr = 1234;
This works fine when issueing from sqlplus (or TOAD for me ;-)) so I know that I have sufficient privileges, but when this is part of stored procedure like this:
CREATE OR REPLACE FUNCTION COPY_KONG (pKongNr IN NUMBER) RETURN NUMBER AUTHID CURRENT_USER IS BEGIN INSERT INTO GESCHAEFTE SELECT * FROM TURAT03.GESCHAEFTE WHERE kong_nr = pKongNr; END;
I get an Oracle error:
[Error] ORA-00942 (11: 22): PL/SQL: ORA-00942: table or view does not exist
As you can see, I've already inserted an
AUTHID
, but to no avail.What else can I do? I'm pretty much at the end of my ideas here.
-
Thorsten over 13 yearsThanks Vincent, quick answer! I was able to directly grant the select to my user and it works now.
-
Reimius almost 9 yearsThis is very strange behaviour, does anyone have a reference as to why this is like this.
-
Vincent Malgrat almost 9 years@reimius: We can only guess at the "why" since the documentation usually doesn't mention a reason for going for a particular implementation. Maybe Oracle choose not to maintain a dependency link between procedures and roles because it could lead to massive decompilation affecting many schemas when a role is modified.
-
Reimius almost 9 yearsYour explanation does make a lot of sense.