DB Audit is Deleted by the Attacker

Oracle Forensics tips by Paul Wright

Oracle Forensics Scenario 5 - DB Audit is Deleted by the Attacker

The defender of the database has set Extended DB auditing which is good as it records the full text of the queries but bad because the audit can be easily deleted and in this scenario it has. The trigger for the investigation is missing audit from SYS.AUD$. The Admin is looking back on the decision not to audit to the OS as a mistake. Additionally the Analyst informs the Admin that secure configurations now audit to remote SYSLOG in preference to logging on the local server at all. Also the DBA is not archiving their redo logs. The Forensic Analyst is going to use their expertise to make the best of a bad lot and attempt to find out the source of the suspected hack.

The attacker has again leveraged PLSQL injection vulnerability through the MODPLSQL Gateway from application server and is able to query the backend database with the privileges of the web application which still include SELECT ANY DICTIONARY. One advantage the analyst has is in this case is that the attack has occurred very recently and the current online redo logs have not yet been overwritten.

This is the same attack which bypassed the IDS due to use of the CHR function for SYS.

SQL> SHOW USER

USER is "DBSNMP"

SQL> SELECT paSsWOrd, username from DBA_USERS where username = ( chr(83)|| chr(89) || chr(83));

PASSWORD USERNAME
------------------------------ ------------------------------
0C15939594CE60D2 SYS

The attacker is aware of the DB auditing and deletes the audit trail entry

DELETE FROM sys.aud$ WHERE sessionid =213622;

The audit trail does not show the attack. A gap can be ascertained by querying the ROWID, SESSIONID and ENTRYID from SYS.AUD$. This shows that there is certainly a gap in the audit. But what is the gap? The Analyst has to use the redo logs to answer this question.

Redo logs record the historic changes made to the data in the database so that if the data files are corrupted the backups can be brought back up to present state by applying the changes that are recorded in the redo log to the older backup data files (using the control file to link the two).

Redo log views:

v$log
v$logfile
v$log_history
v$thread

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ---------------- ---
         1          1        167   52428800          1 NO CURRENT          16-M
         2          1        165   52428800          1 NO INACTIVE         13-M
         3          1        166   52428800          1 NO INACTIVE         13-M

SQL> SELECT * FROM V$LOGFILE;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ----------------------------------------------------
        3         ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\XP10R2JA\REDO03.LOG
        2         ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\XP10R2JA\REDO02.LOG
        1         ONLINE E:\ORACLE\PRODUCT\10.2.0\ORADATA\XP10R2JA\REDO01.LOG

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96521/onlineredo.htm

The analyst sets up LogMiner to read the redo logs.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'E:\ORACLE\PRODUCT\10.2.0\ORADATA\XP10R2JA\REDO03.LOG', OPTIONS =>DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'E:\ORACLE\PRODUCT\10.2.0\ORADATA\XP10R2JA\REDO02.LOG', OPTIONS =>DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'E:\ORACLE\PRODUCT\10.2.0\ORADATA\XP10R2JA\REDO01.LOG', OPTIONS =>DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

select scn,timestamp,username,table_name,operation from v$logmnr_contents;

EXECUTE DBMS_LOGMNR.END_LOGMNR;

N.B to save time the analyst only need load the relevant redo log file redo01.

SQL> desc v$logmnr_contents;

Name                                      Null?    Type
----------------------------------------- -------- ---------------------------
SCN                                                NUMBER
CSCN                                               NUMBER
TIMESTAMP                                          DATE
COMMIT_TIMESTAMP                                   DATE
THREAD#                                            NUMBER
LOG_ID                                             NUMBER
XIDUSN                                             NUMBER
XIDSLT                                             NUMBER
XIDSQN                                             NUMBER
PXIDUSN                                            NUMBER
PXIDSLT                                            NUMBER
PXIDSQN                                            NUMBER
RBASQN                                             NUMBER
RBABLK                                             NUMBER
RBABYTE                                           NUMBER
UBAFIL                                             NUMBER
UBABLK                                             NUMBER
UBAREC                                             NUMBER
UBASQN                                             NUMBER
ABS_FILE#                                          NUMBER
REL_FILE#                                          NUMBER
DATA_BLK#                                          NUMBER
DATA_OBJ#                                          NUMBER
DATA_OBJD#                                         NUMBER
SEG_OWNER                                          VARCHAR2(32)
SEG_NAME                                           VARCHAR2(256)
TABLE_NAME                                         VARCHAR2(32)
SEG_TYPE                                           NUMBER
SEG_TYPE_NAME                                      VARCHAR2(32)
TABLE_SPACE                                        VARCHAR2(32)
ROW_ID                                             VARCHAR2(18)
SESSION#                                           NUMBER
SERIAL#                                            NUMBER
USERNAME                                           VARCHAR2(30)
SESSION_INFO                                       VARCHAR2(4000)
TX_NAME                                            VARCHAR2(256)
ROLLBACK                                           NUMBER
OPERATION                                          VARCHAR2(32)
OPERATION_CODE                                     NUMBER
SQL_REDO                                           VARCHAR2(4000)
SQL_UNDO                                           VARCHAR2(4000)
RS_ID                                              VARCHAR2(32)
SEQUENCE#                                          NUMBER
SSN                                                NUMBER
CSF                                                NUMBER
INFO                                               VARCHAR2(32)
STATUS                                             NUMBER
REDO_VALUE                                         NUMBER
UNDO_VALUE                                         NUMBER
SQL_COLUMN_TYPE                                    VARCHAR2(30)
SQL_COLUMN_NAME                                    VARCHAR2(30)
REDO_LENGTH                                        NUMBER
REDO_OFFSET                                        NUMBER
UNDO_LENGTH                                        NUMBER
UNDO_OFFSET                                        NUMBER
DATA_OBJV#                                         NUMBER
SAFE_RESUME_SCN                                    NUMBER
XID                                                RAW(8)
PXID                                               RAW(8)
AUDIT_SESSIONID                                    NUMBER