Re: Dropping tables/Partitions with Purge... is data really gone?
Date: Mon, 8 Apr 2013 12:58:15 -0700 (PDT)
Message-ID: <1365451095.83105.YahooMailNeo_at_web126102.mail.ne1.yahoo.com>
If it is encrypted, they will only see gibberish. Isn't that the point of encryption? Take a look at this work:
http://lianggang.wordpress.com/2009/07/14/data-block-dump-in-oracle-part-4-vs-transparent-data-encyption/
(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
From: Dba DBA <oracledbaquestions_at_gmail.com> To: oracle-l_at_freelists.org
Sent: Monday, April 8, 2013 3:18 PM
Subject: Re: Dropping tables/Partitions with Purge... is data really gone?
The security people won't know how to dump blocks to the OS. Would they be able to see the data with some kind of scanning tool? I need to use hourly partitions and drop them every hour. I am concerned security may make me use a tablespace for each partition. This will be a serious hassle to manage if I have to do this. btw, I know there will still be encrypted data in the archive logs. This is just about what is in the database files.
On Mon, Apr 8, 2013 at 3:12 PM, rjamya <rjamya_at_gmail.com> wrote:
> I believe purge ensures that table cannot be recovered from recyclebin.
> The blocks are marked as available for reuse after drop, but it doesnt mean
> they are reformatted or immediately reused (based on my very limited
> knowledge).
>
> So, if you dump some of those blocks (if you remember the blocks numbers),
> you *might* be able to see encrypted data but not entirely sure about that.
> Unless you drop the datafile, at the file system level it will appear as
> still full (data) file since extent allocation is handled internally.
>
> Raj
>
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 08 2013 - 21:58:15 CEST