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.