Identifying database link usage

As part of ongoing security reviews I wanted to determine if all database links on production systems were in use. That is not very easy to do and this article is a listing of some of the options I have considered to get that information and how it is now possible from 11GR2 onwards.

The first option was to look and see if auditing can be used. The manual states “You can audit statements that refer to tables, views, sequences, standalone stored procedures or functions, and packages, but not individual procedures within packages. (See “Auditing Functions, Procedures, Packages, and Triggers” for more information about auditing these types of objects.)

You cannot directly audit statements that reference clusters, database links, indexes, or synonyms. However, you can indirectly audit access to these schema objects, by auditing the operations that affect the base table.”

So you could audit activities on a base table that a database link might utilise, probably via a synonym. However that would show all table usage but it would be very difficult to break it down to see if a database link had been involved.

On the assumption that the code has a call to “@db_link_name” you could probably trawl ASH data or v$sql to see if a reference is available. It would be more likely that a synonym would be in use and as we have said above, we cannot audit synonym usage but you could maybe find it in v$sql. Again very work intensive with no guaranteed return.

There has been an enhancement request in MoS since 2006 – search for Bug 5098260

Jared Still posted a routine, although he does not claim to be the original author,  which shows a link being actually used. However in reality that is not really a good way of capturing information across many systems unless you enable an excessive amount of tracing or monitoring across all systems. I have demoed usage of it below and it does work

I’ve created a DB link from SNAPCL1A to SNAPTM1. First I opened the DB link:

 

select sysdate from dual@snaptm1;
SYSDATE
---------
22-SEP-15

 

I can see my DB link being opened in v$dblink (in my own session):

select a.db_link, u.username, logged_on, open_cursors, in_transaction, update_sent

from v$dblink a, all_users u

where a.owner_id = u.user_id;

DB_LINK                        USERNAME                       LOG OPEN_CURSORS IN_ UPD

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

SNAPTM1                        SYS                            YES            0 YES NO

The following script can be used to see open DB link sessions (on both databases). It can be executed from any session and it will only show open DB links (that have not been committed, rolled back or manually closed/terminated on the origin database):

col origin for a30

col "GTXID" for a30

col lsession for a10

col username for a20

col waiting for a50

Select /*+ ORDERED */

substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",

substr(g.K2GTITID_ORA,1,35) "GTXID",

substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,

s2.username,

decode(bitand(ksuseidl,11),

1,'ACTIVE',

0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),

2,'SNIPED',

3,'SNIPED',

'KILLED'

) "State",

substr(w.event,1,30) "WAITING"

from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2

where  g.K2GTDXCB =t.ktcxbxba

and   g.K2GTDSES=t.ktcxbses

and  s.addr=g.K2GTDSES

and  w.sid=s.indx

and s2.sid = w.sid;

 

On the origin database:

 

ORIGIN                         GTXID                          LSESSION   USERNAME             State    WAITING

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

teora01x-3762                  SNAPCL1A.cc76ea8a.7.32.983     125.5      SYS                  INACTIVE SQL*Net message from client

 

On the destination database:

 

ORIGIN                         GTXID                          LSESSION   USERNAME             State    WAITING

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

teora01x-4065                  SNAPCL1A.cc76ea8a.7.32.983     133.599    SYSTEM               INACTIVE SQL*Net message from client

 

Now, I rollback my session on the origin database:

 

SQL> rollback;
Rollback complete.

If I query the v$dblink view, I still see my link there, but the transaction is closed now:

 

select a.db_link, u.username, logged_on, open_cursors, in_transaction, update_sent

from v$dblink a, all_users u

where a.owner_id = u.user_id  2    3  ;

 

DB_LINK                        USERNAME             LOG OPEN_CURSORS IN_ UPD

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

SNAPTM1                        SYS                  YES            0 NO  NO

The script will not return anything at this point:

SQL> Select /*+ ORDERED */

substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",

substr(g.K2GTITID_ORA,1,35) "GTXID",

substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,

s2.username,

decode(bitand(ksuseidl,11),

1,'ACTIVE',

0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),

2,'SNIPED',

3,'SNIPED',

'KILLED'

) "State",

substr(w.event,1,30) "WAITING"

from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2

where  g.K2GTDXCB =t.ktcxbxba

and   g.K2GTDSES=t.ktcxbses

and  s.addr=g.K2GTDSES

and  w.sid=s.indx

and s2.sid = w.sid;

no rows selected 

However since at least 11.2.0.3 Oracle have provided a better means of identifying database link usage after the event and not just during

Databases TST11204 and QUICKIE on different servers  -both have dblinks to each other –

TST11204

create user dblinktest identified by Easter2012 ;

grant create session, create database link to dblinktest;

SQL> connect dblinktest/xxxxxxxxxx

Connected.

SQL>  select * from test@quickie;

C1 C2

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

5 five

create database link TST11204 connect to dblinkrecd identified by xxxxxx using 'TST11204';

select * from test@TST11204

At this point we have made a connection  – lets see what we can find out about it. I would advise using the timestamp# column of aud$ to reduce the volume of data that has to be searched.

SQL> select userid, terminal, comment$text from sys.aud$ where comment$text like 'DBLINK%';

 

USERID  TERMINAL        COMMENT$TEXT

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

                        DBLINKRECD DBLINK_INFO: (SOURCE_GLOBAL_NAME=QUICKIE.25386385)

This information is in both source and target databases

It will return the source of a database link session. Specifically, it returns a string of the form:

SOURCE_GLOBAL_NAME=dblink_src_global_name, DBLINK_NAME=dblink_name, SOURCE_AUDIT_SESSIONID=dblink_src_audit_sessionid

where:

dblink_src_global_name is the unique global name of the source database

dblink_name is the name of the database link on the source database

dblink_src_audit_sessionid is the audit session ID of the session on the source database that initiated the connection to the remote database using dblink_name.

So hopefully that might help in identifying if a database link is still in use or when it was last used and it can be used as another part of your security toolkit.