Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Accessing tables from another Schema
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!).
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;
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.
Thanks!
Joel
Joel R. Kallman Oracle Service Industries
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com