I am SYS

Your sensitive data is only one query away, even if you encrypt it!

I have found that bind variables find their way into the redo logs as a side effect of the AWR collecting sql statements along with their bind values. They are also freely available in the AWR tables if they are captured.

Using the test case Oracle documented here… http://www.oracle.com/technology/obe/10gr2_db_vmware/security/tde/tde.htm

This test case shows that when using TDE to encrypt a column, the redo logs are definitely protected for the DML on the target table. The example shows the insert statement “insert into cust_payment_info values (‘Mike’, ‘Anderson’, 10004, ’4929889576357400′,’YES’);” actually gets mined out as “insert into “OE”.”cust_payment_info”(“FIRST_NAME”,”LAST_NAME”,”ORDER_NUMBER”,”CREDIT_CARD_NUMBER”,”ACTIVE_CARD”) values (‘Mike’, ‘Anderson’, 10004, ‘Unsupported Type’,'YES’);”. This shows that the encrypted column CREDIT_CARD_NUMBER actually gets mined out as ‘Unsupported Type’ so you are protected there…

In the case that I observed this, we use our own encryption function that uses dbms_crypto package, not TDE. So we won’t see the ‘Unsupported Type’, which gets placed by TDE internal mechanisms, but we will see the value as it has been encrypted, so this is acceptable for us…

But, where I have found you are not protected is the insert into the SYS.WRH$_SQLSTAT table which is part of the Automated Workload Repository data gathering actually has the literal bind value ’4929889576357400′ stored in hexadecimal format in the BIND_DATA column. This occurred when the sql statement used above “insert into cust_payment_info…” got parsed and placed into v$sql and eventually moved into Active Session History, then finally moved into the AWR into the SYS.WRH$_SQLSTAT table. You may want to run this query on your database for entertainment purposes to see what I am talking about: select dbms_sqltune.extract_bind(bind_data, 1).value_string from sys.wrh$_sqlstat where bind_data is not null; The view definition for DBA_HIST_SQLBIND will give you some information on how easy the hexadecimal data is to translate into your real data.

If by using logminer, you mine out inserts into the SYS.WRH$_SQLSTAT, you will see inserts like below:
“insert into “SYS”.”WRH$_SQLSTAT”(…,”BIND_DATA”,”FLAG”) values (…HEXTORAW(‘beda0a2003004a4232dd000408c0021603c2191a’),NULL);”
I removed most of the columns and literal values but chose to show you the remaining hex value that is inserted into the BIND_DATA column. This is where you would be able to get the card information that was originally inserted into the CREDIT_CARD_NUMBER column. A simple call to the dbms_sqltune.extract_bind procedure will give you the literal card number value. (Note: the hex above is not for this exact statement, and won’t return the card number in the test, but can be reproduced).

So, no matter if you encrypt a data column using TDE, or by encrypting it using another method before inserting, I have found this BIND_DATA is still in the AWR table and consequently in the redo logs. Since the BIND_DATA appears to be a simple hexadecimal conversion of some sort, it does not meet PCI encryption standards.
When I first presented this to Oracle in a Service Request, their recommendations to workaround this problem were to turn off AWR altogether, disabling bind capturing only, or to disable logging of peeked binds. All of these workarounds could be drastic changes on a production database system, so we needed a fix for this bug, which Oracle did provide. Oracle has provided us “Patch 9065026: BIND_DATA LACKS CONTROL AT SINGLE-SQL LEVEL” to control single sql statements that we know will have sensitive bind values.

The patch readme does not include instructions on how to use it, so I have provided a test case below that Oracle gave to me.

connect scott/tiger

set serveroutput on

declare

bindvar varchar2(20);

retval varchar2(20);

stmt varchar2(100);

bind_data varchar2(100);

hint varchar2(100);

stored_stmt varchar2(100);

begin

bindvar := ‘TURNER’;

stmt := ‘select ‘||

‘ ename from emp where ename = :b1′;

execute immediate stmt into retval using bindvar;

select sql_text, dbms_sqltune.extract_bind(bind_data,1).value_string

into stored_stmt, bind_data from v$sql where sql_text = stmt;

dbms_output.put_line(‘statement: ‘||stored_stmt);

dbms_output.put_line(‘bind_data: ‘||nvl(bind_data,’Empty!’));

end;

/

statement: select ename from emp where ename = :b1

bind_data: TURNER

declare

bindvar varchar2(20);

retval varchar2(20);

stmt varchar2(100);

bind_data varchar2(100);

hint varchar2(100);

stored_stmt varchar2(100);

begin

hint := ‘ /*+ OPT_PARAM(”_cursor_bind_capture_area_size”, 0) */ ‘;

bindvar := ‘TURNER’;

stmt := ‘select ‘||

hint||

‘ ename from emp where ename = :b1′;

execute immediate stmt into retval using bindvar;

select sql_text, dbms_sqltune.extract_bind(bind_data,1).value_string

into stored_stmt, bind_data from v$sql where sql_text = stmt;

dbms_output.put_line(‘statement: ‘||stored_stmt);

dbms_output.put_line(‘bind_data: ‘||nvl(bind_data,’Empty!’));

end;

/

statement: select /*+ OPT_PARAM(‘_cursor_bind_capture_area_size’, 0) */ ename

from emp where ename = :b1
bind_data: Empty!