Auditing with the listener log [Debunked]

[Updated 16-Oct-2013] I’ve tried this method in a couple of circumstances, but as it happened to be, OS users then were of the same names of database users. I didn’t notice that the listener log had only osusers, as Riccardo correctly pointed out. And db users that have the same names as os users are, with the demise of client-server applications, less and less likely to happen.

There are several ways in which you can track what a user did in an Oracle database. With standard auditing you can see if someone has logged on the database, whether he/she changed objects or whether he/she did a select, insert, update or delete on a table. With Fine Grained Auditing you can even see who changed the salary of the CEO and when.

On databases prior to version 11g, you’ll probably see that auditing is not configured on the database. Oracle 11g, by default, audits logins and alterations on objects, but older versions do not and usually none has changed that after the database is created.

Last week I got a question from a collegue. A customer asked whether we could see if someone was logged in in the last 3 months on their database. They didn’t have version 11g, so immediately I had my doubts.

And unfortunately, by doubts were well founded. There was no audit data. But there was one last chance: the log of the listener (yes, that log is useful for something). The listener log contains data about every login that runs via Oracle’s Net protocol. And since the user we were looking for was a normal user without accounts to database servers and such, we had a chance of finding this user. Unless… yes, unless the listener log was cleaned up recently.

We were in luck. There was listener login data since 2005. Not uncommon for a listener log by the way. None noticed it when the listener.log file grew to 500 Mb in five years. It took a while before notepad reads a 500 Mb log file on a five year old system, I can tell you.

You usually can find the listener log in $ORACLE_HOME/network/log/listener.log on Unix systems with Oracle 10g or lower versions. For Windows systems, look for %ORACLE_HOME%\network\log\listener.log.

So here you see what we found. (The rows of the listener log are usually rather long, so they don’t fit very well on this page).

08-SEP-2010 08:23:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=service account))(COMMAND=status)(ARGUMENTS=64)(SERVICE=service001)(VERSION=135296000)) * status * 0
08-SEP-2010 08:24:20 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=C:\orant\bin\ifrun60.EXE)(HOST=CLIENT211)(USER=user1234))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.150)(PORT=1362)) * establish * orcl * 0
08-SEP-2010 08:24:35 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=C:\orant\bin\ifrun60.EXE)(HOST=CLIENT176)(USER=user8173))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.176)(PORT=1487)) * establish * orcl * 0
08-SEP-2010 08:24:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=service account))(COMMAND=status)(ARGUMENTS=64)(SERVICE=service001)(VERSION=135296000)) * status * 0
08-SEP-2010 08:24:51 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=C:\orant\bin\ifrun60.EXE)(HOST=CLIENT211)(USER=user1234))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.150)(PORT=1413)) * establish * orcl * 0
08-SEP-2010 08:25:17 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=c:\service-appl\applicatie-productie.exe)(HOST=SERVER075)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.2)(PORT=2644)) * establish * orcl * 0
08-SEP-2010 08:25:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=service account))(COMMAND=status)(ARGUMENTS=64)(SERVICE=service001)(VERSION=135296000)) * status * 0
08-SEP-2010 08:26:01 * (CONNECT_DATA=(SERVICE_NAME=orcl.organisatie.nl)(CID=(PROGRAM=C:\appl\appl2\Applicatie.exe)(HOST=CLIENT200)(USER=user1234))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.139)(PORT=3499)) * establish * orcl.organisatie.nl * 0
08-SEP-2010 08:26:02 * (CONNECT_DATA=(SERVICE_NAME=orcl.organisatie.nl)(CID=(PROGRAM=C:\appl\appl2\Applicatie.exe)(HOST=CLIENT200)(USER=user1234))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.139)(PORT=3501)) * establish * orcl.organisatie.nl * 0

So assume we were looking for user8173. A little find on the begin date and then the username is all it takes. So in very little time we found the user logging in on September 8th at 08:24:35. Bingo! Who had thought we would actually be able to find this? Even the customer didn’t.

So what do we learn from this? Never cleanup the listener log? Send it to a special lun on the SAN, just to be able to read 10 years ago? I don’t think that’s the solution. Better to implement database auditing and send the audit trail to a file system where only few (a security officer perhaps) can go. Database auditing has much more options.

If only cleaning up the data wasn’t harder prior to 10.2.0.5 than cleaning up the listener log. Since 10.2.0.5 and 11.1.0.7 there is a package dbms_audit_mgmt that can do that audit cleanup for you. But that’s another story.

<!–[if !mso]> <! st1\:*{behavior:url(#ieooui) } –>

08-SEP-2010 08:23:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=service account))(COMMAND=status)(ARGUMENTS=64)(SERVICE=service001)(VERSION=135296000)) * status * 0
08-SEP-2010 08:24:20 * (CONNECT_
DATA=(SID=orcl)(CID=(PROGRAM=C:\orant\bin\ifrun60.EXE)(HOST=CLIENT211)(USER=user1234))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.150)(PORT=1362)) * establish * orcl * 0
08-SEP-2010 08:24:35 * (CONNECT_
DATA=(SID=orcl)(CID=(PROGRAM=C:\orant\bin\ifrun60.EXE)(HOST=CLIENT176)(USER=user8173))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.176)(PORT=1487)) * establish * orcl * 0
08-SEP-2010 08:24:39 * (CONNECT_
DATA=(CID=(PROGRAM=)(HOST=)(USER=service account))(COMMAND=status)(ARGUMENTS=64)(SERVICE=service001)(VERSION=135296000)) * status * 0
08-SEP-2010 08:24:51 * (CONNECT_
DATA=(SID=orcl)(CID=(PROGRAM=C:\orant\bin\ifrun60.EXE)(HOST=CLIENT211)(USER=user1234))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.150)(PORT=1413)) * establish * orcl * 0
08-SEP-2010 08:25:17 * (CONNECT_
DATA=(SID=orcl)(CID=(PROGRAM=c:\service-appl\applicatie-productie.exe)(HOST=SERVER075)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.2)(PORT=2644)) * establish * orcl * 0
08-SEP-2010 08:25:39 * (CONNECT_
DATA=(CID=(PROGRAM=)(HOST=)(USER=service account))(COMMAND=status)(ARGUMENTS=64)(SERVICE=service001)(VERSION=135296000)) * status * 0
08-SEP-2010 08:26:01 * (CONNECT_
DATA=(SERVICE_NAME=orcl.organisatie.nl)(CID=(PROGRAM=C:\appl\appl2\Applicatie.exe)(HOST=CLIENT200)(USER=user1234))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.139)(PORT=3499)) * establish * orcl.organisatie.nl * 0
08-SEP-2010 08:26:02 * (CONNECT_
DATA=(SERVICE_NAME=orcl.organisatie.nl)(CID=(PROGRAM=C:\appl\appl2\Applicatie.exe)(HOST=CLIENT200)(USER=user1234))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.139)(PORT=3501)) * establish * orcl.organisatie.nl * 0

Advertisements

About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle security and tagged , , , , , . Bookmark the permalink.

2 Responses to Auditing with the listener log [Debunked]

  1. Riccardo says:

    Hi,
    I found this googling around for “username in listener log”.
    I’d like to note that the “user” to be found in the listener.log is NOT the database user but the operating system user used at the client’s end.
    That can be useful of course, but if you are looking (like me) for oracle users because you don’t have any audit active on the database, then the listener.log is not the proper file to look for.

    Riccardo

    • marceljank says:

      Hi Riccardo,

      Sorry for the late reaction. I was on vacation. You are absolutely right. And I started to get an inkling that this post wasn’t correct as well recently. The cases when I was able to use this type of auditing was when the OS user and db user were the same. Which is occurring less and less often nowadays.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s