Pete Finnigan's Oracle Security Weblog

In forensics the evidence can be grouped into two blocks. The first is changes made to the database and the second is read activity. Usually an attackers goal is to steal data (read) but he may need to do changes to get that ability in the database; maybe adding a procedure to do SQL Injection or adding a grant to allow access or ....

For changes we can see these via audit if audit exists and has not been purged and if the correct audit trail settings have been enabled. Often sites do not have adequate audit trail for the database engine itself although they may have audit of the application layer. The application-level audit will not catch a procedure being added as the application level audit is probably not targeting this normally; it should be of course. We must always be auditing the use of the database engine itself as well.

We could also rely on redo if the database is in archive log mode and if the logs are available we could use log miner to analyse the changes in the logs but this is not as simple as it first seems as we need to know when and which logs to target based on time or SCN. If we have no idea when the attack started then it would be difficult to target the correct redo/archive logs.

Another idea is to use flashback which in a sense is similar to using redo. The redo has SQL REDO and SQL UNDO for change vectors and flashback uses UNDO to go back in time for a table, for instance. The same issue applies though, unless we know the time the attack occurred it would be very hard to isolate a change to data and use as audit.

Some changes can be tied to a date/timestamp as the relevant tables have these BUT some as we saw in my blog Forensic Analysis for records in Oracle with no Timestamp do not such as for grants of privileges. We can get very high-level guesses as to when a record might have changed but its related to the block level not the individual rows in the table. For instance if the sysauth$ table has a ROWSCN that translates to 27-MAY-2026 then that does not mean a specific row was changed on that day; well it does actually; that was the date the last specific row changed in that data block BUT it is likely not to be the record we are interested in.

To catch read actions we are much more limited. Again audit trail is the best hope for grabbing any read actions. If no audit is set up then redo does not help as READ / SELECT is not written to redo as it is not a change. For the same reason flashback also would not work.

There may be some incredibly limited cases where a READ is written to redo but that almost certainly does not help an investigation. For instance the table SYS.COL_USAGE$ might be written to. This table records columns used in a where clause and the type of predicate, equal, greater than etc. We could check tables such as COL_USAGE$ to see if a table was referenced in a where clause BUT if the attacker referenced the table without a where clause, then no evidence would be gleaned from this table.

If the database has not been shut down then we can look at transient data in the SGA via V$, X$ and GV$ views. We can look at the SQL in the V$SQL family of views or history views or library cache dumps and more. BUT if the database were shutdown, then this transient READ data is lost.

For the rest of this discussion I want to focus on change and on date/timestamps and SCNs. If we can build a timeline of what happened change wise then we may be able to piece together what happened in terms of an attack. If someone just simple read data as above then this does not help in the investigation.

The ideal goal is a reverse time listing over the period of the attack that includes data changes, redo, audit trails. The audit trail would hopefully include READ events as well.

What changes can we focus on that might help in an attack investigation? there are thousands of tables and views in the database that have date/timestamps and some that have SCN but we cannot check every table, view, procedure etc. What at the core actions we can look at now? we can extend this of course going forward but we have to start somewhere.

The main actions that would tell us a lot about changes in an Oracle database would include (USERS, ROLES, PROFILES, OBJECTS including TABLES and PL/SQL). These items would constitute changes to users, roles, grants, and creation of common database objects BUT any indications of READ or course. As I said my plan is to be able to see at a glance in reverse time order. The idea being that we can use this initial report to target where to look next. Layered onto this list of OBJECTS is the issue of what we can learn from meta data. If there are time stamps then this helps, even SCNs in a limited way can help BUT not of these will deal with deletion of objects or READ.

In the next part we will continue to look at some of these core objects and see what we can learn from them.

As I said my goal is to have a reverse time listing (last changes at the top) and highlight what major events happened in the database

#oracleace #oracleacepro #sym_42 #oracle #forensics #database #security #scn #hacking #databreach #timestamp #evidence