Track tools and applications

Understanding where on the network database connections are initiated is important, but it can get much better than that. You can know not only where the requests are coming from, but also what applications are being Figure 3.4

Using tabular reports to view network endpoint information.

3.3 Track tools and applications 67

Chapter 3

used to access your data, what database drivers are being used, which ver-sions they are using, and more. This knowledge is invaluable; it allows you to segment connections based on the application and, therefore, distinguish between access points such as application server versus developers using var-ious tools, and even users using rogue or ad hoc applications. Moreover, you can correlate this information with location information and under-stand who is using which tool, from which network node, and what they are doing. For example, in Figure 3.2, the access diagram not only shows you the node on the network from which the request is coming but also which application made the request.

Tracking the applications and tools that are used to initiate database connections is one of the most overlooked areas in database security and auditing, but also one that is being adopted quickly. Reasons for adoption include the following:

1. Knowing which tools and versions are being used allows you to address points of vulnerabilities.

2. Knowing which tools and versions are being used allows you to comply with IT governance initiatives.

3. Comparing the set of tools being used with the network location allows you to alert on questionable changes.

4. Classification allows you to make sure that company and applica-tion processes are being adhered to.

Getting a full list of applications and tools touching your database is important from both a security perspective as well as a governance perspec-tive. From a security perspective, it allows you to eliminate points of vulner-abilities that can exist on the database client side and/or the database drivers. As an example, Oracle security alert number 46 (www.oracle.com/

technology/deploy/security/pdf/2002alert46rev1.pdf ) discusses a buffer overflow vulnerability that exists in iSQL*Plus in Oracle 9i (releases 9.0.x, 9.2.0.1, 9.2.0.2). The vulnerability allows an attacker to exploit a buffer overflow condition to gain unauthorized access. If you track what tools and applications are being used in your environment, you can decide whether you want to apply the available patch or whether you will “outlaw” the use of iSQL*Plus and revert back to SQL*Plus (which does not have this vul-nerability). Another such example involving iSQL*Plus is CERT vulnera-bility note VU#435974 (www. kb.cert.org/vuls/id/435974).

68 3.3 Track tools and applications

The second use of application and tool information is indirectly related to security and directly related to control and corporate governance. Com-panies often define a set of applications that may be used within the organi-zation and discourage tools that are not within the approved set. This may be because of issues of licensing (where a developer downloads or uses unli-censed software), could be a matter of security and control, and can even be a question of support. In any case, it will often be useful for you to be able to list the set of tools and applications that are being used to access data and track this information periodically within a governance or control initiative.

The third use of this information is to create a baseline of application/

tool access to identify changes that may occur over time. This information is related to the previous two points, but it allows you to look at deltas ver-sus looking at the entire information sets, and it allows you to ver-sustain track-ing over time. It is difficult to go through a lot of information constantly, and often you don’t have to. Instead, you can review the entire set once and then create a baseline based on the initial list. At this point you can use a system to generate a list of deltas every period and look only at these addi-tional access points. You can then decide whether such an addiaddi-tional access point is a problem that you need to handle, or you can decide to add it to the baseline—in either case, your life becomes much easier and the whole process can be sustained over time. You can even ask for a real-time alert to be generated when some new combination of application/tool, IP address, and/or database user comes up.

You’ve seen how important this information can be, so now let’s look at how you get this information. The core data is available from either internal database tables or by inspecting the network packets that are sent to the database server from the clients (for more on network packets, packet dumps, and sniffing tools, see Chapter 10).

Each database maintains information about the sessions and connec-tions inside internal tables—the Monitoring Data Access (MDA) tables in Sybase, the System Global Area (SGA) tables in Oracle, and tables such as sysprocesses and syslogins in SQL Server and Sybase. In all cases you will need elevated privileges to obtain this information. For example, if you want to list all networked SQL Server clients, along with the hostname from which the connection was initiated, the program that is being used, login time, and login name, use the following SQL command; an example result is shown in Figure 3.5:

select hostname, program_name, login_time, loginame from sysprocesses where hostname != ''

3.3 Track tools and applications 69

Chapter 3

In Oracle, the SGA tables and views can be used. As an example, you can use the following statement to get equivalent information in Oracle 10g:

select machine, terminal, program, logon_time, username from v$session;

MACHINE TERMINAL PROGRAM LOGON_TIM USERNAME raven OMS 09-OCT-04 SYSMAN WORKGROUP\RON-SNYHR85G9DJ RON-SNYHR85G9DJ sqlplusw.exe 19-OCT-04 SYSTEM raven OMS 08-OCT-04 SYSMAN raven OMS 08-OCT-04 SYSMAN raven OMS 08-OCT-04 SYSMAN raven OMS 08-OCT-04 SYSMAN raven OMS 08-OCT-04 SYSMAN raven OMS 08-OCT-04 SYSMAN raven OMS 08-OCT-04 SYSMAN raven OMS 08-OCT-04 SYSMAN raven OMS 08-OCT-04 SYSMAN WORKGROUP\RAVEN RAVEN emagent.exe 08-OCT-04 DBSNMP WORKGROUP\RAVEN RAVEN emagent.exe 08-OCT-04 DBSNMP

The record that shows my Query Analyzer connection and my SQL*Plus connection, respectively, is highlighted in both cases.

I mention these internal tables because in most auditing scenarios you will not stop with connection information; you will often also want to audit what was actually done within that session. This will require access to the actual SQL commands sent as part of these sessions—using views such as V$SQL in Oracle and commands such as dbcc inputbuffer in SQL Server. If all you need is connection information, you can usually manage with built-in traces, monitoring events, or audit events, which are available in all major database products.

The main issue with internal tables is that they are constantly being changed. It is therefore fairly simple to get a snapshot of the current state of Figure 3.5

Retrieving network connection information in SQL Server.

70 3.3 Track tools and applications

the database, but if you want to continuously monitor everything that is happening you will have to continuously poll these tables, sometimes at a high frequency, which can affect the performance of the database. Polling is needed because you cannot set triggers or other types of mechanisms on these tables and tables that show you the actual SQL generated in the con-text of these sessions.

The second option does not need to poll the database; it is based on intercepting communication streams and extracting information from the packets as they come into the database. All of the information mentioned previously is readily available in these streams (e.g., in the TCP/IP commu-nications)—and actually much more. For example, the following packet captures for Oracle, SQL Server, and Sybase highlight information such as the source program, sign-on name, client machine, and much more (refer to Chapter 10 for more information on how you can generate these dumps yourself ).Naturally, each such packet also has a TCP/IP header where the client IP resides, providing you with more than enough information to accomplish your task. (Some of the packet contents have been omitted because they do not contribute to this topic).

Oracle: