Oraclue

Oracle internals, debugging and undocumented features

Inside Encrypted tablespace 11g

Security is a big topic these days so it’s time to play with new Oracle 11g features.

Apperently encrypted tablespaces has few advantages over TDE (  table level encryption ) .Could not find detail info how all works but got this from google:

“Transparent encryption/decryption takes place during disk input/output
(I/O) and not for every logical access to the data. This leads to improved
performance.”
The encrypted data is protected during operations like JOIN and SORT. This
means that the data is safe when it is moved to temporary tablespaces. Data
in undo and redo logs is also protected.

and decided to play some demo:

First you need hex convertor:

Make lifes easier.Here is one:

http://www.paulschou.com/tools/xlate/

and more info about dissasembling redo log.

http://www.orafaq.com/papers/redolog.pdf

Old, but good paper  written by Graham Thornton .

 Now my demo:

SQL> create table secret (username varchar2(20),password varchar2(20)) tablespace users ;

Table created.

SQL> insert into secret values (‘MYSELF’,’SECUREPASSWORD’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from secret;

USERNAME             PASSWORD
——————– ——————–
MYSELF               SECUREPASSWORD

If  I look at datafile

strings /oracle/demo/data/users01.dbf|grep -i SECUREPASSWORD
SECUREPASSWORD

of course our data will be there exposed.
Now let’s see what’s inside redo log file
strings /oracle/demo/redo/member_a/demoredo1a.log|grep -i SECUREPASSWORD
SECUREPASSWORD

well still there as expected.And finally dump logfile

SQL> alter system dump logfile ‘/oracle/demo/redo/member_a/demoredo1a.log’;

System altered.

and find obj# for a our table:

SQL> select obj# from obj$ where name =’SECRET’;

      OBJ#
———-
     70510
and see our data in bold:
lfdba:  0x01000221 CHANGE #10 TYP:0 CLS: 1 AFN:4 DBA:0x01000228 OBJ:70510 SCN:0x0000.00150bb5 SEQ:  1 OP:11.2
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0005.011.0000030a    uba: 0x00c08d33.01ce.11
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000228  hdba: 0x01000223
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 25
fb: –H-FL– lb: 0x1  cc: 2
null: —
col  0: [ 6]  4d 59 53 45 4c 46  /**  MYSELF ***
col  1: [14]  53 45 43 55 52 45 50 41 53 53 57 4f 52 44  /** SECUREPASSWORD ***/
CHANGE #11 TYP:0 CLS:25 AFN:3 DBA:0x00c00049 OBJ:4294967295 SCN:0x0000.001508e5 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0011 sqn: 0x0000030a flg: 0x0012 siz: 112 fbi: 0
            uba: 0x00c08d33.01ce.11    pxid:  0x0000.000.00000000
CHANGE #12 TYP:0 CLS:26 AFN:3 DBA:0x00c08d33 OBJ:4294967295 SCN:0x0000.001508e4 SEQ:  1 OP:5.1
ktudb redo: siz: 112 spc: 6030 flg: 0x0012 seq: 0x01ce rec: 0x11
            xid:  0x0005.011.0000030a
ktubl redo: slt: 17 rci: 0 opc: 11.1 objn: 70510 objd: 70510 tsn: 4
Undo type:  Regular undo        Begin trans    Last buffer split:  No

Note few field here:  objn –  our obj# from obj$  and also note objd .Both values are 70510.
I will do same proces but this time with encryped tablespace.

Befor I can create a wallet I need to create wallet:
sqlnet.ora:

ENCRYPTION_WALLET_LOCATION=
          (SOURCE=(METHOD=FILE)(METHOD_DATA=
          (DIRECTORY=/oracle/admin/demo/scripts)))
         
mkstore -wrl . -create

sqlplus / as sysdba
create tablespace crypto
datafile ‘/oracle/demo/data/crypto.dbf’ size 100M
ENCRYPTION default storage( encrypt );

alter system set encryption wallet open authenticated by “demos123”;
and finally move table secret to encrypted tablespace:

alter table secret move tablespace crypto;
checking logfile

strings /oracle/demo/redo/member_a/demoredo1a.log|grep -i SECUREPASSWORD
SECUREPASSWORD

Password is still there because this is same redo log and keep old entries.Switch will fix it:
strings /oracle/demo/redo/member_a/demoredo3a.log|grep -i SECUREPASSWORD
No data returned.Good.

again let see logfile dump
fb: –H-FL– lb: 0x3  cc: 2
null: —
col  0: [ 6]  4d 59 53 45 4c 46  /** MYSELF ***/
col  1: [14]  53 45 43 55 52 45 50 41 53 53 57 4f 52 44 /* SECUREPASSWORD **/
CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00099 OBJ:4294967295 SCN:0x0000.00151154 SEQ:  1 OP:5.2
ktudh redo: slt: 0x000c sqn: 0x00000359 flg: 0x0012 siz: 136 fbi: 0
            uba: 0x00c08f3e.01bb.1c    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:35 AFN:3 DBA:0x00c00099 OBJ:4294967295 SCN:0x0000.0015115d SEQ:  1 OP:5.4
ktucm redo: slt: 0x000c sqn: 0x00000359 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c08f3e.01bb.1c ext: 36 spc: 4774 fbi: 0
CHANGE #4 TYP:0 CLS:36 AFN:3 DBA:0x00c08f3e OBJ:4294967295 SCN:0x0000.00151153 SEQ:  2 OP:5.1
ktudb redo: siz: 136 spc: 4912 flg: 0x0012 seq: 0x01bb rec: 0x1c
            xid:  0x000a.00c.00000359
ktubl redo: slt: 12 rci: 0 opc: 11.1 objn: 70510 objd: 70511 tsn: 14
Undo type:  Regular undo        Begin trans    Last buffer split:  No

objd:70511
Data are still there UNENCRYPED.

checking datafile

strings /oracle/demo/data/crypto.dbf|grep -i SECUREPASSWORD

Nope.Good no data exposed.Encryption is taking place on “fly”  , when written do disk.

Advertisements

3 responses to “Inside Encrypted tablespace 11g

  1. Michael O'Neill December 12, 2008 at 4:00 am

    The encryption at the TS level is nice because it brings index usage into execution plans where they may not have with TDE.

  2. oraclue December 14, 2008 at 5:01 am

    Good point Michael.I saw few posts on internet covering differences and performance improvements using encrypted tablespaces intead of TDE.When I get time I will do some benchmark and post it.

    Miladin

  3. Martin Berger December 16, 2008 at 10:10 pm

    hmm, does this mean the data is unencrypted in the buffer cache? and maybe also in the pga? (ok, it MUST be unencrypted somewhere, I fear; and this must be the server-side in this case?)
    This leaves all attack vectors against memory structures open (ans when you enforce the server-process to go to swap, you have everything on disk also).
    Or am I totally wrong here?

    Nevertheless, great summary! (and I didn’t want to offed)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: