Detective Guide to Forensics Investigations

This walk-through concentrates on performing forensic incident investigation of SQL attacks. This guide shows techniques for Breach Detection, Incident Investigation, and Postmortem Analysis using the investigation tools in Core Audit.

Starting up

During the recording of the online demo, 10 different attacks were mounted against each of the databases. While each database had different policies, there’s enough information in each repository to locate and understand any of the attacks. For simplicity, this walk-through will discuss the first repository AIX-11g
This guide is best read while working with the product in a different window. To start up the Core Audit Web Console in a new window, click the button above.
For more information, see the Demo Environment page, and the detailed Attack Description.

All in One

The only piece of information we know about the attacks is that they were mounted against the sensitive tables in the database. The sensitive tables are the salaries and cards tables depending on the database. In the AIX-11g repository, it is the salaries table.
Since the demo had a lot of application activity against the sensitive tables, finding the offensive SQLs by hand can be an impossible task. Luckily for us, Core Audit can analyze the regular application activity and find anomalies.
Go to the Policies & Reports panel on the left, click on the Table Security bundle, and select the Table Security New SQLs report. Under the report information you’ll find the Ad-Hoc execution panel. Click the Preview Report link to run the report in the Console. Usually, this report will be saved to disk (or sent in an email) and look like this: Table Security New SQLs

Understanding the Attacks

While this report contains all the attacks, they are all mixed together and do not contain enough information to properly understand them. Lets go through some of them and see what else we can find out.
The first three attacks seem to have originated from the application account HRAPP using sqlplus. To make detection difficult, sqlplus is also the program used as the application program. These attacks are, therefore, equivalent to a SQL injection or malicious application code.
update salaries set salary=9 where emp=9
select * from salaries
update hrapp.salaries set salary=salary*9 where emp=9
Lets first locate these SQLs in the security repository. To go the Dataview panel on the left and keep the Reduced SQL button selected. Select the AIX-11g database and click the Apply button. in the Quick Search box above the SQL list type update.*salaries
The first line in the grid should match the first line we found. Select it and click the Filter SQLs button. The top graph looks empty now, but it actually has a single point in it. Move the mouse into the graph area and you’ll see that the SQL occured on Tue January 15, 2013 at 17:40:00. Since the security repository is in 5 minute aggregates, this SQL actually occurred between 5:40pm and 5:45pm. You might see a different time in your console since the time is adjusted to your local timezone.
The security repository strips out the literals by converting numbers to 9 and strings to empty strings (”). Therefore, all the 9 in the SQLs were actually different numbers when submitted. Our next step will be to locate these SQLs in the compliance repository that contains the exact original SQL.
Select the By SQL button on the left panel, enter HRAPP in the Username box, update salaries set salary in the Text in SQL box, and click the Apply button on the left panel.
Five SQLs appear in the grid, the last of which is the SQL we’re looking for. Click on the line to expand it and see the full text. We now also know the SQL was executed on 5:41:04pm (again, the time you see is adjusted to your local timezone), and that the exact SQL was
update salaries set salary=1000000 where emp=5
This is not enough for us, and we want to see what exactly that user did in this SQL*Plus session. Click on the link at the beginning of the line to see the full session transcript.
The session transcript starts with a few standard SQLs that SQL*Plus always sends at the beginning followed by:
select * from salaries
update salaries set salary=1000000 where emp=5
select * from salaries
This seems to be a fairly simple attack by someone that knew the application password. But since we are curious people, lets find out what was the original salary before the update. Click the Data Changes button on the left panel, enter Salaries in the Table box, 1000000 in the New Values box, and click the Apply button on the left panel
Click on the line to expand it, and under Old Values you’ll see the Salary was previously 181500.
The next question you probably have is how big is the AIX-11g repository to be able to capture so many details. As you can see in the Performance and Sizing page, the size of the AIX-11g repository for the 10 hours of intensive load is 2.8 MB (yes, megabyte).

More Complex Attacks

While the previous attack was not trivial since it was performed using the application account (which has a lot of other activity), it would be nice to see something a little more sophisticated.
In the same Table Security New SQLs report we’ve seen above, there’s this line executed by the DBA TOM using SQL*Plus:
Click the By SQL button on the left panel, empty all the text boxes (including the SQL Depth), type INSERT INTO MYCOPY in the Text in SQL box, and click the Apply button. The second SQL in the grid is what we were looking for. Click the link at the beginning of the line to see the full session transcript.
The session transcript seems fairly short and does not show our SQL:
alter session set recyclebin=off
create table mycopy (salary number, first varchar2(50), last varchar2(50))
grant select any table to tom
create or replace trigger mytrig after delete on mycopy...
delete from mycopy
revoke select any table from tom
select * from mycopy
drop trigger mytrig
drop table mycopy
The reason is that this SQL was a recursive SQL running at a deeper depth (not submitted directly into the database). Uncheck the Depth Zero checkbox and you’ll see all the recursive SQLs as well. Searching through the list shows that our SQL executed inside this SQL:
delete from mycopy
It seems a little curious why an insert selecting from the salary table runs inside a delete.. until we look at the trigger created earlier in the session:
create or replace trigger mytrig after delete on mycopy
  insert into mycopy (select s.salary, e.first, e.last from
        hrapp.employees e, hrapp.salaries s where s.emp =;
Looking at the Table Security New SQLs report we also see that trigger. However, this trigger could have been created at a different time or disguised as a valid change control. This example demonstrated identifying an attack that leveraged a backdoor that is already installed in the database.
The power of Core Audit allows you to identify Zero Day attacks, and other custom attacks that have no signature.

Another Interesting Attack

The previous attack had a small weakness – the trigger creation code contained the malicious SQL. While hiding that trigger code in a change control script might not be difficult, it is still a weakness that could tip off a diligent review of database activity.
Let us investigate the following SQL issued by SYS in the Table Security New SQLs report:
update hrapp.salaries set salary=salary*9 where emp=9
We will again start by looking for the SQL in the compliance repository. Click the By SQL button on the left panel and clear the boxes. Enter SYS in the Username box and update hrapp.salaries set salary in the Text in SQL box. Click the Apply button on the left panel, and you’ll see the following SQL:
update hrapp.salaries set salary=salary*5 where emp=6
This SQL was issued by a local (BEQ) SYS connection (sqlplus / as sysdba). Click on the SES ID link at the beginning of the line to see the full session transcript.
Again the SQL doesn’t appear in the list since it was executed in a deeper depth. Unchecking the Depth Zero checkbox and searching through the list shows that it was executed inside:
BEGIN myproc('xhrqzt1ikqhh3lqsqkotl1ltz1lqsqkn4lqsqkn251vitkt1tdh46'); END;
Looking earlier in the session shows the creation of the myproc procedure:
create or replace procedure myproc(s in varchar2) is
  s1 varchar2(100);
  s1 := translate(s, 'qwertyuiopasdfghjklzxcvbnm1234',
                   'abcdefghijklmnopqrstuvwxyz *.=');
  execute immediate(s1);
If the creation of the procedure happened at a different time using a different session, finding it will be just as simple. Click the By SQL button on the left panel, clear the boxes, type myproc in the Text in SQL box and 0 in the SQL Depth box. Click the Apply button on the left panel and you’ll see all the SQLs that had myproc in them:
create or replace procedure myproc(s in varchar2) is
  s1 varchar2(100);
  s1 := translate(s, 'qwertyuiopasdfghjklzxcvbnm1234',
                   'abcdefghijklmnopqrstuvwxyz *.=');
  execute immediate(s1);
BEGIN myproc('xhrqzt1ikqhh3lqsqkotl1ltz1lqsqkn4lqsqkn251vitkt1tdh46'); END;
BEGIN myproc('ektqzt1zqwst1dneghn1ql1ltstez121ykgd1eeqhh3eqkrl'); END;
BEGIN myproc('ektqzt1zqwst1dneghn1ql1ltstez121ykgd1ikqhh3lqsqkotl'); END;
drop procedure myproc
The strength of this attack is that it did not contain the malicious SQL anywhere in the SQLs that were submitted to the database. The procedure contains nothing suspicious but the ability to execute obfuscated texts. The call to the procedure also contains no visible malicious SQL.
None the less, Core Audit caught this attack like any other attack and allows for full investigation of everything that happened.
It is important to note that while some of these sessions were fully recorded due to being from SYS or DBAs, even application account transcipts that are not generally fully audited, will still contain all the needed information. The reason is Core Audit’s Root Cause feature. Root Cause ensures that when a deep level SQL is captured for whatever reason, all higher depths SQLs are automatically recorded.

Change Control Attack

Core Audit has a change control integration feature. Click on the By Session button on the left panel and you’ll see a list of all the sessions in the databases. The 8th line has CR03 in the Tag column. CR03 is a change control request number that was added by the Change Control Integration policy.
Marking sessions with Change Control numbers allows for easier reconciliation and possibly a lesser scrutiny of the activity. While a human review of the session might glance over some details, the automatic analysis misses nothing.
If you trace this line from the Table Security New SQLs report:
select /**/ * from hrapp.salaries
You will find it came from the CR03 session, where in the middle of many other things, this line executed at depth 1:
select /* test */ * from hrapp.salaries
And it was executed from this block:
t1 varchar2(20);
sqlstm varchar2(100);
  t1 := 'hrapp.salaries';
  sqlstm:= 'select /* test */ * from '|| t1;
  execute immediate sqlstm;


This Guide followed a very specific path in the product. It started from a single report and performed a particular investigation process. While this is an interesting and productive exploration method, it is far from being the only one. Core Audit has more than 200 built-in reports and many possible paths between those and the Dataview interactive tools.
With a little experience you could learn to navigate the reports and dataviews to answer questions you didn’t even know to ask.