8.1.7 to 9.2.0.3 and db-link

Hello All,

While upgrading from 8.1.7 to 9.2.0.3.0 I experienced a problem

1. Say we have a user (technological schema) t1 with a prived authorized (by login-pass) db-link d1 and a package p1 containing a function f3 returning a cursor based on db-link d1.
2. And we have an end-user t2 whom user t1 granted execution privelege on the package.

The below-given example shows how it worked on 8.1.7.

But on 9.2.0.3 when user t2 tries to fetch cursor received by t1.p1.f3 he gets an exception "ORA-02019 connection description for remote database not found". :(

connect sys_at_SID as sysdba

drop user t1 cascade;

create user t1 identified by t1;

grant connect,resource to t1;

drop user t2 cascade;

create user t2 identified by t2;

grant connect,resource to t2;

connect t1/t1_at_SID

create database link d1

connect to t1 identified by t1

using 'SID';

create package p1 as

type r1 is record (dummy varchar2(100));

type c1 is ref cursor return r1;

function f1 return varchar2;

function f2 return c1;

function f3 return c1;

end;

/

create package body p1 as

function f1 return varchar2

as

r varchar2(100);

begin

select * into r from dual_at_d1;

return r;

end;

function f2 return c1

as

r c1;

begin

open r for

select * from dual;

return r;

end;

function f3 return c1

as

r c1;

begin

open r for

select * from dual_at_d1;

return r;

end;

end;

/

grant all on p1 to t2 with grant option;

connect t2/t2_at_SID

set serveroutput on;

--create database link d1

• connect to t1 identified by t1
• using 'SID';

begin

dbms_output.put_line('Single value');

dbms_output.put_line('Result1 |=>'||t1.p1.f1);

end;

/

declare

c1 t1.p1.c1;

r1 t1.p1.r1;

begin

dbms_output.put_line('Local cursor');

c1:=t1.p1.f2;

fetch c1 into r1;

dbms_output.put_line('Result1 |=>'||r1.dummy);

end;

/

declare

c1 t1.p1.c1;

r1 t1.p1.r1;

begin

dbms_output.put_line('Remote cursor');

c1:=t1.p1.f3;

fetch c1 into r1;

dbms_output.put_line('Result1 |=>'||r1.dummy);

end;

/

If I get it right, the problem lies in the cursor because the function f1, based on the same query and returning a single value works OK.

As well as uncommenting the lines that create the db-link for the end-user t2 make it work all right.

And making an authorized public db-link also helps.

But this is a security-breach.

Could you please help to solve the problem.

regards,

Serj Received on Mon Jun 23 2003 - 07:03:48 CEST