Miladin Modrakovic's Blog: Oraclue

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:  0×01000221 CHANGE #10 TYP:0 CLS: 1 AFN:4 DBA:0×01000228 OBJ:70510 SCN:0×0000.00150bb5 SEQ:  1 OP:11.2
KTB Redo
op: 0×01  ver: 0×01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0×0005.011.0000030a    uba: 0x00c08d33.01ce.11
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0×00000000  bdba: 0×01000228  hdba: 0×01000223
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0×0) size/delt: 25
fb: –H-FL– lb: 0×1  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:0×0000.001508e5 SEQ:  1 OP:5.2
ktudh redo: slt: 0×0011 sqn: 0x0000030a flg: 0×0012 siz: 112 fbi: 0
            uba: 0x00c08d33.01ce.11    pxid:  0×0000.000.00000000
CHANGE #12 TYP:0 CLS:26 AFN:3 DBA:0x00c08d33 OBJ:4294967295 SCN:0×0000.001508e4 SEQ:  1 OP:5.1
ktudb redo: siz: 112 spc: 6030 flg: 0×0012 seq: 0x01ce rec: 0×11
            xid:  0×0005.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: 0×3  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:0×0000.00151154 SEQ:  1 OP:5.2
ktudh redo: slt: 0x000c sqn: 0×00000359 flg: 0×0012 siz: 136 fbi: 0
            uba: 0x00c08f3e.01bb.1c    pxid:  0×0000.000.00000000
CHANGE #3 TYP:0 CLS:35 AFN:3 DBA:0x00c00099 OBJ:4294967295 SCN:0×0000.0015115d SEQ:  1 OP:5.4
ktucm redo: slt: 0x000c sqn: 0×00000359 srt: 0 sta: 9 flg: 0×2 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:0×0000.00151153 SEQ:  2 OP:5.1
ktudb redo: siz: 136 spc: 4912 flg: 0×0012 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.