Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Accessing tables from another Schema

Re: Accessing tables from another Schema

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Fri, 07 May 1999 03:13:47 GMT
Message-ID: <373257dc.4389962@newshost.us.oracle.com>


On Thu, 06 May 1999 16:44:28 +0200, Jens Mayer <jmayer_at_ratundtat.com> wrote:

>Hi folks !
>
>I want to access a table from within a package which was created in
>another schema. This doesn't work, even if I use the schema prefixing
>the table name. If I create the package in the same schema as the table,
>it works fine. Are there any package-specific restrictions in accessing
>objects of another schema ?

Keep in mind that procedures always execute with definer's rights. By default, stored procedures and SQL methods execute with the privileges of their definer, not their invoker.

So in your case, the procedures in the package are being executed as the owner of the package and not as the person executing the procedure.

In the small example below, "scott" removes all privileges from "user1" on the "emp" table. "user1" then creates a package to select from this table. Note that it compiles okay, but does not execute successfully until user1 is not granted select privilege on the table referenced in the procedure.

(Note even if user2 were executing this procedure, and this procedure was created in the schema of user1, we would still have to grant select privilege on user1 and not user2!).



SQL> connect scott/tiger
SQL> revoke all on emp from user1;

Revoke succeeded.

SQL> connect user1/user1
Connected.

SQL> create or replace package fooey as   2 procedure getempsal( p_empno number );   3 end fooey;
  4 /

Package created.

SQL> create or replace package body fooey as   2 procedure getempsal( p_empno number ) is

  3  	      l_salary number;
  4  	      l_stmt varchar2(100);
  5    begin
  6  	      l_stmt := 'select e.sal from scott.emp e where empno =
:bnd1';
  7  	      execute immediate l_stmt into l_salary using p_empno;
  8 end;
  9 end fooey;
 10 /

Package body created.

SQL> exec fooey.getempsal(7900);
BEGIN fooey.getempsal(7900); END;

*
ERROR at line 1:

ORA-00942: table or view does not exist 
ORA-06512: at "USER1.FOOEY", line 7 
ORA-06512: at line 1 


SQL> connect scott/tiger
Connected.
SQL> grant select on emp to user1;

Grant succeeded.

SQL> connect user1/user1
Connected.
SQL> exec fooey.getempsal(7900);

PL/SQL procedure successfully completed.



>
>Jens

Thanks!

Joel

Joel R. Kallman Oracle Service Industries

Columbus, OH                             http://govt.us.oracle.com

jkallman@us.oracle.com                   http://www.oracle.com




The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Thu May 06 1999 - 22:13:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US