TNS Connection Profiler for Oracle

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