authid current_user and loops

From: Kamal <kamal80_at_virgilio.it>
Date: 9 May 2003 07:40:50 -0700
Message-ID: <4e766a02.0305090640.2b0d410b@posting.google.com>

Hi everybody.

Has anyone a solution to this?

user X:

create or replace package test
authid current_user is

procedure outp (s in varchar2);
procedure outpm (t_owner in varchar2);

end test;

create or replace package body test
is

procedure outp (s in varchar2)
is
begin

dbms_output.put_line (s);

end outp;

procedure outpm (t_owner in varchar2)
is
begin

for i in (select table_name from all_tables

          where owner = upper(t_owner)) loop

outp (i.table_name);

end loop;

end outpm;

end test;

grant execute on test to y;

create table a (c int);
create table b (c int);
create table c (c int);

grant select on a to y;

grant select on b to y;
grant select on c to y;

set serveroutput on size 1000

begin
test.outpm('x');
end;
/

a
b
c

user Y:

set serveroutput on size 1000

begin
x.test.outpm('x');
end;
/

a

(Oracle 9iR2 on Tru64.
Obviously this is not a useful pkg, but i have another one that has the same problem.)

I want yo say: Why? Why? Why?

TIA Kamal Received on Fri May 09 2003 - 09:40:50 CDT