Three Tier Oracle Security in London ~ Paul M. Wright

dbf records previous state of each row

This is QI IMO.

SQL> CONN SCOTT/TIGER
Connected.

SQL> CREATE TABLE TEST(INPUT VARCHAR2(20));
Table created.

SQL> INSERT INTO TEST VALUES(‘FIRSTROW’);
1 row created.

SQL> SELECT * FROM TEST;
INPUT
——————–
FIRSTROW

SQL> UPDATE TEST SET INPUT=’FIRSTROWUPATED’ WHERE INPUT=’FIRSTROW’;
1 row updated.

SQL> SELECT * FROM TEST;
INPUT
——————–
FIRSTROWUPATED

Wait 5 minute and the row is updated in the table with the new value AND the old value is kept.

The DBF file retains the previous state of the row indefinitely.

Then update the single row again:

SQL> UPDATE TEST SET INPUT=’FIRSTROWUPATEDAGAIN’ WHERE INPUT=’FIRSTROWUPATED’;

1 row updated.

Wait another 5 minutes.

Again keeps the previous versions of that row.

Let’s try once more..
SQL> UPDATE TEST SET INPUT=’FIRSTROWUPATEDTHRICE’ WHERE INPUT=’FIRSTROWUPATEDAGAIN’;

1 row updated

SQL> COMMIT;

Wait 5 minutes.

This is interesting as RDBMSs suffer from a lack of previous state in the relational schema itself and in terms of normal tables represented to the user. In actual fact the Oracle datafile IS recording the previous states of rows that are updated. The previous states are persistent in the data file itself. This knowledge is of great use to a forensic investigator who can see the previous state of a row even if there is no redo, flashback or back up. No need for BBED here…but perhaps a dictionary to spell “updated” would come in use :¬)

March 21st, 2007 | Category: Uncategorized | Subscribe to comments | You can skip to the end and leave a response. Pinging is currently not allowed

2 Responses to “dbf records previous state of each row”

1
pawelbarut:
March 22nd, 2007 at 7:50 AM
I think Oracle behaves like this only when block is almost empty. Try to fill up block completely and then try to update rows. In my opinion results will be different.
What happens when field after update is shorter?

Regards, Pawel
2
gamyers:
March 22nd, 2007 at 11:17 AM
I dug around for a presentation (Once around the block) I went to by Connor McDonald.
One of the diagrams indicates that row data gets filled from the end of the block towards the start (with the row directory going the other way). It looks like each time you make the value bigger, it can’t stretch out beyond the end of the block, so it creates it as a new row in the earlier empty space.
Of course another challenge would be to find the old data in the datafile for the undo tablespace.