Monday, February 18, 2013

Using ODU to extract audit data from truncated AUD$ table.

The following example shows the use of the ODU (Oracle Database Unloader) to extract records from tables that have been truncated.  In this example I will asume someone has hacked into a database and then truncated the AUD$ table in an attempt to hide their activities.

### Evil DBA logs into the HR database, increases his salary, changes the password for SYS user, then logs into SYS and truncates the AUD$ table.


SQL> connect evil_dba/badpassword
Connected.
SQL> update hr.employees set salary = 100000.00 where first_name = 'EVIL' and last_name = 'DBA';

1 row updated.

SQL> alter user sys identified by badpassword;

User altered.

SQL> connect sys/badpassword as sysdba;
Connected.
SQL> select count(*) from dba_audit_trail;

  COUNT(*)

----------
        40

SQL> truncate table aud$;

Table truncated.

SQL> select count(*) from dba_audit_trail;

  COUNT(*)
----------
         0

SQL> exit


It is reported to the auditor that someone has hacked into the HR database, but no audit records can be found in AUD$ to identify the guilty person and nobody is sure of exactly what was changed.  The Auditor could restore the database and recover it until the SCN prior to the truncate, but that may take a long time, so it would usually be faster to read data from the truncated table blocks if they are still in the datafile and have not been overwritten.

1.  Configure control.txt so ODU has a list of datafiles


[oracle@rac1 odu]$ more control.txt
0          1        1 +DATA/test/datafile/system.266.807721615
1          2        2 +DATA/test/datafile/sysaux.273.807721615
2          3        3 +DATA/test/datafile/undotbs1.272.807721615
4          4        4 +DATA/test/datafile/users.271.807721615
6          5        5 +DATA/test/datafile/example.262.807721709

2. Configure asmdisk.txt so ODU has a list of ASM devices


[oracle@rac1 odu]$ more asmdisk.txt
0 /dev/sdb1 DATA1  4096 1048576
0 /dev/sdc1 FRA1   4096 1048576

3. Start ODU


[oracle@rac1 odu]$ ./odu

Oracle Data Unloader trial version 4.1.3

Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.

Web: http://www.oracleodu.com
Email: magic007cn@gmail.com

loading default config.......

byte_order little
block_size  8192
block_buffers 1024
error at line 3.
db_timezone -7
Invalid db timezone:-7
client_timezone 8
Invalid client timezone:8
asmfile_extract_path   /asmfile
data_path   data
lob_path    /odu/data/lob
charset_name US7ASII
charset name 'US7ASII' not found,will use default charset ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order big
trace_level 1
delimiter |
unload_deleted no
file_header_offset 0
is_tru64 no
record_row_addr no
convert_clob_charset yes
use_scanned_lob  yes
trim_scanned_blob yes
lob_switch_dir_rows 20000
db_block_checksum yes
db_block_checking yes
rdba_file_bits 10
compatible 10
load config file 'config.txt' successful
loading default asm disk file ......


grp# dsk# bsize ausize disksize diskname        groupname       path
---- ---- ----- ------ -------- --------------- --------------- -----------------
   1    0  4096  1024K    10236 DATA1           DATA            /dev/sdb1
   2    0  4096  1024K     5114 FRA1            FRA             /dev/sdc1

load asm disk file 'asmdisk.txt' successful
loading default control file ......


 ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ -----------------------------------------
   0    1    1  8192    85760 N     0 +DATA/test/datafile/system.266.807721615
   1    2    2  8192    60160 N     0 +DATA/test/datafile/sysaux.273.807721615
   2    3    3  8192     5120 N     0 +DATA/test/datafile/undotbs1.272.807721615
   4    4    4  8192      640 N     0 +DATA/test/datafile/users.271.807721615
   6    5    5  8192    12800 N     0 +DATA/test/datafile/example.262.807721709
load control file 'control.txt' successful
loading dictionary data......done

loading scanned data......done


4. Unload the dictionary


ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 208
TABLE OBJ$ file_no: 1 block_no: 240
CLUSTER C_OBJ# file_no: 1 block_no: 144
CLUSTER C_OBJ# file_no: 1 block_no: 144
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found TABPART$'s obj# 576
found TABPART$'s dataobj#:576,ts#:0,file#:1,block#:3824,tab#:0
found INDPART$'s obj# 581
found INDPART$'s dataobj#:581,ts#:0,file#:1,block#:3872,tab#:0
found TABSUBPART$'s obj# 588
found TABSUBPART$'s dataobj#:588,ts#:0,file#:1,block#:3928,tab#:0
found INDSUBPART$'s obj# 593
found INDSUBPART$'s dataobj#:593,ts#:0,file#:1,block#:3968,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found LOB$'s obj# 80
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6
found LOBFRAG$'s obj# 609
found LOBFRAG$'s dataobj#:609,ts#:0,file#:1,block#:4096,tab#:0


5. Check that the SYS.AUD$ table exists and record the TS# and OBJ#


ODU> desc sys.aud$


Object ID:383
Storage(Obj#=383 DataObj#=74574 TS#=0 File#=1 Block#=2296 Cluster=0)

NO. SEG INT Column Name                    Null?     Type                       
--- --- --- ------------------------------ --------- ------------
  1   1   1 SESSIONID                      NOT NULL NUMBER                     
  2   2   2 ENTRYID                        NOT NULL NUMBER                     
  3   3   3 STATEMENT                      NOT NULL NUMBER                     
  4   4   4 TIMESTAMP#                              DATE                       
  5   5   5 USERID                                  VARCHAR2(30)               
  6   6   6 USERHOST                                VARCHAR2(128)              
  7   7   7 TERMINAL                                VARCHAR2(255)              
  8   8   8 ACTION#                        NOT NULL NUMBER                     
  9   9   9 RETURNCODE                     NOT NULL NUMBER                     
 10  10  10 OBJ$CREATOR                             VARCHAR2(30)               
 11  11  11 OBJ$NAME                                VARCHAR2(128)              
 12  12  12 AUTH$PRIVILEGES                         VARCHAR2(16)               
 13  13  13 AUTH$GRANTEE                            VARCHAR2(30)               
 14  14  14 NEW$OWNER                               VARCHAR2(30)               
 15  15  15 NEW$NAME                                VARCHAR2(128)              
 16  16  16 SES$ACTIONS                             VARCHAR2(19)               
 17  17  17 SES$TID                                 NUMBER                     
 18  18  18 LOGOFF$LREAD                            NUMBER                     
 19  19  19 LOGOFF$PREAD                            NUMBER                     
 20  20  20 LOGOFF$LWRITE                           NUMBER                     
 21  21  21 LOGOFF$DEAD                             NUMBER                     
 22  22  22 LOGOFF$TIME                             DATE                       
 23  23  23 COMMENT$TEXT                           VARCHAR2(4000)             
 24  24  24 CLIENTID                                VARCHAR2(64)               
 25  25  25 SPARE1                                  VARCHAR2(255)              
 26  26  26 SPARE2                                  NUMBER                     
 27  27  27 OBJ$LABEL                               RAW(255)                   
 28  28  28 SES$LABEL                               RAW(255)                   
 29  29  29 PRIV$USED                               NUMBER                     
 30  30  30 SESSIONCPU                              NUMBER                     
 31  31  31 NTIMESTAMP#                             TIMESTAMP(6)               
 32  32  32 PROXY$SID                               NUMBER                     
 33  33  33 USER$GUID                               VARCHAR2(32)               
 34  34  34 INSTANCE#                               NUMBER                     
 35  35  35 PROCESS#                                VARCHAR2(16)               
 36  36  36 XID                                     RAW(8)                     
 37  37  37 AUDITID                                 VARCHAR2(64)               
 38  38  38 SCN                                     NUMBER                     
 39  39  39 DBID                                    NUMBER                     
 40  40  40 SQLBIND                                 CLOB                       
 41  41  41 SQLTEXT                                 CLOB                       
 42  42  42 OBJ$EDITION                             VARCHAR2(30)            


6.  Scan tablespace 0 for all extents associated with object 383


ODU> scan extent tablespace 0 object 383

scan extent start: 2013-02-18 15:32:18
scanning extent...
scanning extent finished.
scan extent completed: 2013-02-18 15:32:23


7.  Unload records from the truncated table extents


ODU> unload table sys.aud$ object truncate
Auto mode truncated table.

Unloading table: AUD$,object ID: 383
Unloading segment,storage(Obj#=383 DataObj#=383 TS#=0 File#=1 Block#=2296 Cluster=0)
40 rows unloaded

The 40 records from truncated table AUD$ have now been extracted to file SYS_AUD$.txt and if we look at them we can see the last activity before the truncate.  These records could be loaded into temporary tables using sqlldr.

20027|1|1||EVIL_DBA|rac1.test.com|pts/1|100|0||||||||||||||Authenticated by: DATABASE||oracle||||5||2013-02-18 07:22:50.091234|||0|24432|0000000000000000|||2105868124
20027|2|10||EVIL_DBA|rac1.test.com|pts/1|103|0|HR|EMPLOYEES|||||----------S-----|73953||||||||oracle||||49||2013-02-18 07:22:59.535459|||0|24432|0300150027030000||990832|2105868124||®<90><93>©¬<8f>
20027|3|11||EVIL_DBA|rac1.test.com|pts/1|43|0||SYS||||||||||||||oracle||||22||2013-02-18 07:23:25.381809|||0|24432|040014005B020000||990849|2105868124||<91>[¬ <84>®<92>ÃÃ
20027|4|1||EVIL_DBA|rac1.test.com|pts/1|101|0|||||||||1987|121|43|0|2013-02-18 07:23:37|||oracle|||||22|2013-02-18 07:23:37.142216|||0|24432||||2105868124

The records above relate to the actions of EVIL_DBA and the ACTION# can give a clue to their activities.  

    ACTION ACTION_NAME
---------- ----------------------------
       100 LOGON
       103 SESSION REC
        43 ALTER USER
       101 LOGOFF

So just from the text file we can see the EVIL_DBA connected to the database, did something to the HR.EMPLOYEES table, altered user SYS, then logged out.  The extracted data contains additional details which can be mapped to other database tables (USER$, OBJ$ etc) to give a more comprehensive picture.  There is also LOB data which can be loaded which contains the actual commands executed.

In this example ODU extracted audit data directly from truncated table blocks on ASM devices without logging in to the database, so this could also work if the database was shutdown, or corrupted.