6

There are two schemas in a Oracle database.

MYSCHEMA that is controlled by me.

OTHERSCHEMA that is not controlled by me.

I just know I can get result from select * from OTHERSCHEMA.OTHEROBJECT. However, OTHEROBJECT is a synonym.

In my package, I have a statement like

insert into MYSCHEMA.MYTABLE(COL1) select COL1 from OTHERSCHEMA.OTHEROBJECT;

But it gave me Table or view does not exist.

How can I solve or bypass this problem? Thanks!

Alex Yeung
  • 2,495
  • 7
  • 31
  • 48
  • You are logged in to `MYSCHEMA` and run `select * from OTHERSCHEMA.OTHEROBJECT` and it works? Your package is compiled under `MYSCHEMA`? Have you tried running the insert statement outside of the package as a standalone statement - does that work? – John Doyle Dec 22 '11 at 04:45

1 Answers1

11

I assume you received the privilege to select from otherschema.otherobject by means of a role as opposted to a direct grant (such as grant all on otherschema.otherobject to myschema). If this is the case, the privileges within this role will not be used to determine what rights you have within a PL/SQL block.

See also How Roles Work in PL/SQL Blocks (Oracle Docu, where it says under Roles Used in Named Blocks with Definer's Rights:

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.)

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293