No question at this time
DBA Top 10
1 M. Cadot 5800
2 B. Vroman 3800
3 J. Péran 2200
4 A. Kavsek 1400
5 P. Tsongayinwe 800
6 D. Johnson 700
7 T. Boles 600
8 D. Walgude 400
9 J. Alcroft 200
9 S. Spb 200
9 P. Wisse 200
About
DBA-Village
Advertise here
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48667
Total active users1334
Act. users last 24h0
Act. users last hour0
Registered user hits last week21
Registered user hits last month191
Go up

ORA-02019 : connection description for remote database not found
Next thread: login as sysdba
Prev thread: Help needed to delete huge volume of data

Message Score Author Date
Hi Team I have a user that has an execute permi...... Phuti Tsongayinwe Apr 22, 2022, 13:01
how referenced when called? begin schemanm.proc...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts David Johnson Apr 22, 2022, 13:18
Thanks David We run the procedure like below: ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Phuti Tsongayinwe Apr 22, 2022, 13:25
Hello Tso, check the code of the procedure to s...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Bruno Vroman Apr 23, 2022, 10:20
Thanks Bruno I will have a look. Regards... Phuti Tsongayinwe Apr 25, 2022, 09:08
As feedback: I checked the code I can't get the...... Phuti Tsongayinwe May 05, 2022, 08:34
Private database link can only be accessed by it...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot May 05, 2022, 10:05
>>> <i> I can't get the AUTHID </i> You can g...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot May 05, 2022, 10:41
Thanks Michel... Phuti Tsongayinwe May 05, 2022, 13:26
Sorry, missing word in previous post: "creati...... Michel Cadot May 05, 2022, 14:58

Follow up by mail Click here

Subject: ORA-02019 : connection description for remote database not found
Author: Phuti Tsongayinwe, South Africa
Date: Apr 22, 2022, 13:01, 31 days ago
Os info: solaris 11
Oracle info: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Message: Hi Team

I have a user that has an execute permissions on a stored procedure that belongs to a different schema.

But when this user run the stored procedure, I get the below errors:

ORA-02019 : connection description for remote database not found

When I run the procedure as an owner it runs fine.

What might be a problem here...

Please help.

Thanks in advance...

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-02019 : connection description for remote database not found
Author: David Johnson, United States
Date: Apr 22, 2022, 13:18, 31 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: how referenced when called?

begin schemanm.procedurenm; end;
/

or thru local or public synonym?
begin procedurenm; end;
/

ora-02019 seems to indicate
begin procedurenm@schemanm; end;
/

which would actually be a call thru a db-link
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-02019 : connection description for remote database not found
Author: Phuti Tsongayinwe, South Africa
Date: Apr 22, 2022, 13:25, 31 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Thanks David

We run the procedure like below:

begin schemanm.procedurenm; end;
/

Thanks for the reply...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-02019 : connection description for remote database not found
Author: Bruno Vroman, Belgium
Date: Apr 23, 2022, 10:20, 30 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hello Tso,

check the code of the procedure to see if it uses AUTHID set to DEFINER or to CURRENT_USER.

I assume that the procedure makes use of a database link; if the procedure is defined with "CURRENT_USER" for AUTHID, the database link is interpreted as "the db link as seen by current_user" and this might cause the ORA-02019...

In this case, the situation has to be checked from a "business point of view" (maybe it is normal that the current user cannot eventually use the procedure).

If the user should be able to use it, then either
 - the database link should be made "public" (currently it is most probably a private database link belonging to the procedure owner),
 - or another "twin" database link should exist in the schema of "current_user" (at first sight not the best move)
 - or the procedure should be defined with AUTHID set to DEFINER (beware, double check why CURRENT_USER was used, there might be good reasons)
 -((there might be other ways, maybe hiding the use of the dblink in a view, but this becomes a bit tricky so first think at previous points))

HTH,

Bruno Vroman
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-02019 : connection description for remote database not found
Author: Phuti Tsongayinwe, South Africa
Date: Apr 25, 2022, 09:08, 28 days ago
Message: Thanks Bruno

I will have a look.

Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-02019 : connection description for remote database not found
Author: Phuti Tsongayinwe, South Africa
Date: May 05, 2022, 08:34, 18 days ago
Message: As feedback:

I checked the code I can't get the AUTHID, for business continuity I created public database links and it works.

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-02019 : connection description for remote database not found
Author: Michel Cadot, France
Date: May 05, 2022, 10:05, 18 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Private database link can only be accessed by its owner.
If you create a function with AUTHID CURRENT_USER then it can only access public database link or private database link owned by the current caller NOT by the function owner (unless it is the current caller).

Here's an example.
As MICHEL I create a private database link and a function with AUTHID DEFINER (the default), then call it from the owner and SCOTT: 
MICHEL> create database link mikj3 connect to michel identified by "michel" using 'mikj3';



Database link created.



MICHEL> create or replace function f return varchar2 as

  2    ret varchar2(100);

  3  begin

  4    select instance_name into ret from v$instance@mikj3;

  5    return ret;

  6  end;

  7  /



Function created.



MICHEL> select f from dual;

F

-------------------------------------------------------------

mikj3



1 row selected.



MICHEL> grant execute on f to scott;



Grant succeeded.



MICHEL> connect scott/TIGER

Connected.

SCOTT> select michel.f from dual;

F

----------------------------------------

mikj3

So far so good: all granted users can use it.
Now I change the function for a CURRENT_USER one: 
SCOTT> connect michel/michel

Connected.

MICHEL> create or replace function f return varchar2 authid current_user as

  2    ret varchar2(100);

  3  begin

  4    select instance_name into ret from v$instance@mikj3;

  5    return ret;

  6  end;

  7  /



Function created.



MICHEL> select f from dual;

F

-------------------------------------------------------------------------

mikj3



MICHEL> connect scott/TIGER

Connected.

SCOTT> select michel.f from dual;

select michel.f from dual

       *

ERROR at line 1:

ORA-02019: connection description for remote database not found

ORA-06512: at "MICHEL.F", line 4

There you have your error because SCOTT, the caller, has no such database link.
Now make the database link public: 
SCOTT> connect michel/michel

Connected.

MICHEL> drop database link mikj3;



Database link dropped.



MICHEL> create public database link mikj3 connect to michel identified by "michel" using 'mikj3';



Database link created.



MICHEL> select f from dual;

F

--------------------------------------------------------------------------------------------------

mikj3



MICHEL> connect scott/TIGER

Connected.

SCOTT> select michel.f from dual;

F

-----------------------------------------

mikj3

This now "works" again because as the database link is public everyone can use it.
Note: creating a database link public is a security as EVERYONE can use it and so EVERYONE has the privilege to access and maybe modify the data in the remote database.

Regards
Michel

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-02019 : connection description for remote database not found
Author: Michel Cadot, France
Date: May 05, 2022, 10:41, 18 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
>>> I can't get the AUTHID

You can get it in DBA/ALL/USER_PROCEDURES.AUTHID.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-02019 : connection description for remote database not found
Author: Phuti Tsongayinwe, South Africa
Date: May 05, 2022, 13:26, 18 days ago
Message: Thanks Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: ORA-02019 : connection description for remote database not found
Author: Michel Cadot, France
Date: May 05, 2022, 14:58, 17 days ago
Message:
Sorry, missing word in previous post:

"creating a database link public is a security" <g>HOLE</g>.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here