Oracle's Block Browser and Editor tool

Featured Database Articles

September 23, 2009
Oracle's Block Browser and Editor tool

By Steve Callan
In a prior article on Oracle’s Block Browser and Editor tool (BBED), the installation of the tool was covered. In this article, we’ll look at starting a session using a simple database and see how to view data. The database used in this example is minimal in terms of structure. Use dbca to create a general purpose database and pick a sample schema such as SCOTT to practice on. This simplifies what you have to find.

The schema contains four tables (EMP, DEPT, BONUS, and SALGRADE) and two indexes, all contained in the USERS tablespace and further, only one database file to contend with (users01). We don’t care about the other two segment names (PK_EMP and PK_DEPT) because they represent indexes, and really, why would you want to use bbed on an index? Remember, use of bbed is a last resort measure, and there are so many other ways of fixing or rescuing indexes that are much safer.

As an example, let’s get the
rowid for Scott, the analyst (EMPNO=7788) from the EMP table. If not obvious, the ROWID value you will see is likely to be different.

SQL> select rowid from emp where empno=7788;

ROWID
------------------
AAAMfMAAEAAAAAgAAH
Plugging the ROWID into a customized “get_rowinfo” procedure (which is based on the DBMS_ROWID package covered in the prior article) yields the following.

SQL> exec get_rowinfo('AAAMfMAAEAAAAAgAAH');
Type: 1
Data obj number: 51148
Relative fno: 4
Block number: 32
Row number: 7

PL/SQL procedure successfully completed.
Understanding the block number and row number within the block is pretty straightforward. So is the relative file number of 4, and this is easily confirmed via the following query.

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

FILE#||''||NAME||''||BYTES
--------------------------------------------------------
1 /opt/app/oracle/oradata/ORCL2/system01.dbf 503316480
2 /opt/app/oracle/oradata/ORCL2/undotbs01.dbf 36700160
3 /opt/app/oracle/oradata/ORCL2/sysaux01.dbf 272629760
4 /opt/app/oracle/oradata/ORCL2/users01.dbf 5242880
5 /opt/app/oracle/oradata/ORCL2/example01.dbf 104857600
What does type mean? The short answer is that 1 = DATA and 2 = INDEX, so seeing the “1” is confirmation you’re working with the correct type. The data object number is for information and is not essential, but can also be used for confirmation. Once you know the block and file number, you can tie them together for the Data Block Address (dba), of which, the dba value will be used quite extensively in all future bbed commands.

As an alternative to DBMS_ROWID, you could just decode the ROWID from the query based on what is known as the Extended Rowid Format Oracle uses. The base-64 decomposition of 'AAAMfMAAEAAAAAgAAH' works out to be the following.

Data Object ID

Relative File No

Block Number

Row Slot(Number)

AAAMfM

AAE

AAAAAg

AAH

51148

4

32

7

Oracle uses a conversion table of A-Z being 0-25 in decimal form, and a-z being 26-51. Once you have the decimal value, you can derive the binary value, and then string the binary strings together to get the final value. For example, the M and f components under the data object ID are decimal values 12 and 31, whose binary values are 001100 and 011111. AAAMfM is then represented as:

000000 000000 000000 001100 011111 001100
The decimal value of this is then computed to be 51148 (and matches what was shown earlier). Of course, you could also just query DBA_OBJECTS to get the DATA_OBJECT_ID value of the table.

Two other preliminary items need to be mentioned at this point. The first concerns setting up a UNIX environment. If you have the resources at work, where resources implies a totally throw away database on a totally throw away ORACLE_HOME installation on a server that can be down in case you have to reinstall Oracle, you have it made. All you need to do is make the executable (if not already done).

If you don’t have a UNIX server, how do you get access to one (i.e., you’re doing this on a home computer)? One option is to use a virtual machine product, such as VMWare. Another is to buy/use a bare bones PC and install Oracle Enterprise Linux on it. Yet another is to install OEL on your current PC via a partitioning tool and live with booting from multiple operating systems. The boot from multiple systems on your main home PC is not the best choice, but you can make that happen with relatively little effort. If you don’t like having OEL or some other brand of Linux on your PC, you can always remove it later and reclaim the disk space it partitioned.

The second is using a parameter file when starting bbed. Nothing new about what a parameter file is and does as it is just like parameter files used elsewhere in Oracle (exp, imp, sqlldr, etc.). What is new, however, are the parameters and their values or options. Enter “bbed help=y” to see the list. In this example, bbed is located in $ORACLE_HOME/bin after having been compiled elsewhere.

[oracle@oralinux ~]$ bbed help=y
PASSWORD - Required parameter
FILENAME - Database file name
BLOCKSIZE - Database block size
LISTFILE - List file name
MODE - [browse/edit]
SPOOL - Spool to logfile [no/yes]
CMDFILE - BBED command file name
LOGFILE - BBED log file name
PARFILE - Parameter file name
BIFILE - BBED before-image file name
REVERT - Rollback changes from BIFILE [no/yes]
SILENT - Hide banner [no/yes]
HELP - Show all valid parameters [no/yes]
Collect the file name information as shown earlier. Identify the block size of the file(s), and for the initial runs of using this tool, use the browse mode. The contents of a parameter file are shown below.

[oracle@oralinux bbed]$ more bbed.par
blocksize=8192
listfile=/home/oracle/bbed/orcl2files.txt
mode=browse
Starting a session

To start a session using a parameter file named bbed.par located in a directory named bbed, you would use bbed parfile=bbed.par. Once inside a bbed session, you can invoke a help command by specifying “all” to see everything, or by a keyword, such as help set. The set command is useful (and necessary) for navigation between and within blocks and files. If you start with a file based on an 8K blocksize, you could set a new filename (or file number) and its blocksize by using the set filename (or file) and set block commands.

Let’s check out the contents of a block. We’ll use the one block containing all of the EMP table (if you used an 8K block size, it is highly likely all rows will be in the one block). We know that someone in the list of employees is the president, so let’s search for PRESIDENT after setting the data block address, and also having set the offset to 0. The “set dba 4,32” refers to file number 4 and block number 32. We want to start in the beginning of the block, so no offset is needed.

BBED> set dba 4,32
DBA 0x01000020 (16777248 4,32)

BBED> set offset 0
OFFSET 0

BBED> find /c PRESIDENT
File: /opt/app/oracle/oradata/ORCL2/users01.dbf (4)
Block: 32 Offsets: 7831 to 8191 Dba:0x01000020
------------------------------------------------------------------------
50524553 4944454e 54ff0777 b50b1101 010102c2 33ff02c1 0b2c0108 03c24e59
0553434f 54540741 4e414c59 535403c2 4c430777 bb041301 010102c2 1fff02c1
152c0108 03c24e53 05434c41 524b074d 414e4147 455203c2 4f280777 b5060901
010103c2 1933ff02 c10b2c01 0803c24d 6305424c 414b4507 4d414e41 47455203
c24f2807 77b50501 01010103 c21d33ff 02c11f2c 010803c2 4d37064d 41525449
4e085341 4c45534d 414e03c2 4d630777 b5091c01 010103c2 0d3302c2 0f02c11f
2c010803 c24c4305 4a4f4e45 53074d41 4e414745 5203c24f 280777b5 04020101
0103c21e 4cff02c1 152c0108 03c24c16 04574152 44085341 4c45534d 414e03c2
4d630777 b5021601 010103c2 0d3302c2 0602c11f 2c010803 c24b6405 414c4c45
4e085341 4c45534d 414e03c2 4d630777 b5021401 010102c2 1102c204 02c11f2c
010803c2 4a460553 4d495448 05434c45 524b03c2 50030777 b40c1101 010102c2
09ff02c1 151006db bf

<32 bytes per line>
The supported datatypes you can search for are shown via “help find” at the bbed prompt. Note the absence of numeric and date datatypes.

Parameter

Datatype

x

Hexadecimal

d

Decimal

u

Unsigned decimal

o

Octal

c

Character

If a hacker were looking for sensitive employee data, this would be a jackpot. He found a block containing PRESIDENT. To confirm his finding, he could dump the contents of the block at that offset (position within the block, so to speak).

BBED> dump /v dba 4,32 offset 7831 count 32
File: /opt/app/oracle/oradata/ORCL2/users01.dbf (4)
Block: 32 Offsets: 7831 to 7862 Dba:0x01000020
-------------------------------------------------------
50524553 4944454e 54ff0777 b50b1101 l PRESIDENT..wµ...
010102c2 33ff02c1 0b2c0108 03c24e59 l ...Â3..Á.,...ÂNY

<16 bytes per line>
How many records are there in the table? Use the print command (or p for short) to print the metadata about the block. This is where knowledge of data block structure comes in handy. The struct (C-like data structure) of interest is kdbh. The following tells you how many rows via the kdbhnrow value (14 in this case).

BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
b1 kdbhntab @101 1
b2 kdbhnrow @102 14
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 46
sb2 kdbhfseo @108 7521
b2 kdbhavsp @110 7475
b2 kdbhtosp @112 7475
Instead of examining each and every row, you could set the pointer to the last row, and then examine (x for short) each row while spooling output. With print, numbers and dates can be displayed. As a simple example, (knowing ahead of time KING is in row 9 of the EMP table), print the kdbr struct, where the use of kdbr includes the row (starting at 0, not 1). Row 9 in the table is kdbr[8] for our purposes.

BBED> print *kdbr[8]
rowdata[197]
------------
ub1 rowdata[197] @7818 0x2c
Now that we are at offset 7818, it is a simple matter to examine the contents via the print (p for short) and examine (x for short) commands, and use format flags. We know what the EMP table looks like in terms of character (c), numeric (n) and date (t) attributes. In bbed, use “x /rnccntnnn” to examine and format the output.

BBED> x /rnccntnnn
rowdata[197] @7818
------------
flag@7818: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7819: 0x01
cols@7820: 8

col 0[3] @7821: 7839
col 1[4] @7825: KING
col 2[9] @7830: PRESIDENT
col 3[0] @7840: *NULL*
col 4[7] @7841: 17-NOV-81
col 5[2] @7849: 5000
col 6[0] @7852: *NULL*
col 7[2] @7853: 10
From a hacking (or recovery) standpoint, we know the president is named King. What are the column names? Well, they’re not present here, but what’s to stop you from deconstructing the same thing with respect to the base tables represented by the data dictionary views of DBA_TAB_COULMNS and DBA_OBJECTS? You have the data object ID, so finding the object name is trivial, and with the object name (which becomes the table name in this case), you can get the column names from DBA_TAB_COULMNS. The only hard part in all of this is finding the block numbers within the file, and we already know the file is related to the SYSTEM tablespace.

In Closing

The USER$ table in Oracle has a data object ID of 10. That has been the value from at least 8i days. Who are the database users? What are their hashed passwords? That information plus much more is easily extracted. All we’ve done so far is to look at data, and with just that, you should be able to appreciate the enormous power behind bbed.

src