Mining Information from the Listener Log(三)

转完毕(就算开张了,^_^)

In the first two parts of this article series, you have learned how to build a tool to mine information from the listener log, which contains valuable information, yet is usually ignored in database analysis. If you haven’t already done so, please take a take a moment to go through part 1 and part 2 of this series. (Part 1 contains the description of this tool.) In this third, and last article of the series, you will see how to extract enhanced bits of information from the listener log and also how to tie it in with security analysis.

Standard Disclaimer

• • • This paper is solely based on my independent research into the composition of the listener log and not an extract from any other source, including Oracle manuals. While I have made every attempt to derive and present accurate information, there is no guarantee of its accuracy if the format of listener log will be changed in some Oracle version, or has not been changed already for some platforms. Therefore, I’m not making any kind of statement guaranteeing the accuracy of the findings on this paper.
    • The results and output are from an actual production database infrastructure; however, the identifying information, such as IP Address, Host Name, usernames, and so on, have been masked to hide their identities. If it resembles any actual infrastructure, it is purely coincidental.

JDBC Connections

Earlier, we saw that the HOST parameter in the CONNECT_STRING shows __jdbc__ when the client connects to the database using the JDBC thin driver. In this case, the real host name is shown in the HOST parameter in the PROTOCOL_INFO field. Using that knowledge, we can pull up a report on what service name is used from which client. The following query accomplishes this:

The output is:

Now it’s time to match up client IP addresses to make sure they are correctly pointing to the right service name. Some client IP addresses use multiple service names since they run multiple applications. While there is no way to differentiate between applications, we can at least eliminate the possibility that an appserver uses a wrong service name.

SID or Service Name Breakup

Now that you have seen how clients use both service names and SIDs while connecting, a consolidated report will help you to understand how clients connect, and will help us fix two potential issues — clients using SIDs instead of service names, and using wrong service names. We will use this information for JDBC thin clients only, because that’s where most of our applications are.

You can use this query to see the breakup of the SID/Service Names:

The output is:

If the SID column is NULL, then the client has used the Service Name, which is displayed in the next column. This output shows relatively good news. Most of the clients are using Service Name instead of SIDs. This output serves two purposes — (1) you can target the IP addresses shown in the lower portion of the output to change them to service names whenever possible, and (2) you can check the Service Names used by the IP addresses in the upper half if they are accurate.

Mining for Security

So far, we have collected information on the database connections that are legitimate. Listener logs also contain information on unsuccessful attempts for connection. Even though not all unsuccessful attempts are attacks, a pattern might emerge from the attempt to show a potential attack. Using the listener mining tool, we can reveal a lot of those issues.

Listener Password

When you set a password for the listener, the user must supply the correct password before issuing some damaging commands such as stopping the listener. Note: this behavior is different across Oracle versions. In Oracle 9i and earlier, a password, if set, applies to any user trying to manipulate the listener. In Oracle 10g and later, the Oracle software owner without a password can manipulate the listener. So, if a user other than the software owner tries to manipulate the listener, he has to supply the correct password, else he gets the following error:

And this message also finds its way to the listener log file such as the following line:

We can mine this information from the listener log using our tool. Note an important difference, however. This line has just four fields, not the usual six. Therefore, the field ACTION will show the last field on this line — the return code, i.e., 1190.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25702/viewspace-464246/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25702/viewspace-464246/