Data Block Dump in Oracle – part 4 – vs. Transparent Data Encyption
Now, let us look into Transparent Data Encryption in 11g implemention using data block dumps.
Transparent Data Encryption (TDE) can be used to encrypt table columns, or tablespaces (only in 11g) without requiring database users change their access code. In short, data in tables/indexes are stored in an encrypted format; when queried, Oracle will automatically decrypt these encrypted data and present to the users. Once setup, encrypt and decrypt happen on the fly, transparently.
Here, we study a few cases to answer these questions.
1) If I have a table on a not-encrypted tablespace and I encrypt its column, will all data in the column be safe now?
2) If I have a table and I move it to a encrypted tablespace, will all data in the column be safe now?
3) When I generate redo logs or archive logs, are they encrypted?
In the case 1, we see the answer is a NO to question1.
CREATE TABLE DUMP_EXAMPLE (
COL1 VARCHAR2(2000),
COL2 NUMBER,
COL3 TIMESTAMP) TABLESPACE USERS;
INSERT INTO DUMP_EXAMPLE VALUES(‘CREDIT CARD NUMBER 1′,12345,SYSTIMESTAMP);
INSERT INTO DUMP_EXAMPLE VALUES(‘CREDIT CARD NUMBER 2′,22345,SYSTIMESTAMP+1);
INSERT INTO DUMP_EXAMPLE VALUES(‘CREDIT CARD NUMBER 3′,32345,SYSTIMESTAMP+2);
COMMIT;
ALTER TABLE DUMP_EXAMPLE MODIFY (COL1 ENCRYPT);
SELECT TABLE_NAME, COLUMN_NAME, ENCRYPTION_ALG FROM DBA_ENCRYPTED_COLUMNS; — verify that the col1 is encrypted now.
DUMP_EXAMPLE COL1 AES 192 bits key
Now, dump the data block.
SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID), DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FROM DUMP_EXAMPLE;
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 478;
27039F80 2C01D801 36012F02 03012C02 45524314 [...,./.6.,...CRE]
27039F90 20544944 44524143 4D554E20 20524542 [DIT CARD NUMBER ]
27039FA0 04C30433 78072E18 0110076D 012C3309 [3......xm....3,.]
27039FB0 52431403 54494445 52414320 554E2044 [..CREDIT CARD NU]
27039FC0 5245424D C3043220 072E1803 0F076D78 [MBER 2......xm..]
27039FD0 2C330901 43140301 49444552 41432054 [..3,...CREDIT CA]
27039FE0 4E204452 45424D55 04312052 2E1802C3 [RD NUMBER 1.....]
27039FF0 076D780B 3309010E 40B9561E C78E0605 [.xm....3.V.@....]
block_row_dump:
tab 0, row 0, @0x1f6f
tl: 41 fb: –H-FL– lb: 0×1 cc: 3
col 0: [20] 43 52 45 44 49 54 20 43 41 52 44 20 4e 55 4d 42 45 52 20 31
col 1: [ 4] c3 02 18 2e
col 2: [11] 78 6d 07 0e 01 09 33 1e 56 b9 40
tab 0, row 1, @0x1f4a
tl: 37 fb: –H-FL– lb: 0×1 cc: 3
col 0: [20] 43 52 45 44 49 54 20 43 41 52 44 20 4e 55 4d 42 45 52 20 32
col 1: [ 4] c3 03 18 2e
col 2: [ 7] 78 6d 07 0f 01 09 33
tab 0, row 2, @0x1f25
tl: 37 fb: –H-FL– lb: 0×1 cc: 3
col 0: [20] 43 52 45 44 49 54 20 43 41 52 44 20 4e 55 4d 42 45 52 20 33
col 1: [ 4] c3 04 18 2e
col 2: [ 7] 78 6d 07 10 01 09 33
end_of_block_dump
As you can see, the column is marked as encrypted, but the existing data are not encrypted. That would become an issue if you are not aware of this implementation.
What will happen to the new records inserted?
INSERT INTO DUMP_EXAMPLE VALUES(‘CREDIT CARD NUMBER 4′,42345,SYSTIMESTAMP+3);
COMMIT;
DBE2130 4403022C 4114097B 486A82F2 98C4247C [,..D{..A..jH|$..]
DBE2140 21F6778B 511B1456 B544614C AEA2515F [.w.!V..QLaD._Q..]
DBE2150 10CB765B 96D17F19 58D77344 B0ECD329 [[v......Ds.X)...]
DBE2160 0DBF64F3 D2124302 680A6666 487D69E2 [.d...C..ff.h.i}H]
DBE2170 8030E715 567A3D96 1802C304 6D780B2E [..0..=zV......xm]
DBE2180 09010E07 B9561E33 03002C40 45524314 [....3.V.@,...CRE]
DBE2190 20544944 44524143 4D554E20 20524542 [DIT CARD NUMBER ]
DBE21A0 04C30433 78072E18 0110076D 002C3309 [3......xm....3,.]
DBE21B0 52431403 54494445 52414320 554E2044 [..CREDIT CARD NU]
DBE21C0 5245424D C3043220 072E1803 0F076D78 [MBER 2......xm..]
DBE21D0 2C330901 43140300 49444552 41432054 [..3,...CREDIT CA]
DBE21E0 4E204452 45424D55 04312052 2E1802C3 [RD NUMBER 1.....]
DBE21F0 076D780B 3309010E 40B9561E C7960601 [.xm....3.V.@....]
Yes, new records are encrypted.
As you can see, further actions are needed to protect/encrypt old records of a column. To encrypt old records, you can move the table using “alter table”.
(Case 2) If a table is moved to encrypted tablespace, the content will be encrypted. Data block dump shows that they are not directly readable.
CREATE SMALLFILE TABLESPACE “ENCRYPTBS” DATAFILE ‘\Path to directory\ENCRYPTBS’ SIZE 2M AUTOEXTEND ON NEXT 1K MAXSIZE 10M ENCRYPTION USING ‘AES192′ DEFAULT STORAGE(ENCRYPT);
ALTER TABLE DUMP_EXAMPLE MOVE TABLESPACE ENCRYPTBS;
SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID), DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FROM DUMP_EXAMPLE;
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 12;
Block dump from disk:
Encrypted block <10, 29360140> content will not be dumped. Dumping header only.
buffer tsn: 10 rdba: 0x01c0000c (7/12)
scn: 0×0000.0011cde3 seq: 0×02 flg: 0×14 tail: 0xcde30602
frmt: 0×02 chkval: 0x4def type: 0×06=trans data
End dump data blocks tsn: 10 file#: 7 minblk 12 maxblk 12
As you can see the encypted block can not be dumped.
(Case 3) Find out records in redo/archive logs for DMLs on encrypted tables. Here is an example.
Insert a row, redo is generated. Now, find current redo log and dump the redo log.
INSERT INTO DUMP_TDE_EXAMPLE VALUES(‘CREDIT CARD NUMBER 4′,42345,SYSTIMESTAMP+4);
COMMIT;
SELECT L.STATUS, LF.MEMBER FROM V$LOG L JOIN V$LOGFILE LF ON L.GROUP#=LF.GROUP#
WHERE L.STATUS = ‘CURRENT’;
alter system dump logfile ‘logfile_number_location’;
Now, ready to look into the redo dump and find out whether data are encrypted. I first calculate DBA (Data Block Address) of the insert value using data file number and block#.
SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID), DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FROM DUMP_EXAMPLE;
5 472
SELECT DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(5,472) FROM DUAL; — in decimal
SELECT TO_CHAR(DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(5,472), ‘XXXXXXXXX’) FROM DUAL; — in hex
Search dump files for the block address. What I see is data in encrypted format. Like,
INSERT INTO DUMP_EXAMPLE VALUES(‘CREDIT CARD NUMBER 5′,52345,SYSTIMESTAMP+5);
COMMIT;
If unencrypted, the text can be read
CHANGE #1 TYP:0 CLS: 1 AFN:4 DBA:0x010001d7 OBJ:71094 SCN:0×0000.0011a82d SEQ: 1 OP:11.2
KTB Redo
op: 0×01 ver: 0×01
compat bit: 4 (post-11) padding: 0
op: F xid: 0×0004.017.000002fe uba: 0x00c006ca.0187.1a
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0×00000000 bdba: 0x010001d7 hdba: 0x010001d3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0×0) size/delt: 37
fb: –H-FL– lb: 0×1 cc: 3
null: —
col 0: [20] 43 52 45 44 49 54 20 43 41 52 44 20 4e 55 4d 42 45 52 20 35
col 1: [ 4] c3 06 18 2e
col 2: [ 7] 78 6d 07 13 01 37 06
If TDE is enabled, the redo data is encrypted. See the different the col0 values above and below for the same inserted record.
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0×00000000 bdba: 0x01c00010 hdba: 0x01c0000b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0×0) size/delt: 85
fb: –H-FL– lb: 0×1 cc: 3
null: —
col 0: [68]
a4 5d 94 29 07 b6 76 31 d2 10 a8 79 a4 04 4c d3 8f f5 df c7 b0 7d bc 52 95
c6 f7 1b f5 8a da d4 60 3a 7f 3b 7e dc 6a 3d f6 23 e6 6a b2 77 b3 9e 17 8a
58 a9 43 97 45 b4 c2 2c bc bb 1b 63 9c 75 db ea b6 f6
col 1: [ 4] c3 06 18 2e
col 2: [ 7] 78 6d 07 13 01 3a 08
Trackbacks & Pingbacks