Re: 8.1.7 to 9.2.0.3 and db-link

Database link should be created from the destination side.. i.e. user T2 in this case.

As per the statements below:

 > connect t1/t1_at_SID
 >
 > create database link d1
 >
 > connect to t1 identified by t1
 >
 > using 'SID';
 >

should be:

 > connect t2/t2_at_SID
 >
 > create database link d1
 >
 > connect to t1 identified by t1
 >
 > using 'SID';
 >

Hope this helps
Abhijith.

Serj wrote:
> 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 - 00:42:30 CDT