Re: Finding database link connection origin

On 9 Jan 2002 18:49:53 GMT, n.a.ekern_at_usit.uio.no wrote:

>Hi all.
>
>We are using Oracle 8.1.7 databases.
>
>I am wondering, if a database DB1 is accessed over a database link, is
>there any way to find out the following:
>
>The database from which the database link connection is coming from.
>
>If there is, I am planning on having a logon trigger logging this, and
>maybe deny the logon.
>
>I have looked in v$session but could not find it there.
>I also tested the following:
>
>Created a function in the DB1 database under username test:
>create or replace function testcallstack
>return varchar2
>is
>begin
>return dbms_utility.format_call_stack;
>end;
>/
>
>Executing it from inside DB1 gives the following result:
>variable callstack varchar2(4000)
>exec :callstack:=testcallstack
>print :callstack
> ----- PL/SQL Call Stack -----
> object line object
> handle number name
>d8736a68 5 function TEST.TESTCALLSTACK
>d86790e8 1 anonymous block
>
>But executing from another database like this:
>
>Create database link db1 connect to test identified by testpw using'db1';
>variable callstack varchar2(4000)
>exec :callstack:=testcallstack_at_db1
>print :callstack
>
>gives NULL as result.
>
>I would appreciate very much any tip on how to find the identity of the
>foreign database.
>
>Tia,
>Njål Ekern
>n.a.ekern_at_usit.uio.no
>naekern_at_hotmail.com

You could try v$session_connect_info.
However I have the feeling this is a more or less absurd situation: in a properly designed distributed database you would know where the link was coming from, wouldn't you?
Doesn't this imply you can't exercise any control over your distributed databases, if you need to resort to such measures and even potentially deny the logon?

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jan 09 2002 - 14:54:26 CST