### 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>[¬
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.
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.
No comments:
Post a Comment