Thursday, January 17, 2013

Using Oracle Data Unloader (ODU) to extract data from Oracle Databases while avoiding security and auditing.

There are some very expensive products being sold to stop people from accessing sensitive data in Oracle databases, and some companies even insist that DBAs should manage databases without having access to the data. This post is to remind managers that even with the most expensive security product, if the data is not encrypted, anyone with access to the files on disk can read it. Don't believe what the Salesmen tell you !!!

Anyone with access to Oracle datafiles with unencrypted table data can extract it. The Oracle block structure is reasonably well known, and there are a number of commercial products that allow users to read directly from files without being logged into the database. If you are reading data directly from files, it can be done from standby databases, shutdown databases, or even file fragments or backup pieces.

These data unloader tools are usually meant for extracting data from corrupt files, truncated tables, or for fast data migration or replication, but they can also be used to bypass database security and audit layers for extraction of sensitive data or even password hashes which can be used to guess passwords for privilege escalation.

Following is an example using the Oracle Data Unloader (ODU) tool.  There are other data unloader tools available, and I have not compared them, so can't say which is best, and have not compared prices.

1. Download Oracle Data Unloader (ODU) trial version from and extract the binary and config files.

2. Get the SYSTEM tablespace file names by selecting from v$datafile, or by looking in the OS.

SQL> select ts#,file#,rfile#,name from v$datafile where name like '%system%';
       TS#      FILE#     RFILE#  NAME
---------- ---------- ----------  --------------------------------------------------------------------------------
         0          1          1  /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf

3. Add the datafile to control.txt

$ more control.txt
#ts fno   rfno     filename                                          block_size  is_big_file header_offset blocks
 0 1 1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf

4. Start ODU

$ ./odu

Oracle Data Unloader trial version 4.1.3

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


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
---- ---- ----- ------ -------- --------------- --------------- --------------------------------------------

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 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf
load control file 'control.txt' successful
loading dictionary data......done

loading scanned data......done

5. 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

6. Unload the table data you want.  In this case we are unloading the USER$ table which contains password hashes.

ODU> unload table SYS.USER$

Unloading table: USER$,object ID: 22
Unloading segment,storage(Obj#=22 DataObj#=10 TS#=0 File#=1 Block#=208 Cluster=1)
89 rows unloaded

7. Review the extracted data, load it into another database etc.

$ ls -lrt
total 9136
-rwxr-xr-x  1 oracle oinstall     558 Mar 22  2011 config.txt
-rwxr-xr-x  1 oracle oinstall 2588361 Feb  2  2012 odu
-rw-r--r--  1 oracle oinstall       0 Jan 18 09:40 odu_trace.txt
-rwxr-xr-x  1 oracle oinstall      87 Jan 18 09:40 asmdisk.txt
-rwxr-xr-x  1 oracle oinstall     181 Jan 18 09:50 control.txt
-rw-r--r--  1 oracle oinstall    1440 Jan 18 09:52 user.odu
-rw-r--r--  1 oracle oinstall 2732224 Jan 18 09:52 obj.odu
-rw-r--r--  1 oracle oinstall 3725801 Jan 18 09:52 col.odu
-rw-r--r--  1 oracle oinstall  148683 Jan 18 09:52 tab.odu
-rw-r--r--  1 oracle oinstall   33307 Jan 18 09:52 lob.odu
-rw-r--r--  1 oracle oinstall      40 Jan 18 09:52 lobfrag.odu
-rw-r--r--  1 oracle oinstall   61636 Jan 18 09:52 ind.odu
drwxr-xr-x  2 oracle oinstall    4096 Jan 18 09:53 data
$ cd data
$ ls
SYS_USER$.ctl  SYS_USER$.sql  SYS_USER$.txt

$ more SYS_USER$.txt

0|SYS|1|DCB748A5BC5390F2|0|3|2009-08-13 23:00:59|2013-01-15 10:22:57|2009-08-13 23:56:35|2009-08-13 23:56:35|0||1|||0|0|DEFAU

I was able to extract the password hashes for the SYS user, and all other users in the database (not shown), without logging into the database, or leaving an audit record.  I could then use brute force, or rainbow tables to find the password.  Alternatively I could also extract any unencrypted credit card details, application passwords etc.

To properly secure sensitive data in Oracle Databases, access to the server, datafiles, and backups should be restricted, and data should be encrypted.