Oracle’s Block Browser and Editor tool

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.

»


See All Articles by Columnist
Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles