No question at this time
The DBA-Village forum
as RSS feed
Site StatisticsEver registered users | 48667 | Total active users | 1334 | Act. users last 24h | 0 | Act. users last hour | 0 | Registered user hits last week | 21 | Registered user hits last month | 191 |
|
Go up
ORA-02019 : connection description for remote database not found
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...... |
     |
David Johnson |
Apr 22, 2022, 13:18 |
Thanks David
We run the procedure like below:
...... |
     |
Phuti Tsongayinwe |
Apr 22, 2022, 13:25 |
Hello Tso,
check the code of the procedure to s...... |
     |
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...... |
     |
Michel Cadot |
May 05, 2022, 10:05 |
>>> <i> I can't get the AUTHID </i>
You can g...... |
     |
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 |
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: |
       |
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: |
       |
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: |
       |
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: |
       |
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: |
       |
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
|