Common mistake when loading data into an #encrypted database.

Hacker attacking internet

There is a mistake that I’m seeing frequently. Loading a raw data file into an encrypted database then leaving the data file on an unencrypted device.

After loading the data into the database; if you don’t need the data file anymore, you should do a secure delete on the file. If you are going to need the data file again, then move the data file to an encrypted device then do a secure delete on the old data file. Better yet, when you bring the data file down, save it straight to an encrypted device and work from that device.

This is an easy thing to fix.

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.

Oracle Transparent Data Encryption Baseline Practices webinar

I will be giving a webinar on Oracle Transparant Data Encryption Baseline Practices August 27, 2015 at 3PM. Sponsored by @odtug 

Why “Baseline Practices?” well best practices does not seem to be working so we are going to start improving “your game” by setting the baseline and getting you to think how information is secured.

This one-hour presentation includes how to, gotchas (where data can leak in clear text), and baseline practices for implementing Oracle Transparent Data Encryption. We will walk through setting up Oracle Transparent Data Encryption and establish baseline practices to secure your information, explaining where and how to encrypt the data, and where and how data leaks in plain text and HEX format. We will also explore these questions: When sending data to a standby database, when does the data stay encrypted and when can it transfer over the network in clear text? When using Oracle Streams, does data go across the network in clear text? When gathering statistics on encrypted data, where can the data be exposed unencrypted? As we discuss each of the leaks, we will also review how to mitigate the leakage and eliminate ghost data.

Register here: https://attendee.gotowebinar.com/register/7938280806383993602

#Oracle #TDE #dataleak #Histograms

While at #KSCOPE15, I was asked about encrypted data showing up in histograms.  So, I ran a few experiments and learned that encrypted data does indeed leak. I contacted Oracle through an old friend to get their input.

Here is the reply I received.

================================================================

The attack vector here is via SELECT on histogram tables. The protection profile for TDE by design does not address DB users with proper access to such tables. The gotcha here is that many people don’t realize they should control access to STATS tables as well as the tables with sensitive data.

Possible ways to workaround:

1. Configure Database Vault policy so user who tries to query sensitive columns on these views/tables is denied access

2. Do not collect stats on sensitive columns in the first place

===================================================================

Here is my experiment:

1 Test 1) Note, we are putting this into a non-encrypted tablespace, we are going to explicitly encrypt column D1. 2 3 --CREATE A TEST TABLE 4 RLOCKARD@pdev > CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY, d1 VARCHAR2(255)) TABLESPACE not_sensitive; 5 Table created. 6 7 -- ENCRYPT THE DATA 8 RLOCKARD@pdev > alter table t1 modify (d1 encrypt using 'AES256'); 9 Table altered. 10 11 --INSERT SOME TEST DATA 12 RLOCKARD@pdev > insert into t1 (D1) (select 'Encrypt your data' from dual connect by level <= 10); 13 10 rows created. 14 RLOCKARD@pdev > insert into t1 (D1) (select 'Is this encrypted?' from dual connect by level <= 5); 15 5 rows created. 16 RLOCKARD@pdev > insert into t1 (D1) (select 'Practice Secure Computing' from dual connect by level <= 20); 17 20 rows created. 18 RLOCKARD@pdev > commit; 19 Commit complete. 20 21 -- GATHER STATISTICS ALONG WITH HISTOGRAMS. 22 RLOCKARD@pdev > begin 23 dbms_stats.gather_table_stats(null,'T1', method_opt=> 'for all columns size skewonly'); 24 end; 25 / 26 PL/SQL procedure successfully completed. 27 -- THIS LOOKS GOOD 28 RLOCKARD@pdev > select 29 endpoint_number, 30 endpoint_actual_value 31 from dba_tab_histograms 32 where owner = 'RLOCKARD' 33 and table_name = 'T1' 34 and column_name = 'D1'; 35 2 3 4 5 6 7 36 ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALUE 37 --------------- -------------------------------- 38 10 39 15 40 35 41 42 -- HOWEVER, WHEN WE DIG A BIT FURTHER IT'S QUITE EASY TO 43 -- TRANSLATE ENDPOINT_VALUE INTO THE FIRST CHARACTERS OF THE 44 -- DATA THEREBY EXPOSING THE INFORMATION. 45 -- NOTE THIS QUERY IS FROM Jonathan Lewis blog at: https://jonathanlewis.wordpress.com/category/oracle/statistics/histograms/ 46 47 48 RLOCKARD@pdev > ed 49 Wrote file afiedt.buf 50 51 1 select 52 2 endpoint_number, 53 3 endpoint_number - nvl(prev_endpoint,0) frequency, 54 4 hex_val, 55 5 chr(to_number(substr(hex_val, 2,2),'XX')) || 56 6 chr(to_number(substr(hex_val, 4,2),'XX')) || 57 7 chr(to_number(substr(hex_val, 6,2),'XX')) || 58 8 chr(to_number(substr(hex_val, 8,2),'XX')) || 59 9 chr(to_number(substr(hex_val,10,2),'XX')) || 60 10 chr(to_number(substr(hex_val,12,2),'XX')) || 61 11 chr(to_number(substr(hex_val,14,2),'XX')) || 62 12 chr(to_number(substr(hex_val,16,2),'XX')), 63 13 endpoint_actual_value 64 14 from ( 65 15 select 66 16 endpoint_number, 67 17 lag(endpoint_number,1) over( 68 18 order by endpoint_number 69 19 ) prev_endpoint, 70 20 to_char(endpoint_value,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')hex_val, 71 21 endpoint_actual_value 72 22 from 73 23 dba_tab_histograms 74 24 WHERE 75 25 owner = 'RLOCKARD' 76 26 AND table_name = 'T1' 77 27 and column_name = 'D1' 78 28 ) 79 29 order by 80 30* endpoint_number 81 31 / 82 83 ENDPOINT_NUMBER FREQUENCY HEX_VAL CHR(TO_N ENDPOINT_ACTUAL_VALUE 84 --------------- ------------ ------------------------------- -------- -------------------------------- 85 10 10 456E6372797079E93CBEA1A5000000 Encrypye 86 15 5 49732074686967A04440BE12C00000 Is thig 87 35 20 5072616374698217A0D44499800000 Practi? 88 89 3 rows selected. 90 91 92 93 94 TEST 2) 95 Important note: THIS IS ALL PSEUDO DATA, NOTING IS REAL. 96 97 -- the test customers table contains pseudo ssn's and cc numbers for demo purposes. 98 -- reality is, because cc_nbr and ssn are distinct, histograms should not be gathered, 99 -- however a "lazy" DBA may use the 'for all columns size skewonly' method_opt 100 -- therefore, by using the defaults you will get out 254 rows with data that should be encrypted. 101 102 create table t3 as select id, fname, lname, city, state, cc_nbr, ssn from customers; 103 alter table t3 modify (cc_nbr encrypt using 'AES256', SSN encrypt using 'AES256'); 104 105 begin 106 dbms_stats.gather_table_stats(null,'T3', method_opt=> 'for all columns size skewonly'); 107 end; 108 / 109 110 desc t3 111 112 RLOCKARD@pdev > desc t3 113 Name Null? Type 114 ------------------------------------------------------------------------ -------- ------------------------------------------------- 115 ID NOT NULL NUMBER 116 FNAME VARCHAR2(25) 117 LNAME VARCHAR2(25) 118 CITY VARCHAR2(25) 119 STATE VARCHAR2(25) 120 CC_NBR VARCHAR2(16) ENCRYPT 121 SSN VARCHAR2(11) ENCRYPT 122 123 RLOCKARD@pdev > 124 125 126 select 127 endpoint_number, 128 endpoint_actual_value 129 from dba_tab_histograms 130 where owner = 'RLOCKARD' 131 and table_name = 'T3' 132 and column_name = 'SSN'; 133 134 RLOCKARD@pdev > l 135 1 select 136 2 endpoint_number, 137 3 endpoint_actual_value 138 4 from dba_tab_histograms 139 5 where owner = 'RLOCKARD' 140 6 and table_name = 'T3' 141 7* and column_name = 'SSN' 142 RLOCKARD@pdev > / 143 144 ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALUE 145 --------------- -------------------------------- 146 4247 778294598 147 4269 782777484 148 4291 785731383 149 4313 788768328 150 4335 792928354 151 4357 795685465 152 4379 798987731 153 4401 812732627 154 4424 815857391 155 4446 818188243 156 ========SNIP A LOT====== 157 158 RLOCKARD@pdev > SELECT * FROM T3 WHERE SSN='778294598'; 159 160 ID FNAME LNAME CITY STATE CC_NBR 161 ---------- ------------------------- ------------------------- ------------------------- ------------------------- ---------------- 162 SSN 163 ----------- 164 41742 Monica Gaestel Strattanville Pennsylvania 3483712444144721 165 778294598 166 167 168 1 row selected.

Oracle Transparent Data Encryption Performance. Oracle 12.1.0.2 on Linux 5

 

Some performance test on Oracle TDE.  I have three tables t1, t2 and t3. All three tables have identical data and 1.6M rows. I’m going to come back and visit this a bit more later, but want to get some initial performance numbers out there.

1 RLOCKARD@pdev > desc t1 2 Name Null? Type 3 -------------------------------------- -------- ----------------- 4 ID NOT NULL NUMBER 5 NBR NUMBER 6 7 RLOCKARD@pdev > desc t2 8 Name Null? Type 9 -------------------------------------- -------- ----------------- 10 ID NOT NULL NUMBER 11 NBR NUMBER 12 13 RLOCKARD@pdev > desc t3 14 Name Null? Type 15 -------------------------------------- -------- ----------------- 16 ID NOT NULL NUMBER 17 NBR NOT NULL NUMBER ENCRYPT 18 19 TABLE_NAME TABLESPACE_NAME 20 ------------------------- ------------------------------ 21 REGION USERS 22 T3 USERS 23 T1 DAT 24 PRODUCTS BIG_SENSITIVE 25 T2 BIG_SENSITIVE 26 FNAMES USERS 27 ZIP USERS 28 STORES USERS 29 NETWORKS USERS 30 CUSTOMERS USERS 31 LNAMES USERS 32 ZIP_CODES USERS 33 SALES USERS 34 REGIONS USERS 35 STORE_USERS USERS 36 37 15 rows selected. 38 39 RLOCKARD@pdev > 40

Table T1 resides in the DAT tablespace that is not encrypted.  Selecting sum(nbr) from t1 where nbr between 40 and 45 executed in .27 seconds.  here there are a bunch of direct path reads followed by 188182 microseconds

1 RLOCKARD@pdev > @no_encrypt 2 RLOCKARD@pdev > set timing on 3 RLOCKARD@pdev > alter session set timed_statistics = true; 4 5 Session altered. 6 7 Elapsed: 00:00:00.03 8 RLOCKARD@pdev > alter session set statistics_level = all ; 9 10 Session altered. 11 12 Elapsed: 00:00:00.05 13 RLOCKARD@pdev > alter session set sql_trace = true ; 14 15 Session altered. 16 17 Elapsed: 00:00:00.06 18 RLOCKARD@pdev > alter session set max_dump_file_size=unlimited; 19 20 Session altered. 21 22 Elapsed: 00:00:00.02 23 RLOCKARD@pdev > alter session set tracefile_identifier = owi_trace; 24 25 Session altered. 26 27 Elapsed: 00:00:00.00 28 RLOCKARD@pdev > 29 RLOCKARD@pdev > BEGIN 30 2 dbms_monitor.session_trace_enable (waits=>true); 31 3 END; 32 4 / 33 34 PL/SQL procedure successfully completed. 35 36 Elapsed: 00:00:00.27 37 RLOCKARD@pdev > select sum(nbr) from t1 where nbr between 40 and 45; 38 39 SUM(NBR) 40 ---------- 41 4055760 42 43 1 row selected. 44 45 Elapsed: 00:00:00.27 46 RLOCKARD@pdev > 47 RLOCKARD@pdev > BEGIN 48 2 dbms_monitor.session_trace_disable; 49 3 END; 50 4 / 51 52 PL/SQL procedure successfully completed. 53 54 Elapsed: 00:00:00.01 55

1 WAIT #46912559622408: nam='direct path read' ela= 42 file number=78 first dba=3144 block cnt=8 obj#=92336 tim=1436288730005400 2 WAIT #46912559622408: nam='direct path read' ela= 45 file number=78 first dba=3152 block cnt=8 obj#=92336 tim=1436288730005873 3 WAIT #46912559622408: nam='direct path read' ela= 43 file number=78 first dba=3160 block cnt=8 obj#=92336 tim=1436288730006338 4 WAIT #46912559622408: nam='direct path read' ela= 44 file number=78 first dba=3168 block cnt=8 obj#=92336 tim=1436288730006835 5 WAIT #46912559622408: nam='direct path read' ela= 41 file number=78 first dba=3176 block cnt=8 obj#=92336 tim=1436288730007325 6 WAIT #46912559622408: nam='direct path read' ela= 45 file number=78 first dba=3184 block cnt=8 obj#=92336 tim=1436288730007807 7 WAIT #46912559622408: nam='direct path read' ela= 42 file number=78 first dba=3192 block cnt=8 obj#=92336 tim=1436288730008267 8 WAIT #46912559622408: nam='direct path read' ela= 38 file number=78 first dba=3202 block cnt=6 obj#=92336 tim=1436288730008762 9 WAIT #46912559622408: nam='direct path read' ela= 43 file number=78 first dba=3208 block cnt=8 obj#=92336 tim=1436288730009241 10 WAIT #46912559622408: nam='direct path read' ela= 45 file number=78 first dba=3216 block cnt=8 obj#=92336 tim=1436288730009633 11 WAIT #46912559622408: nam='direct path read' ela= 41 file number=78 first dba=3224 block cnt=8 obj#=92336 tim=1436288730010093 12 WAIT #46912559622408: nam='direct path read' ela= 73 file number=78 first dba=3232 block cnt=8 obj#=92336 tim=1436288730010585 13 WAIT #46912559622408: nam='direct path read' ela= 44 file number=78 first dba=3240 block cnt=8 obj#=92336 tim=1436288730011082 14 WAIT #46912559622408: nam='direct path read' ela= 43 file number=78 first dba=3248 block cnt=8 obj#=92336 tim=1436288730011545 15 WAIT #46912559622408: nam='direct path read' ela= 44 file number=78 first dba=3256 block cnt=8 obj#=92336 tim=1436288730012024 16 WAIT #46912559622408: nam='direct path read' ela= 45 file number=78 first dba=3296 block cnt=8 obj#=92336 tim=1436288730012491 17 WAIT #46912559622408: nam='direct path read' ela= 44 file number=78 first dba=3304 block cnt=8 obj#=92336 tim=1436288730012992 18 WAIT #46912559622408: nam='direct path read' ela= 41 file number=78 first dba=3312 block cnt=8 obj#=92336 tim=1436288730013200 19 WAIT #46912559622408: nam='direct path read' ela= 40 file number=78 first dba=3320 block cnt=8 obj#=92336 tim=1436288730013423 20 FETCH #46912559622408:c=0,e=188182,p=3113,cr=6199,cu=0,mis=0,r=1,dep=0,og=1,plh=3724264953,tim=1436288730013681 21 STAT #46912559622408 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=6199 pr=3113 pw=0 time=188179 us)' 22 STAT #46912559622408 id=2 cnt=95344 pid=1 pos=1 obj=92336 op='TABLE ACCESS FULL T1 (cr=6199 pr=3113 pw=0 time=180339 us cost=827 size=95049 card=31683)' 23 WAIT #46912559622408: nam='SQL*Net message from client' ela= 317 driver id=1413697536 #bytes=1 p3=0 obj#=92336 tim=1436288730014177 24 FETCH #46912559622408:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3724264953,tim=1436288730014283 25 WAIT #46912559622408: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=92336 tim=1436288730014321 26 WAIT #46912559622408: nam='SQL*Net message from client' ela= 760 driver id=1413697536 #bytes=1 p3=0 obj#=92336 tim=1436288730015101 27 CLOSE #46912559622408:c=0,e=18,dep=0,type=0,tim=1436288730015216 28

Now table T2 resides in an encrypted tablespace.  Lets run that same query and see what happens.  Okay, the execution time went up to 1.67 seconds.  Here on the FETCH line we see 1567559 microseconds of CPU time to decrypt the data after a bunch of direct path reads.

1 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=4928 block cnt=8 obj#=92341 tim=1436288983345072 2 WAIT #46912560638840: nam='direct path read' ela= 44 file number=77 first dba=4936 block cnt=8 obj#=92341 tim=1436288983349077 3 WAIT #46912560638840: nam='direct path read' ela= 47 file number=77 first dba=4944 block cnt=8 obj#=92341 tim=1436288983353083 4 WAIT #46912560638840: nam='direct path read' ela= 49 file number=77 first dba=4952 block cnt=8 obj#=92341 tim=1436288983357078 5 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=4960 block cnt=8 obj#=92341 tim=1436288983361086 6 WAIT #46912560638840: nam='direct path read' ela= 50 file number=77 first dba=4968 block cnt=8 obj#=92341 tim=1436288983365107 7 WAIT #46912560638840: nam='direct path read' ela= 52 file number=77 first dba=4976 block cnt=8 obj#=92341 tim=1436288983369245 8 WAIT #46912560638840: nam='direct path read' ela= 47 file number=77 first dba=4984 block cnt=8 obj#=92341 tim=1436288983373257 9 WAIT #46912560638840: nam='direct path read' ela= 39 file number=77 first dba=4994 block cnt=6 obj#=92341 tim=1436288983377270 10 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=5000 block cnt=8 obj#=92341 tim=1436288983381286 11 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=5008 block cnt=8 obj#=92341 tim=1436288983384340 12 WAIT #46912560638840: nam='direct path read' ela= 48 file number=77 first dba=5016 block cnt=8 obj#=92341 tim=1436288983388373 13 WAIT #46912560638840: nam='direct path read' ela= 49 file number=77 first dba=5024 block cnt=8 obj#=92341 tim=1436288983392414 14 WAIT #46912560638840: nam='direct path read' ela= 47 file number=77 first dba=5032 block cnt=8 obj#=92341 tim=1436288983396450 15 WAIT #46912560638840: nam='direct path read' ela= 47 file number=77 first dba=5040 block cnt=8 obj#=92341 tim=1436288983400460 16 WAIT #46912560638840: nam='direct path read' ela= 46 file number=77 first dba=5048 block cnt=8 obj#=92341 tim=1436288983404468 17 WAIT #46912560638840: nam='direct path read' ela= 45 file number=77 first dba=5056 block cnt=8 obj#=92341 tim=1436288983408470 18 WAIT #46912560638840: nam='direct path read' ela= 49 file number=77 first dba=5064 block cnt=8 obj#=92341 tim=1436288983412478 19 WAIT #46912560638840: nam='direct path read' ela= 17 file number=77 first dba=5072 block cnt=1 obj#=92341 tim=1436288983416457 20 FETCH #46912560638840:c=0,e=1567559,p=3095,cr=3099,cu=0,mis=0,r=1,dep=0,og=1,plh=3321871023,tim=1436288983420903 21 STAT #46912560638840 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3099 pr=3095 pw=0 time=1567554 us)' 22 STAT #46912560638840 id=2 cnt=95344 pid=1 pos=1 obj=92341 op='TABLE ACCESS FULL T2 (cr=3099 pr=3095 pw=0 time=1559639 us cost=827 size=95049 card=31683)' 23 WAIT #46912560638840: nam='SQL*Net message from client' ela= 320 driver id=1413697536 #bytes=1 p3=0 obj#=92341 tim=1436288983421412 24 FETCH #46912560638840:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3321871023,tim=1436288983421496 25 WAIT #46912560638840: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=92341 tim=1436288983421533 26 WAIT #46912560638840: nam='SQL*Net message from client' ela= 648 driver id=1413697536 #bytes=1 p3=0 obj#=92341 tim=1436288983422201 27 CLOSE #46912560638840:c=0,e=18,dep=0,type=0,tim=1436288983422305 28

1 RLOCKARD@pdev > @tablespace_encryption.sql 2 RLOCKARD@pdev > set timing on 3 RLOCKARD@pdev > 4 RLOCKARD@pdev > alter session set timed_statistics = true; 5 6 Session altered. 7 8 Elapsed: 00:00:00.00 9 RLOCKARD@pdev > alter session set statistics_level = all ; 10 11 Session altered. 12 13 Elapsed: 00:00:00.00 14 RLOCKARD@pdev > alter session set sql_trace = true ; 15 16 Session altered. 17 18 Elapsed: 00:00:00.01 19 RLOCKARD@pdev > alter session set max_dump_file_size=unlimited; 20 21 Session altered. 22 23 Elapsed: 00:00:00.00 24 RLOCKARD@pdev > alter session set tracefile_identifier = owi_trace; 25 26 Session altered. 27 28 Elapsed: 00:00:00.00 29 RLOCKARD@pdev > 30 RLOCKARD@pdev > BEGIN 31 2 dbms_monitor.session_trace_enable (waits=>true); 32 3 END; 33 4 / 34 35 PL/SQL procedure successfully completed. 36 37 Elapsed: 00:00:00.00 38 RLOCKARD@pdev > select sum(nbr) from t2 where nbr between 40 and 45; 39 40 SUM(NBR) 41 ---------- 42 4055760 43 44 1 row selected. 45 46 Elapsed: 00:00:01.67 47 RLOCKARD@pdev > 48 RLOCKARD@pdev > BEGIN 49 2 dbms_monitor.session_trace_disable; 50 3 END; 51 4 / 52 53 PL/SQL procedure successfully completed. 54 55 Elapsed: 00:00:00.00 56 RLOCKARD@pdev > 57

Table T3 resides in the DAT tablespace and has the nbr column encrypted.  Execution time 19.86 seconds.

 

1 RLOCKARD@pdev > @column_encryption 2 RLOCKARD@pdev > set timing on 3 RLOCKARD@pdev > alter session set timed_statistics = true; 4 5 Session altered. 6 7 Elapsed: 00:00:00.00 8 RLOCKARD@pdev > alter session set statistics_level = all ; 9 10 Session altered. 11 12 Elapsed: 00:00:00.00 13 RLOCKARD@pdev > alter session set sql_trace = true ; 14 15 Session altered. 16 17 Elapsed: 00:00:00.01 18 RLOCKARD@pdev > alter session set max_dump_file_size=unlimited; 19 20 Session altered. 21 22 Elapsed: 00:00:00.00 23 RLOCKARD@pdev > alter session set tracefile_identifier = owi_trace; 24 25 Session altered. 26 27 Elapsed: 00:00:00.00 28 RLOCKARD@pdev > 29 RLOCKARD@pdev > BEGIN 30 2 dbms_monitor.session_trace_enable (waits=>true); 31 3 END; 32 4 / 33 34 PL/SQL procedure successfully completed. 35 36 Elapsed: 00:00:00.00 37 RLOCKARD@pdev > select sum(nbr) from t3 where nbr between 40 and 45; 38 39 SUM(NBR) 40 ---------- 41 4055760 42 43 1 row selected. 44 45 Elapsed: 00:00:19.86 46 RLOCKARD@pdev > 47 RLOCKARD@pdev > BEGIN 48 2 dbms_monitor.session_trace_disable; 49 3 END; 50 4 / 51 52 PL/SQL procedure successfully completed. 53 54 Elapsed: 00:00:00.00 55 RLOCKARD@pdev > 56 RLOCKARD@pdev > 57

Looking at the 10046 trace file, we see a large number of “direct path read’ then FETCH c=0, e=19848382.  c is the CPU used and e is the elapsed time in micro seconds.  So, after reading all the data into the SGA, the CPU spent 19.848382 seconds to decrypt the data.

1 WAIT #46912556873704: nam='direct path read' ela= 47 file number=10 first dba=17536 block cnt=8 obj#=92434 tim=1436288197076658 2 WAIT #46912556873704: nam='direct path read' ela= 46 file number=10 first dba=17544 block cnt=8 obj#=92434 tim=1436288197087637 3 WAIT #46912556873704: nam='direct path read' ela= 45 file number=10 first dba=17552 block cnt=8 obj#=92434 tim=1436288197098738 4 WAIT #46912556873704: nam='direct path read' ela= 43 file number=10 first dba=17560 block cnt=8 obj#=92434 tim=1436288197109734 5 WAIT #46912556873704: nam='direct path read' ela= 48 file number=10 first dba=17568 block cnt=8 obj#=92434 tim=1436288197120785 6 WAIT #46912556873704: nam='direct path read' ela= 45 file number=10 first dba=17576 block cnt=8 obj#=92434 tim=1436288197131809 7 WAIT #46912556873704: nam='direct path read' ela= 45 file number=10 first dba=17584 block cnt=8 obj#=92434 tim=1436288197142990 8 WAIT #46912556873704: nam='direct path read' ela= 45 file number=10 first dba=17592 block cnt=8 obj#=92434 tim=1436288197154216 9 WAIT #46912556873704: nam='direct path read' ela= 67 file number=10 first dba=17600 block cnt=8 obj#=92434 tim=1436288197167921 10 WAIT #46912556873704: nam='direct path read' ela= 59 file number=10 first dba=17608 block cnt=8 obj#=92434 tim=1436288197179630 11 WAIT #46912556873704: nam='direct path read' ela= 46 file number=10 first dba=17616 block cnt=8 obj#=92434 tim=1436288197190774 12 WAIT #46912556873704: nam='direct path read' ela= 56 file number=10 first dba=17624 block cnt=8 obj#=92434 tim=1436288197201914 13 WAIT #46912556873704: nam='direct path read' ela= 44 file number=10 first dba=17632 block cnt=8 obj#=92434 tim=1436288197213080 14 WAIT #46912556873704: nam='direct path read' ela= 47 file number=10 first dba=17640 block cnt=8 obj#=92434 tim=1436288197224429 15 WAIT #46912556873704: nam='direct path read' ela= 44 file number=10 first dba=17648 block cnt=8 obj#=92434 tim=1436288197235618 16 WAIT #46912556873704: nam='direct path read' ela= 56 file number=10 first dba=17656 block cnt=8 obj#=92434 tim=1436288197247424 17 FETCH #46912556873704:c=0,e=19848382,p=14178,cr=14181,cu=0,mis=0,r=1,dep=0,og=1,plh=463314188,tim=1436288197269519 18 STAT #46912556873704 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=14181 pr=14178 pw=0 time=19848378 us)' 19 STAT #46912556873704 id=2 cnt=95344 pid=1 pos=1 obj=92434 op='TABLE ACCESS FULL T3 (cr=14181 pr=14178 pw=0 time=19110498 us cost=3863 size=212000 card=4000)' 20 WAIT #46912556873704: nam='SQL*Net message from client' ela= 296 driver id=1413697536 #bytes=1 p3=0 obj#=92434 tim=1436288197270054 21 FETCH #46912556873704:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=463314188,tim=1436288197270136 22 WAIT #46912556873704: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=92434 tim=1436288197270200 23 WAIT #46912556873704: nam='SQL*Net message from client' ela= 584 driver id=1413697536 #bytes=1 p3=0 obj#=92434 tim=1436288197270805 24 CLOSE #46912556873704:c=0,e=17,dep=0,type=0,tim=1436288197270917 25

Encrypt those backups

April 2005 Ameratrade loses a backup tape containing information on 200,000 customers.

February 2005 Bank of America loses backup tapes containing information on 1.2 million charge cards.

September 2011, SAIC loses backup tapes of 4.9 Million members of the military who sought medical treatment in the San Antonio area. The data contained name, social security numbers, phone numbers and medical information. This data was not encrypted.

SAIC made the following statement: “Retrieving data on the tapes, which were stolen from a company employee’s car is not likely to happen because doing so requires knowledge of and access to specific hardware and software and knowledge of the system and data structures.” Excuse me if this does not make me feel better. I can get on eBay to get the hardware needed and download the software from any number of vendors to do the restore. Yes if the backup was done from Oracle or DB2 or MS SQL Server then you would need the software from the vendor. What if this theft was targeted and the thief knew what they were after?

I can go on and on about backup tapes that are lost out of the back seat of an employees’ car. And to be honest; I have transported tapes in my car too. However; when I reflect on transporting critical information in my car, I now get the hebegebes. Now we use a bonded courier to transport backup tapes.

Backup tapes are also being shipped to someplace like Iron Mountain. But lets face it, the people who are handling your backup tapes are low paid employees who could be influenced to look the other way. If someone really wants your backup tapes there is a way for someone to get your backup tape.

What are the options for encrypting backups.

  1. Use rman encryption.

  2. Encrypt the backup files on the OS.

For option 1, using rman to encrypt. There are a few options you can use a password to encrypt the backup or you can use a wallet to encrypt the backup.

If the backup is being sent offsite, using a password to encrypt the backup may be your better option.

If the backup is being sent to a Disaster Recovery site to build a standby database, using the wallet may be the better option.

Right now we are addressing sending a backup offsite so lets walk through the process of building an encrypted backup using a password.

First find out what encryption algorithms are supported.

SQL> select ALGORITHM_NAME, ALGORITHM_DESCRIPTION

2 from V$RMAN_ENCRYPTION_ALGORITHMS;

ALGORITHM_ ALGORITHM_DESCRIPTION

———- —————————————————————-

AES128 AES 128-bit key

AES192 AES 192-bit key

AES256 AES 256-bit key

SQL>

Of the algorithms that are available, AES256 is the strongest one available. So we are going to select AES256 for our encryption.

RMAN> set encryption algorithm ‘aes256’ identified by A_Passphrase_that_you_select;

executing command: SET encryption

using target database control file instead of recovery catalog

Using “set encryption algorithm’ command we did two things. One we set the algorithm that will be used for the backup and we set the passphrase that we need to decrypt the backup.

Next we are going to run the backup like we would normally do.

RMAN> backup as compressed backupset database format ‘/home/oracle/backup/encrypted_with_password%u%d.backup’;

Starting backup at 02-AUG-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/opt/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00001 name=/opt/oracle/oradata/orcl/system01.dbf

input datafile file number=00002 name=/opt/oracle/oradata/orcl/example01.dbf

input datafile file number=00004 name=/opt/oracle/oradata/orcl/undotbs01.dbf

input datafile file number=00006 name=/opt/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 02-AUG-12

channel ORA_DISK_1: finished piece 1 at 02-AUG-12

piece handle=/home/oracle/backup/encrypted_with_password0dnhl9n6ORCL.backup tag=TAG20120802T170333 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 02-AUG-12

channel ORA_DISK_1: finished piece 1 at 02-AUG-12

piece handle=/home/oracle/backup/encrypted_with_password0enhl9s1ORCL.backup tag=TAG20120802T170333 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 02-AUG-12

RMAN>

How do we decrypt the backup when we need to restore. It’s that simple.

RMAN> set decryption identified by A_Passphrase_that_you_select;

executing command: SET decryption

using target database control file instead of recovery catalog

RMAN> restore database;

Starting restore at 02-AUG-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

skipping datafile 1; already restored to file /opt/oracle/oradata/orcl/system01.dbf

skipping datafile 2; already restored to file /opt/oracle/oradata/orcl/example01.dbf

skipping datafile 3; already restored to file /opt/oracle/oradata/orcl/sysaux01.dbf

skipping datafile 4; already restored to file /opt/oracle/oradata/orcl/undotbs01.dbf

skipping datafile 6; already restored to file /opt/oracle/oradata/orcl/users01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 02-AUG-12

RMAN>

Okay, I did not need to restore the database, but it’s good to know that this works.

Now you don’t have an excuse not encrypt your backups.