17

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Thorsten
  • 12,921
  • 17
  • 60
  • 79

1 Answers1

28

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.

lisymcaydnlb
  • 150
  • 2
  • 12
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • Thanks Vincent, quick answer! I was able to directly grant the select to my user and it works now. – Thorsten Dec 09 '10 at 15:16
  • This is very strange behaviour, does anyone have a reference as to why this is like this. – Reimius May 20 '15 at 15:59
  • 1
    @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. – Vincent Malgrat May 21 '15 at 07:38
  • Your explanation does make a lot of sense. – Reimius May 21 '15 at 18:54