When a client, be it a user or a web application, connects to an Oracle
database instance it first connects to the TNS Listener. The listener hands the
client off to the database instance and logs the connection. The information
logged by the listener about the connection contains, amongst other things,
information such as the time of the connection, the IP address of the client
and the source TCP port as well as the connection string used. This connection
string contains information about the username and the program used to connect.
(N.B. the information in the connection string is entirely under the control of
the connecting client)

All of these pieces of
information together create a "connection profile". Explaining this
further, consider an example network setup: we have a Java web application
running on a server with a IP address of 10.90.100.15
that connects to an Oracle database server. Other than the web application, the
only other client allowed to connect to the Oracle database server is the
company's sole DBA from a fixed IP address, 10.90.90.77. The DBA uses SQL*Plus
to connect and administer the server but only occasionally. This means there
should be only ever be two connection profiles in the listener log file - one
for the web application and one for the DBA.

Connection Profile for the
Web Application

Host: 10.90.100.15

Program: java

User: apache

Connection Profile for the
DBA

Host: 10.90.90.77

Program: sqlplus.exe

User: john.doe

If the DBA one day uses a
different machine with IP address 10.90.90.88 to log into the Oracle database
server, a third connection profile would be created.

Connection Profile 2 for the
DBA

Host: 10.90.90.88

Program: sqlplus.exe

User: john.doe

By parsing the listener log
file one can create a list of connection profiles. This enables the database
administrators and forensic examiners to establish whether unexpected
connection profiles exist. For example, in the course of a well know breach in 2011,
the attacker got a shell on the web server and from there used sqlplus to connect to the database server. At no time
should anyone have connected from the web server to the database server using sqlplus. The attacker's connections created a new and
unexpected connection profile and it was this that expedited the forensic
examination.

To that end, I have
developed the TNS Connection Profiler, which
is a tool that can be used to explore log entries in the Oracle TNS Listener
log file and examine connection profiles.

To examine a listener log
file simply click on the “cogs” icon on the left hand of the toolbar. A dialog
box will open allowing you to select one or more listener log files.

Pressing the “Process”
button will dump the listener entries into an XML format in the specified
output directory. Once the processing has finished the file will be loaded into
the Profiler.

Searches can be performed on
the output. For example, to search for all entries that have used SQL*Plus to
connect to the database server click on the magnifying glass on the toolbar and
select “Program” from the drop down menu on the “Search” window, “contains” and
“sql”:

Then click on “Search”. This
will open up a new tab with the results of the search:

Choosing the “Profiler”
button on the toolbar, 4th from the left will open a new window
detail each different profile in the listener log file.

System Requirements

The TNS Connection Profiler runs on Windows and requires the
.Net Framework. You can download the .Net Framework from http://www.microsoft.com/net