Oracle 10g Transparent Encryption: Not So Encrypted

Jun 15, 2007 / By Christo Kutrovsky

Tags:

I have been asked by a client to try out Oracle 10g’s transparent encryption. I’ve created the wallet, set it up into the sqlnet.ora file, and opened it. I initialized the certificate, created a test table, and encrypted a column with it.

Maybe you’ve done the same, and thought, “now my data is safe and encrypted”.

Are you sure? Did you check?

I did. And here’s what I found.

I am not going to cover all the details into creating the wallet and certificate. You can check the Oracle manual for that part. Mine are set. And here’s my test case.

We start by creating the simplest table:

create table secret_data (
id varchar2(20), 
secret_text varchar2(100)
);
insert into secret_data values ('visible id AAA', 'This is my secret text that I want to hide');
commit;

So far so good. This is a simple table, so if you look in the datafiles (using block dump) you are going to see the cleartext of my secret text.

First, we obtain the file and block numbers for the row in question:

SQL> select dbms_rowid.ROWID_RELATIVE_FNO (rowid) f, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) b, t.* fro
m secret_data t;

         F          B ID
---------- ---------- --------------------
SECRET_TEXT
--------------------------------------------------------------------------------
         5        646 visible id AAA
This is my secret text that I want to hide

Next, we dump the block. NOTE: you should not do this on your production servers, although it’s generally safe to do so.

SQL> alter system dump datafile 5 block 646;

System altered

Then, we look in the udump directory and find the trace file we generated. I’ve extracted only the section we are interested in. Note this is from Oracle 10.2.0.3.

Start dump data blocks tsn: 5 file#: 5 minblk 646 maxblk 646
buffer tsn: 5 rdba: 0x01400286 (5/646)
scn: 0x0000.0023063c seq: 0x01 flg: 0x02 tail: 0x063c0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000006682800 to 0x0000000006684800
006682800 0000A206 01400286 0023063C 02010000  [......@.<.#.....]
006682810 00000000 00000001 0000C12F 0023063B  [......../...;.#.]
006682820 00000000 00320002 01400281 000F0009  [......2...@.....]
006682830 0000035B 008007D7 000F019E 00002001  [[............ ..]
006682840 0023063C 00000000 00000000 00000000  [<.#.............]
006682850 00000000 00000000 00000000 00000000  [................]
006682860 00000000 00010100 0014FFFF 1F471F5B  [............[.G.]
006682870 00001F47 1F5B0001 00000000 00000000  [G.....[.........]
006682880 00000000 00000000 00000000 00000000  [................]
        Repeat 498 times
0066847B0 00000000 00000000 00000000 2C000000  [...............,]
0066847C0 760E0201 62697369 6920656C 41412064  [...visible id AA]
0066847D0 68542A41 69207369 796D2073 63657320  [A*This is my sec]
0066847E0 20746572 74786574 61687420 20492074  [ret text that I ]
0066847F0 746E6177 206F7420 65646968 063C0601  [want to hide..<.]
Block header dump:  0x01400286
 Object id on Block? Y
 seg/obj: 0xc12f  csc: 0x00.23063b  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1400281 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Notice how the cleartext is visible and quite readable. So far so good, this is what we expected. Now let’s encrypt the table:

SQL> alter table secret_data modify (secret_text encrypt);

Table altered

SQL> alter system checkpoint;

System altered

Okay, done. The data is all safe. Or is it? Let’s look at the block dump again:

Start dump data blocks tsn: 5 file#: 5 minblk 646 maxblk 646
buffer tsn: 5 rdba: 0x01400286 (5/646)
scn: 0x0000.002306bb seq: 0x01 flg: 0x06 tail: 0x06bb0601
frmt: 0x02 chkval: 0x2473 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000006682800 to 0x0000000006684800
006682800 0000A206 01400286 002306BB 06010000  [......@...#.....]
006682810 00002473 00000001 0000C12F 002306B6  [s$....../.....#.]
006682820 00000000 00320002 01400281 000F0009  [......2...@.....]
006682830 0000035B 008007D7 000F019E 00008000  [[...............]
006682840 0023063C 0022000A 00000354 0080015F  [<.#...".T..._...]
006682850 000801DA 00002001 002306BB 00000000  [..... ....#.....]
006682860 00000000 00010100 0014FFFF 1F1D1EF4  [................]
006682870 00001F1D 1EF40001 00000000 00000000  [................]
006682880 00000000 00000000 00000000 00000000  [................]
        Repeat 492 times
006684750 00000000 00000000 0E02022C 69736976  [........,...visi]
006684760 20656C62 41206469 B2544141 275ECE93  [ble id AAAT...^']
006684770 9530D2FF 4506BF40 C9D2E79E AB5DBEA4  [..0.@..E......].]
006684780 32DB9FE4 98009E4D 07B6FB97 C7D4E19C  [...2M...........]
006684790 042763AB 8DA0EA2D C31CCD1B A8488E73  [.c'.-.......s.H.]
0066847A0 35CA50A8 4BCF2067 7C94E6CA 6A95065C  [.P.5g .K...|\..j]
0066847B0 41EFB8C4 A2DDEB7A 1F61B969 2CAA6ECC  [...Az...i.a..n.,]
0066847C0 760E0200 62697369 6920656C 41412064  [...visible id AA]
0066847D0 68542A41 69207369 796D2073 63657320  [A*This is my sec]
0066847E0 20746572 74786574 61687420 20492074  [ret text that I ]
0066847F0 746E6177 206F7420 65646968 06BB0601  [want to hide....]
Block header dump:  0x01400286
 Object id on Block? Y
 seg/obj: 0xc12f  csc: 0x00.2306b6  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1400281 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Notice the following:

  • There’s less “emptiness” in the block. The number of all zero lines is reduced from 498 to 492. So essentially we have more data into the block.
  • The old cleartext values are still visible at the exact same offsets.
  • There’s new data just in front, which has the same “visible id AAA” text followed by some encrypted text.

What happened? Oracle “updated” the row with encrypted data by adding a new row to the block, and marking the old row as unused.

Why is the old data still there? It’s an optimization in Oracle. There is no need to zero out that data. We can simply use a different place inside the block. The old data can remain unchanged. This saves CPU cycles in zeroing out data, which is not required. However it has the side-effect of leaving “history” behind.

How can you solve this problem? Well you have to move the table — this will defragment and re-initialize all its blocks.

alter table secret_data move;
alter system dump datafile 5 block 652;

Looking at the block now:

Start dump data blocks tsn: 5 file#: 5 minblk 652 maxblk 652
buffer tsn: 5 rdba: 0x0140028c (5/652)
scn: 0x0000.002307da seq: 0x01 flg: 0x00 tail: 0x07da0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000006682800 to 0x0000000006684800
006682800 0000A206 0140028C 002307DA 00010000  [......@...#.....]
006682810 00000000 00000001 0000C130 002307DA  [........0.....#.]
006682820 00000000 00320003 01400289 00210009  [......2...@...!.]
006682830 0000035A 00000000 00000000 00008000  [Z...............]
006682840 002307D1 00000000 00000000 00000000  [..#.............]
006682850 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
006682870 00000000 00000000 00000000 00010100  [................]
006682880 0014FFFF 1F051F19 00001F05 1F190001  [................]
006682890 00000000 00000000 00000000 00000000  [................]
        Repeat 495 times
006684790 00000000 02002C00 7369760E 656C6269  [.....,...visible]
0066847A0 20646920 54414141 5ECE93B2 30D2FF27  [ id AAAT...^'..0]
0066847B0 06BF4095 D2E79E45 5DBEA4C9 DB9FE4AB  [.@..E......]....]
0066847C0 009E4D32 B6FB9798 D4E19C07 2763ABC7  [2M............c']
0066847D0 A0EA2D04 1CCD1B8D 488E73C3 CA50A8A8  [.-.......s.H..P.]
0066847E0 CF206735 94E6CA4B 95065C7C EFB8C46A  [5g .K...|\..j...]
0066847F0 DDEB7A41 61B969A2 AA6ECC1F 07DA0601  [Az...i.a..n.....]
Block header dump:  0x0140028c
 Object id on Block? Y
 seg/obj: 0xc130  csc: 0x00.2307da  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1400289 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Notice how there is no cleartext at all. However, the old block location still has the cleartext and the encrypted data in place.

The only true way to remove the cleartext data is:

  • create a new empty tablespace
  • move all objects from the current tablespace to the new one
  • drop the tablespace from the database (without removing the file)
  • use dd to zero out the old file
  • remove the file

Of course, there are other more dangerous ways to do this, such as zeroing out sections of the datafile after the move, but you risk corrupting your database or losing Oracle’s support of that database.

Note that it’s not only Oracle Encryption that suffers from this problem. Any external encryption will also have a section where it will update column data, and the old cleartext will remain somewhere in the datafiles.

10 Responses to “Oracle 10g Transparent Encryption: Not So Encrypted”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>