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 [[email protected]<.#.....] 006682810 00000000 00000001 0000C12F 0023063B [......../...;.#.] 006682820 00000000 00320002 01400281 000F0009 [[email protected]] 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 [[email protected]#.....] 006682810 00002473 00000001 0000C12F 002306B6 [s$....../.....#.] 006682820 00000000 00320002 01400281 000F0009 [[email protected]] 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 [[email protected]].] 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 [[email protected]#.....] 006682810 00000000 00000001 0000C130 002307DA [........0.....#.] 006682820 00000000 00320003 01400289 00210009 [[email protected]!.] 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 [[email protected]]....] 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
ddto 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.
11 Comments. Leave new
LOL. Good catch Christo and “well done” Oracle.
Wow!
Good to know! Thanks.
By the way I’m staring into your eyes on page 40 of Oracle mag as I type this.
And we a lucky to see those eyes for real…. every day! ;-)
Wow – great post, thanks!
What happens if the block was full to begin with?
Hi Christo, thanks for making clear that this is not only Oracle’s problem; it is known and a workaround is documented here:
https://tinyurl.com/388ymx
Thanks, Hein.
Dominic,
It depends how full the block is. If the block was 100% full (or close) then your row will be migrated, leaving the cleartext behind.
Great article.
We offer a transparent easy-to-use encryption tool for the Oracle RDBMS called the Encryption Wizard for Oracle.
Feel free to review this offering on your blog:
https://www.relationalwizards.com/html/database_encryption.html
– RDC
What’s the workaround ?
Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.