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.
Oracle DBA - A lifelong learning experience 
Identifying database link usage
Posted by John Hallas on September 28, 2015
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:
I can see my DB link being opened in v$dblink (in my own session):
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):
On the origin database:
On the destination database:
Now, I rollback my session on the origin database:
If I query the v$dblink view, I still see my link there, but the transaction is closed now:
The script will not return anything at this point:
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
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.
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.
Posted in 11g new features, Oracle | Tagged: aud$ comment$text, blink_src_global_name, comment$text, database link usage | 2 Comments »