#Oracle #TDE Webinar Ghost Data Teaser Sponsored by #ODTUG

Here is a teaser for the Oracle Transparent Data Encryption Webinar Sponsored by ODTUG.

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.

Learn more by signing up for my webinar at: https://attendee.gotowebinar.com/register/7938280806383993602

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

#Oracle #In-Memory

I started to play with in-memory to see what performance improvements I can get.  I set up four tables, zip, customers, products and sales and executed a simple queries against them.

Sales has 3M rows, and customers has 110K rows, zip 4271 rows and products 6 rows. 

SELECT p.name, sum(s.price),count(*)
FROM customers c,
       products p,
       sales s,
       zip_codes z
WHERE c.ID = s.cust_id
    AND p.ID = s.product_id
    AND c.zip = z.zip
GROUP BY p.NAME;

Run 1) using a default install of Oracle 12.1.0.2.  Execution time 17.21 seconds

1 SQL> sho parameter inmemory 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 inmemory_clause_default string 6 inmemory_force string DEFAULT 7 inmemory_max_populate_servers integer 0 8 inmemory_query string ENABLE 9 inmemory_size big integer 0 10 inmemory_trickle_repopulate_servers_ integer 1 11 percent 12 optimizer_inmemory_aware boolean TRUE 13 14 15 SQL> l 16 1 SELECT p.name, sum(s.price),count(*) 17 2 FROM customers c, 18 3 products p, 19 4 sales s, 20 5 zip_codes z 21 6 WHERE c.ID = s.cust_id 22 7 AND p.ID = s.product_id 23 8 AND c.zip = z.zip 24 9* GROUP BY p.NAME 25 SQL> / 26 27 Elapsed: 00:00:17.21 28 29 Execution Plan 30 ---------------------------------------------------------- 31 Plan hash value: 1738379704 32 33 ---------------------------------------------------------------------------------------------- 34 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 35 ---------------------------------------------------------------------------------------------- 36 | 0 | SELECT STATEMENT | | 1 | 47 | 3118 (1)| 00:00:01 | 37 | 1 | HASH GROUP BY | | 1 | 47 | 3118 (1)| 00:00:01 | 38 | 2 | NESTED LOOPS | | 1 | 47 | 3117 (1)| 00:00:01 | 39 | 3 | NESTED LOOPS | | 1 | 47 | 3117 (1)| 00:00:01 | 40 |* 4 | HASH JOIN | | 1 | 29 | 3116 (1)| 00:00:01 | 41 |* 5 | HASH JOIN | | 1 | 16 | 353 (1)| 00:00:01 | 42 | 6 | TABLE ACCESS FULL | ZIP | 4271 | 21355 | 11 (0)| 00:00:01 | 43 | 7 | TABLE ACCESS FULL | CUSTOMERS | 110K| 1181K| 342 (1)| 00:00:01 | 44 | 8 | TABLE ACCESS FULL | SALES | 3142K| 38M| 2755 (1)| 00:00:01 | 45 |* 9 | INDEX UNIQUE SCAN | SYS_C0011426 | 1 | | 0 (0)| 00:00:01 | 46 | 10 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 18 | 1 (0)| 00:00:01 | 47 ---------------------------------------------------------------------------------------------- 48 49 Predicate Information (identified by operation id): 50 --------------------------------------------------- 51 52 4 - access("C"."ID"="S"."CUST_ID") 53 5 - access("C"."ZIP"="Z"."ZIP") 54 9 - access("P"."ID"="S"."PRODUCT_ID") 55 56 57 Statistics 58 ---------------------------------------------------------- 59 2313 recursive calls 60 0 db block gets 61 3155556 consistent gets 62 16351 physical reads 63 34844 redo size 64 882 bytes sent via SQL*Net to client 65 551 bytes received via SQL*Net from client 66 2 SQL*Net roundtrips to/from client 67 179 sorts (memory) 68 0 sorts (disk) 69 5 rows processed

So we turn on In-Memory by setting inmemory_size to a value > 100M. (100M is the smallest value you can use)  We then add the sales and customers table to the In-Memory column store using the alter table <tablename> inmemory statement. Once I made the change I did a stop / start of the instance and executed the query again. Oh, I did one more thing to load the column store, select * from sales; and select * from customers;  If you want to load the column store when the database instance starts up then use the alter statement alter table <tablename> inmemory priority critical; (there are five levels of priority critical, high, medium, low and none. (none is the default)

Run 2) Execution time 10.73 seconds.  Yea, that’s all I did and it’s that simple.  Now imagine the performance improvement if I actually tried!

1 SQL> alter system set INMEMORY_SIZE=500M scope=spfile; 2 3 System altered. 4 5 SQL> alter table sales inmemory; 6 7 Table altered. 8 9 SQL> alter table customers inmemory; 10 11 Table altered. 12 13 SQL> 14

1 SQL> sho parameter inmemory 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 inmemory_clause_default string 6 inmemory_force string DEFAULT 7 inmemory_max_populate_servers integer 1 8 inmemory_query string ENABLE 9 inmemory_size big integer 500M 10 inmemory_trickle_repopulate_servers_ integer 1 11 percent 12 optimizer_inmemory_aware boolean TRUE 13 SQL> 14 15 16 SQL> set timing on 17 SQL> SELECT p.name, sum(s.price),count(*) 18 FROM customers c, 19 products p, 20 sales s, 21 zip z 22 WHERE c.ID = s.cust_id 23 AND p.ID = s.product_id 24 AND c.zip = z.zip 25 GROUP BY p.NAME 26 / 27 2 3 4 5 6 7 8 9 10 28 29 Elapsed: 00:00:10.73 30 31 Execution Plan 32 ---------------------------------------------------------- 33 Plan hash value: 1408061610 34 35 ------------------------------------------------------------------------------------------- 36 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 37 ------------------------------------------------------------------------------------------- 38 | 0 | SELECT STATEMENT | | 1 | 47 | 215 (17)| 00:00:01 | 39 | 1 | HASH GROUP BY | | 1 | 47 | 215 (17)| 00:00:01 | 40 |* 2 | HASH JOIN | | 1 | 47 | 214 (17)| 00:00:01 | 41 |* 3 | HASH JOIN | | 1 | 34 | 76 (2)| 00:00:01 | 42 | 4 | MERGE JOIN CARTESIAN | | 25626 | 575K| 62 (0)| 00:00:01 | 43 | 5 | TABLE ACCESS FULL | PRODUCTS | 6 | 108 | 3 (0)| 00:00:01 | 44 | 6 | BUFFER SORT | | 4271 | 21355 | 59 (0)| 00:00:01 | 45 | 7 | TABLE ACCESS FULL | ZIP | 4271 | 21355 | 10 (0)| 00:00:01 | 46 | 8 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 110K| 1181K| 14 (8)| 00:00:01 | 47 | 9 | TABLE ACCESS INMEMORY FULL | SALES | 3142K| 38M| 130 (20)| 00:00:01 | 48 ------------------------------------------------------------------------------------------- 49 50 Predicate Information (identified by operation id): 51 --------------------------------------------------- 52 53 2 - access("C"."ID"="S"."CUST_ID" AND "P"."ID"="S"."PRODUCT_ID") 54 3 - access("C"."ZIP"="Z"."ZIP") 55 56 Note 57 ----- 58 - dynamic statistics used: dynamic sampling (level=2) 59 - 1 Sql Plan Directive used for this statement 60 61 62 Statistics 63 ---------------------------------------------------------- 64 3894 recursive calls 65 0 db block gets 66 9231 consistent gets 67 14236 physical reads 68 0 redo size 69 882 bytes sent via SQL*Net to client 70 551 bytes received via SQL*Net from client 71 2 SQL*Net roundtrips to/from client 72 526 sorts (memory) 73 0 sorts (disk) 74 5 rows processed 75 76 SQL>

There will be more to come, but if you want to read more about Oracle in-memory there is an excellent white paper at http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html

Okay, I looked at the memory usage and the size of my tables I loaded into the column store and made a change.  The customers table is over 1G, therefore I changed the inmemory_size to 2G.  Let’s see what that does for my performance.

1 SQL> alter system set inmemory_size=2G scope=spfile; 2 3 System altered. 4 5 SQL> shutdown immediate 6 Database closed. 7 Database dismounted. 8 ORACLE instance shut down. 9 SQL> startup 10 ORACLE instance started. 11 12 Total System Global Area 2449473536 bytes 13 Fixed Size 2948320 bytes 14 Variable Size 243270432 bytes 15 Database Buffers 50331648 bytes 16 Redo Buffers 5439488 bytes 17 In-Memory Area 2147483648 bytes 18 Database mounted. 19 Database opened. 20 SQL> 21

Okay, 6.02 seconds.  Nice try but I think can do better.  Physical reads went from  16,351 without in-memory to 14,236 with 500M used for in-memory and down to 1,084 when I sized in-memory appropriately.

1 SQL> @qry1 2 3 NAME SUM(S.PRICE) COUNT(*) 4 ----------------------------------- ------------ ---------- 5 Posidon Odin 527301055 627150 6 Scuba Pro G500 Regulator 453549388 628716 7 Stop Stink 1874278.96 627888 8 Scuba Fins 75210608.1 629888 9 Scuba Pro Mask 46894525.5 628395 10 11 Elapsed: 00:00:06.02 12 13 Execution Plan 14 ---------------------------------------------------------- 15 Plan hash value: 1408061610 16 17 ------------------------------------------------------------------------------------------- 18 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 19 ------------------------------------------------------------------------------------------- 20 | 0 | SELECT STATEMENT | | 1 | 47 | 215 (17)| 00:00:01 | 21 | 1 | HASH GROUP BY | | 1 | 47 | 215 (17)| 00:00:01 | 22 |* 2 | HASH JOIN | | 1 | 47 | 214 (17)| 00:00:01 | 23 |* 3 | HASH JOIN | | 1 | 34 | 76 (2)| 00:00:01 | 24 | 4 | MERGE JOIN CARTESIAN | | 25626 | 575K| 62 (0)| 00:00:01 | 25 | 5 | TABLE ACCESS FULL | PRODUCTS | 6 | 108 | 3 (0)| 00:00:01 | 26 | 6 | BUFFER SORT | | 4271 | 21355 | 59 (0)| 00:00:01 | 27 | 7 | TABLE ACCESS FULL | ZIP | 4271 | 21355 | 10 (0)| 00:00:01 | 28 | 8 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 110K| 1181K| 14 (8)| 00:00:01 | 29 | 9 | TABLE ACCESS INMEMORY FULL | SALES | 3142K| 38M| 130 (20)| 00:00:01 | 30 ------------------------------------------------------------------------------------------- 31 32 Predicate Information (identified by operation id): 33 --------------------------------------------------- 34 35 2 - access("C"."ID"="S"."CUST_ID" AND "P"."ID"="S"."PRODUCT_ID") 36 3 - access("C"."ZIP"="Z"."ZIP") 37 38 Note 39 ----- 40 - dynamic statistics used: dynamic sampling (level=2) 41 - 1 Sql Plan Directive used for this statement 42 43 44 Statistics 45 ---------------------------------------------------------- 46 3793 recursive calls 47 0 db block gets 48 9239 consistent gets 49 1084 physical reads 50 0 redo size 51 882 bytes sent via SQL*Net to client 52 551 bytes received via SQL*Net from client 53 2 SQL*Net roundtrips to/from client 54 491 sorts (memory) 55 0 sorts (disk) 56 5 rows processed 57 58 SQL> 59

Okay, I decided to do a little better.  I added three indexes to support the foreign key relationships and see what that does.  3.79 seconds, 44 physical reads.  My work here is done.

1 SQL> create index sales_cust_id on sales(cust_id); 2 3 Index created. 4 5 Elapsed: 00:00:17.77 6 SQL> create index product_id on sales(product_id); 7 8 Index created. 9 10 1* create index customers_zip on customers(zip) 11 SQL> / 12 13 Index created. 14 15 Elapsed: 00:00:00.58 16 17 Elapsed: 00:00:24.23 18 SQL> exec dbms_stats.gather_schema_stats(user); 19 20 PL/SQL procedure successfully completed. 21 22 Elapsed: 00:00:22.57 23 24 SQL> @qry1 25 26 NAME SUM(S.PRICE) COUNT(*) 27 ----------------------------------- ------------ ---------- 28 Posidon Odin 527301055 627150 29 Scuba Pro G500 Regulator 453549388 628716 30 Scuba Fins 75210608.1 629888 31 Scuba Pro Mask 46894525.5 628395 32 Stop Stink 1874278.96 627888 33 34 Elapsed: 00:00:03.79 35 36 Execution Plan 37 ---------------------------------------------------------- 38 Plan hash value: 463445694 39 40 ---------------------------------------------------------------------------------------------------------- 41 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | 42 ---------------------------------------------------------------------------------------------------------- 43 | 0 | SELECT STATEMENT | | 5 | 235 | | 4081 (4)| 00:00:01 | 44 | 1 | HASH GROUP BY | | 5 | 235 | | 4081 (4)| 00:00:01 | 45 | 2 | MERGE JOIN | | 5 | 235 | | 4080 (4)| 00:00:01 | 46 | 3 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 6 | 108 | | 2 (0)| 00:00:01 | 47 | 4 | INDEX FULL SCAN | SYS_C0011426 | 6 | | | 1 (0)| 00:00:01 | 48 |* 5 | SORT JOIN | | 5 | 145 | | 4078 (4)| 00:00:01 | 49 | 6 | VIEW | VW_GBC_13 | 5 | 145 | | 4077 (4)| 00:00:01 | 50 | 7 | HASH GROUP BY | | 5 | 140 | | 4077 (4)| 00:00:01 | 51 |* 8 | HASH JOIN | | 3102K| 82M| | 4000 (2)| 00:00:01 | 52 | 9 | INDEX FAST FULL SCAN | ZIP_ZIP | 4271 | 21355 | | 4 (0)| 00:00:01 | 53 |* 10 | HASH JOIN | | 3102K| 68M| 2368K| 3988 (2)| 00:00:01 | 54 | 11 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 110K| 1074K| | 14 (8)| 00:00:01 | 55 | 12 | TABLE ACCESS INMEMORY FULL| SALES | 3142K| 38M| | 130 (20)| 00:00:01 | 56 ---------------------------------------------------------------------------------------------------------- 57 58 Predicate Information (identified by operation id): 59 --------------------------------------------------- 60 61 5 - access("P"."ID"="ITEM_1") 62 filter("P"."ID"="ITEM_1") 63 8 - access("C"."ZIP"="Z"."ZIP") 64 10 - access("C"."ID"="S"."CUST_ID") 65 66 Note 67 ----- 68 - dynamic statistics used: dynamic sampling (level=2) 69 - 1 Sql Plan Directive used for this statement 70 71 72 Statistics 73 ---------------------------------------------------------- 74 139 recursive calls 75 0 db block gets 76 189 consistent gets 77 44 physical reads 78 0 redo size 79 882 bytes sent via SQL*Net to client 80 551 bytes received via SQL*Net from client 81 2 SQL*Net roundtrips to/from client 82 13 sorts (memory) 83 0 sorts (disk) 84 5 rows processed 85 86 SQL> 87

#infosec #Oracle data #redaction

I had a rather disturbing conversion with my wife a couple nights ago, she had called a service provider to discuss a bill.  The customer service rep asked her for her social security number to verify her identity.  When my wife told the customer service rep she was not comfortable giving out that information, the customer service rep told her that he has her full social security number on the screen in front of him. WTF. This is a large corporation, and I’m wondering if they are ignoring the problem until there is a leak and they get embarrassed because after all it cost money to protect data. 

Do you store sensitive information? Does you’re user base have access to that information?  Does you’re user base need to see that sensitive information? I have always operated on the principle of “need to know.”  This simply states, does a person need to know something in order to do their job?  If the answer to that is no, then don’t give them the information.

In the customers table we are storing two very sensitive pieces of information, SSN and Credit Card Number.  Now sit back and think, what would happen to your company / job / reputation if this information were to leak? 

1 SQL> desc customers 2 Name Null? Type 3 ----------------------------------------- -------- ---------------------------- 4 ID NOT NULL NUMBER 5 SSN VARCHAR2(25) 6 FNAME VARCHAR2(55) 7 LNAME VARCHAR2(55) 8 UPDATE_DATE DATE 9 CREDIT_CARD_NBR VARCHAR2(19) 10 CREDIT_LIMIT NUMBER 11 12

Before we start, lets populate customers.ssn and customers.credit_card_nbr with random data just to make sure there is no live data is involved in this demonstration.

1 SQL> @redaction_demo 2 SQL> conn rlockard@pdev 3 Enter password: 4 Connected. 5 SQL> 6 SQL> update customers set ssn = 7 2 ceil(dbms_random.value(0,9)) || 8 3 ceil(dbms_random.value(0,9)) || 9 4 ceil(dbms_random.value(0,9)) || '-' || 10 5 ceil(dbms_random.value(0,9)) || 11 6 ceil(dbms_random.value(0,9)) || '-' || 12 7 ceil(dbms_random.value(0,9)) || 13 8 ceil(dbms_random.value(0,9)) || 14 9 ceil(dbms_random.value(0,9)) || 15 10 ceil(dbms_random.value(0,9)), 16 11 credit_card_nbr = 17 12 ceil(dbms_random.value(0,9)) || 18 13 ceil(dbms_random.value(0,9)) || 19 14 ceil(dbms_random.value(0,9)) || 20 15 ceil(dbms_random.value(0,9)) || ' ' || 21 16 ceil(dbms_random.value(0,9)) || 22 17 ceil(dbms_random.value(0,9)) || 23 18 ceil(dbms_random.value(0,9)) || 24 19 ceil(dbms_random.value(0,9)) || ' ' || 25 20 ceil(dbms_random.value(0,9)) || 26 21 ceil(dbms_random.value(0,9)) || 27 22 ceil(dbms_random.value(0,9)) || 28 23 ceil(dbms_random.value(0,9)) || ' ' || 29 24 ceil(dbms_random.value(0,9)) || 30 25 ceil(dbms_random.value(0,9)) || 31 26 ceil(dbms_random.value(0,9)) || 32 27 ceil(dbms_random.value(0,9)); 33 34 1666 rows updated. 35 36 SQL> 37 SQL> commit; 38

So lets connect at app_user and see what we can see.  Umm, I don’t like that.  Imagine the damage that could be caused if a less then honest employee went on a shopping spree.  I know of once such case where a customer service rep had access to information like this. When the theft was discovered, we estimate over $250,000 was stolen over a six month period.

1 SQL> conn app_user/app_user@pdev 2 Connected. 3 SQL> select ssn, credit_card_nbr from rlockard.customers; 4 /* snip a lot */ 5 428-72-3919 2671 6148 1798 1588 6 251-71-5615 7925 4145 5815 3778 7 345-69-2348 9457 1637 2244 2527 8 967-55-1843 7474 9595 9119 7212 9 969-21-1324 4642 3219 4458 6715 10

What if we could create a policy that only let the user see the last four digits of the ssn and the last for digits of the credit card number?  We use the dbms_redact.add_policy to a table column.  in this case we are redacting rlockard.customers.ssn and giving it a policy name of cust_sensitive.  There is also the expression; expression when evaluated to true the policy will be used. We are also passing function_type that is dbms_redact.partial and function_parameters.  Function parameters uses the predefined constant dbms_redact.redact_us_ssn_f5.  This redacts the first five characters of the social security number.

1 SQL> conn rlockard@pdev 2 Enter password: 3 Connected. 4 SQL> 5 SQL> declare 6 2 begin 7 3 dbms_redact.add_policy ( 8 4 object_schema => 'RLOCKARD', 9 5 object_name => 'CUSTOMERS', 10 6 policy_name => 'CUST_SENSITIVE', 11 7 expression => '1=1', 12 8 column_name => 'SSN', 13 9 function_type => dbms_redact.partial, 14 10 function_parameters => DBMS_REDACT.REDACT_US_SSN_F5 15 11 ); 16 12 end; 17 13 / 18 19 PL/SQL procedure successfully completed. 20

Okay that takes care of SSN, and oh by the way “expression => ‘1=1’ is not required on Oracle 12C.  function_parameters uses the constant constant ‘DBMS_REDACT.REDACT_US_SSN_F5 that redacts the first five characters of the SSN.

Now lets take care of credit_card_number.  In this case because we have already added the cust_sensitive policy we are going to alter the policy to add a column (this is the action parameter.)  We then add credit_card_nbr and use the predefined constant dbms_redact.redact_cc16_f12.

1 SQL> 2 SQL> BEGIN 3 2 DBMS_REDACT.ALTER_POLICY ( 4 3 object_schema => 'RLOCKARD', 5 4 object_name => 'CUSTOMERS', 6 5 policy_name => 'CUST_SENSITIVE', 7 6 column_name => 'CREDIT_CARD_NBR', 8 7 action => DBMS_REDACT.ADD_COLUMN, 9 8 function_type => DBMS_REDACT.PARTIAL, 10 9 function_parameters => DBMS_REDACT.REDACT_CCN16_F12, 11 10 expression => '1=1' 12 11 ); 13 12 END; 14 13 / 15 16 PL/SQL procedure successfully completed. 17

Well Oracle also provides us with the constant DBMS_REDACT.REDACT_CCN16_F12 that redacts the first twelve characters of a sixteen digit credit card number.  So what does this look like.

1 SQL> 2 SQL> conn app_user/app_user@pdev 3 Connected. 4 select ssn, credit_card_nbr from rlockard.customers; 5 /* snip a lot */ 6 XXX-XX-1979 ****-****-****-8336 7 XXX-XX-4224 ****-****-****-2794 8 XXX-XX-3475 ****-****-****-2386 9 XXX-XX-8333 ****-****-****-5569 10 11 SSN CREDIT_CARD_NBR 12 ------------------------- ------------------- 13 XXX-XX-6396 ****-****-****-1936 14 XXX-XX-8118 ****-****-****-7557 15 XXX-XX-8122 ****-****-****-9645 16 XXX-XX-3169 ****-****-****-2112 17 XXX-XX-2722 ****-****-****-1597

Well we have connected as app_user, and the data is now redacted to a point where it is semi-secure. I would not be comfortable with this being public, but it’s a big improvement over displaying the entire social security number and credit card number.