Adding ISPIM Session Recording Information to Guardium DAM to Gain Additional Information on Local Sessions

by John Haldeman, Security Practice Lead

One of the things that Guardium Database Activity Monitoring does best is monitor privileged users such as a DBA accessing a database. That being said, there are some situations where additional information can be brought in from other sources to provide even more information on the nature of the access. This post is about pulling in additional identity and session recording information from IBM Security Privileged Identity Manager to provide additional context for a database administrator's session.

Take the following report in Guardium:

A Report in Guardium DAM showing Sensitive Object Access by Administrative Users

Both rows show administrative accounts (SYS and SYSTEM) accessing sensitive data (a table called HR.EMPLOYEES). The first row shows exactly who accessed what sensitive object and how they did it. It is easy to look at the information in the OS User column and know it is likely John Haldeman that performed the access. Failing that, it being a remote connection from 172.16.20.65 could also be used to help identify who executed the query by looking at who owned that IP during that time period.

The second row in the report illustrates a challenge. The SYS user is a default database account, ORACLE is a default operating system user for Oracle environments, and the access is local. It is very difficult to determine who ran that query. Depending on the database environment, sometimes the UID chain can be used to pick up the user information we are looking for. UID chaining allows Guardium to identify the user chain that occurred before connection to the database in a local session - tracking the execution of switch user (su) commands in UNIX and Linux environments. This would help us identify the user accessing the sensitive table as long as the original account that connected to the database's OS was not some kind of shared account...

A Session Report in Guardium DAM showing UID Chaining

Unfortunately the account is called "shared_dba_1". The identity of the person accessing the database is effectively hidden. At this point you might throw your hands up and suggest that perhaps not knowing the end user identity is a inevitable when your administrative users use shared credentials.

This would indeed be the case if those shared credentials were not managed by a privileged identity management system like IBM Security Privilege Identity Management (ISPIM). ISPIM manages shared credentials checking them out and in of a credential vault when administrators need them. The passwords can be changed on every check in and, if configured correctly, administrators need not even know the credential at all - having the system log in automatically for the administrator. ISPIM logs all of the check in and check out events for the shared credentials and attaches some very useful information for auditing during that process.

In our example above, the shared_dba_1 credential was checked out just a few minutes before the SQL statement was run, and the following information was captured:

Recording Metadata in ISPIM Showing the End User Identification Information We're Looking For

So, now we know who used that local shared operating system credential, which means we know who ran the SQL statement we are interested in. But, we have to check a separate system and line up credentials and timestamps to see that information. Luckily ISPIM keeps all of this session recording information in a DB2 LUW Database, and DB2 LUW data can be imported into Guardium using the enterprise integrator functions, so we can have it all in one interface.

First create a new DB2 LUW Datasource in Tools --> Config & Control --> Datasource Definitions. Enter in the information you need to access the PSRDB database. Unless you own the ISPIM environment you might need some help getting an account and the connection information. Here is an example:

Guardium Datasource Configured to Connect to ISPIM Recording Database

Next, import a table definition from DB2 in Tools --> Report Building --> Custom Table Builder --> Upload Definition. Provide the following SQL statement to base the definition on:

SELECT r.userid, r.applicationuserid, r.localuserid, r.processname,
r.applicationname, r.localhost, r.remotehost, b.data, b.time
FROM piminst.bookmark b, piminst.recording r
WHERE b.recordingid = r.recordingid
ORDER BY b.time

The resulting table definition looks like the following. Optionally specify the data types to help with group selection later:

ISPIM Recording Data Table Definition in Guardium

Finally upload the data, create a custom domain, and then a create a custom query on that imported information. You can also create a schedule to import the data regularly. In the end, the recording data comes into Guardium looking like this:

Putty Session Data in Guardium

Note that more than just the identity information on who was using shared_dba_1 is imported. Additionally, the commands executed through putty are also imported. It shows the SELECT statement that originally started our investigation (red underline), but having the rest of the session's context shows that the query results were spooled to a file (green underline), and then finally SCP'd to another server (blue underline). This incident could be more serious than we first thought!

ISPIM actually recorded much more including the results of the commands executed in the putty session from start to finish. To illustrate the recording capability, you can view a recording of that session sped up to four times the normal speed at the following video:

You might be asking yourself where the value-add of Guardium is to this kind of monitoring if the SELECT statement we were interested in was also detected by ISPIM monitoring. The answer comes from the original screenshot in this blog post. A JDBC tool was used to create the first administrative session and unfortunately ISPIM does not record the sessions through those tools. Additionally, Guardium would also be invaluable in proving to your auditors that when shared credentials are used, they are used only in contexts where session recording has occurred. In this way, the solutions complement each other.