Demo code for Ghost Data in Indexes

NOTE: all demo data is fake.

This is the demo code for encrypting data where there is an existing index. We are starting with a table customers_tst that is in the unencrypted tablespace dat.

  1. start with dropping the old test objects.
  2. create two small tablespaces small_idx and dat.
  3. create the customers_tst table as a subset of customers.
  4. create an index on customers_tst(ssn)
  5. alter the table customers_tst to add encryption to ssn and cc_nbr. Because ssn has an index we are not using salt.
  6. We then alter the index to rebuild. Because Oracle marks block as free and does not erase them, the old index still exists.
  7. We check for ghost data in small_indx.dbf and dat.dbf.
  8. We confirm that there is ghost data and then drop the index.
  9. Once we drop the tablespace, we can then turn our attention on shredding the ghost data.
1 DROP TABLE customers_tst; 2 DROP MATERIALIZED VIEW customer_sales; 3 DROP TABLESPACE small_idx INCLUDING CONTENTS; 4 DROP TABLESPACE dat INCLUDING CONTENTS; 5 -- rm /opt/oracle/oradata/DEV/datafile/small_idx.dbf 6 -- rm /opt/oracle/oradata/DEV/datafile/dat.dbf 7 CREATE TABLESPACE small_idx DATAFILE '/opt/oracle/oradata/DEV/datafile/small_idx.dbf' SIZE 10M; 8 CREATE TABLESPACE dat DATAFILE '/opt/oracle/oradata/DEV/datafile/dat.dbf' SIZE 10M; 9 10 -- create a test table from customers. 11 CREATE TABLE customers_tst 12 tablespace dat 13 as (select * 14 from customers 15 where rownum <= 1000); 16 17 -- we are going to build an index on SSN 18 CREATE INDEX customers_ssn_idx ON customers_tst(ssn) TABLESPACE small_idx; 19 20 ALTER TABLE customers_tst MODIFY 21 (ssn encrypt USING 'AES256' NO SALT, 22 cc_nbr encrypt USING 'AES256'); 23 24 -- now lets do an index rebuild and test for ghost data 25 ALTER INDEX customers_ssn_idx REBUILD; 26 27 -- in root container run flush the buffer cache 28 29 -- in shell run strings /opt/oracle/oradata/DEV/datafile/small_idx.dbf 30 -- in shell run strings /opt/oracle/oradata/DEV/datafile/dat.dbf 31 32 SELECT * FROM customers_tst 33 where ssn = '347631761'; 34 35 drop index customers_ssn_idx; 36 37 DROP TABLESPACE small_idx; 38 -- in the shell shread the datafile 39 -- in the shell shred /opt/oracle/oradata/DEV/datafile/small_idx.dbf 40 -- in the shell rm /opt/oracle/oradata/DEV/datafile/small_idx.dbf 41 -- we are going to build an index on region so support FK to regions. 42 create tablespace small_idx datafile '/opt/oracle/oradata/DEV/datafile/small_idx.dbf' size 10M; 43 44 -- a small tablespce to hold a materialized view. 45 CREATE TABLESPACE dat 46 DATAFILE '/opt/oracle/oradata/DEV/datafile/dat.dbf' SIZE 10M; 47 48 -- now lets recreate our test data and encrypt it. 49 50 CREATE TABLE customers_tst 51 tablespace dat 52 as (select * 53 from customers 54 where rownum <= 1000); 55 56 ALTER TABLE customers_tst MODIFY 57 (ssn encrypt USING 'AES256' NO SALT, 58 cc_nbr encrypt USING 'AES256'); 59 60 -- we are going to build an index on SSN 61 CREATE INDEX customers_ssn_idx ON customers_tst(ssn) TABLESPACE small_idx; 62 63 64 CREATE MATERIALIZED VIEW CUSTOMER_SALES 65 TABLESPACE DAT 66 NOCACHE 67 PARALLEL 68 USING INDEX 69 REFRESH 70 START WITH SYSDATE NEXT SYSDATE + 1/24 71 COMPLETE 72 WITH ROWID 73 USING DEFAULT ROLLBACK SEGMENT 74 DISABLE QUERY REWRITE AS 75 SELECT 76 c.fname, 77 c.lname, 78 c.city, 79 c.state, 80 c.zip, 81 c.cc_nbr, -- cc_nbr is sensitive and encrypted. 82 c.ssn, -- ssn is sensitive and encrypted. 83 s.price, 84 s.sales_date, 85 p.name 86 FROM customers_tst c, 87 sales_tst s, 88 products p 89 where c.id = s.cust_id 90 and s.product_id = p.id; 91 92 CREATE INDEX CUSTOMER_SALES_IDX ON CUSTOMER_SALES (SSN) TABLESPACE small_idx; 93 CREATE INDEX CUSTOMER_SALES_IDX2 ON CUSTOMER_SALES (cc_nbr) tablespace small_idx; 94 95 --DROP MATERIALIZED VIEW customer_sales; 96 97 -- lets check for sensitive data in the datafiles. 98 -- in shell strings /opt/oracle/oradata/DEV/datafile/dat.dbf 99 -- in shell strings /opt/oracle/oradata/DEV/datafile/small_idx.dbf 100 DROP MATERIALIZED VIEW customer_sales; 101 DROP TABLESPACE small_idx INCLUDING CONTENTS; 102 DROP TABLESPACE dat INCLUDING CONTENTS; 103 -- in shell shred /opt/oracle/oradata/DEV/datafile/dat.dbf 104 -- in shell shred /opt/oracle/oradata/DEV/datafile/small_idx.dbf 105 -- in shell rm /opt/oracle/oradata/DEV/datafile/dat.dbf 106 -- in shell rm /opt/oracle/oradata/DEV/datafile/small_idx.dbf 107 108 CREATE TABLESPACE small_idx DATAFILE '/opt/oracle/oradata/DEV/datafile/small_idx.dbf' SIZE 10M; 109 CREATE TABLESPACE dat DATAFILE '/opt/oracle/oradata/DEV/datafile/dat.dbf' SIZE 10M; 110 111 112 CREATE TABLE customers_tst 113 (ID, 114 FNAME, 115 LNAME, 116 CITY, 117 STATE, 118 ZIP, 119 DISCOUNT, 120 CC_NBR ENCRYPT USING 'AES256', 121 REGION, 122 SSN ENCRYPT USING 'AES256' NO SALT) 123 TABLESPACE dat 124 AS 125 (select * 126 from customers 127 where rownum <= 1000); 128 129 130 -- we are going to build an index on SSN 131 CREATE INDEX customers_ssn_idx ON customers_tst(ssn) TABLESPACE small_idx; 132 133 134 -- we are going to recreate the materialized view, this time 135 -- we will add the encrypt clause. 136 137 CREATE MATERIALIZED VIEW CUSTOMER_SALES 138 ( fname, 139 lname, 140 city, 141 state, 142 zip, 143 cc_nbr encrypt USING '3DES168', 144 ssn encrypt USING '3DES168' NO SALT, -- because ssn has an index we will not use salt. 145 price, 146 sales_date, 147 product_name ) 148 TABLESPACE DAT 149 AS 150 (SELECT 151 c.fname, 152 c.lname, 153 c.city, 154 c.state, 155 c.zip, 156 c.cc_nbr, 157 c.ssn, 158 s.price, 159 s.sales_date, 160 p.name 161 FROM customers_tst c, 162 sales_tst s, 163 products p 164 WHERE c.ID = s.cust_id 165 and s.product_id = p.id); 166 167 CREATE INDEX CUSTOMER_SALES_IDX ON CUSTOMER_SALES (SSN) tablespace small_idx; 168 CREATE INDEX CUSTOMER_SALES_IDX2 ON CUSTOMER_SALES (ZIP) TABLESPACE small_idx; 169 170 -- in shell strings /opt/oracle/oradata/DEV/datafile/dat.dbf 171 -- in shell strings /opt/oracle/oradata/DEV/datafile/small_idx.dbf

#Oracle #TDE Ghost Data Teaser

Here is a teaser for the Oracle Transparent Data Encryption presentation

We look at having an existing table with existing indexes. A policy comes out that says we need to encrypt SSN and Credit Card Numbers. Once we encrypt the columns and rebuild the indexes, does the unencrytped data in the index get encrypted?

Watch and find out.