Re: Security Question - how do you deal with sensitive information hardcoded in SQL statements

The suggestion to set cursor_sharing to any value other than exact isn't the 
best option but it could work (you'd need to flush the shared pool first, though 
since the query text won't change and queries submitted before the 
cursor_sharing change would still be visible).  As an example, using the EMP 
table and (hopefully) non-existent SSN values (which  have been obscured in the 
event one or more of my generated values is an actual SSN):

SQL> alter table emp add emp_ssn varchar2(12);
Table altered.
SQL>
SQL> update emp set emp_ssn = 
substr(empno,1,3)||'-'||substr(empno,2,2)||'-'||empno;
14 rows updated.
SQL>
SQL> alter table emp modify emp_ssn not null;
Table altered.
SQL>
SQL> select * from emp
  2  where emp_ssn = 'xxx-xx-xxxx';
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     
DEPTNO EMP_SSN
---------- ---------- --------- ---------- --------- ---------- ---------- 
---------- ------------
      7782 CLARK      MANAGER         7839 09-JUN-81       
2450                    10 xxx-xx-xxxx
SQL>
SQL> select sql_text
  2  from v$sqlarea
  3  where sql_text like 'select * from%';
SQL_TEXT
---------------------------------------------------------------------------------------------------

select * from emp where emp_ssn = 'xxx-xx-xxxx';
SQL>
SQL> alter session set cursor_sharing = force;
Session altered.
SQL>
SQL> select *  from emp
  2  where emp_ssn = 'xxx-xx-xxxx';
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     
DEPTNO EMP_SSN
---------- ---------- --------- ---------- --------- ---------- ---------- 
---------- ------------
      7782 CLARK      MANAGER         7839 09-JUN-81       
2450                    10 xxx-xx-xxxx
SQL>
SQL> select sql_text
  2  from v$sqlarea
  3  where sql_text like 'select *  from%';
SQL_TEXT
---------------------------------------------------------------------------------------------------

select *  from emp where emp_ssn = :"SYS_B_0"
SQL>


Ideally you'd want the vendor to comply with regulations regarding sensitive 
data; one wonders how this vendor got this product on the market.


David Fitzjarrell





________________________________
From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
To: "jkstill_at_gmail.com" <jkstill_at_gmail.com>; Oracle-L Freelists 
<oracle-l_at_freelists.org>
Sent: Mon, May 2, 2011 12:10:34 PM
Subject: RE: Security Question - how do you deal with sensitive information 
hardcoded in SQL statements

Two options that I can think of (none of which I think are good, let's be clear 
about that):
X-archive-position: 36057
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce_at_freelists.org
Errors-to: oracle-l-bounce_at_freelists.org
X-original-sender: Freek.DHooge_at_uptime.be
Precedence: normal
Reply-To: Freek.DHooge_at_uptime.be
List-help: <mailto:ecartis_at_freelists.org?Subject=help>
List-unsubscribe: <oracle-l-request_at_freelists.org?Subject=unsubscribe>
List-software: Ecartis version 1.0.0
List-Id: oracle-l <oracle-l.freelists.org>
X-List-ID: oracle-l <oracle-l.freelists.org>
List-subscribe: <oracle-l-request_at_freelists.org?Subject=subscribe>
List-owner: <mailto:steve.adams_at_ixora.com.au>
List-post: <mailto:oracle-l_at_freelists.org>
List-archive: <http://www.freelists.org/archives/oracle-l>
X-list: oracle-l

* Set cursor_sharing to force or similar, so that hard coded values are replaced 
with bind variables.

. write a report for management, indicating that this is a problem and 
additional resources or changes from vendor are required to solve this. And then 
forget about it.


Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
---
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On 
Behalf Of Jared Still
Sent: maandag 2 mei 2011 19:49
To: Oracle-L Freelists
Subject: Security Question - how do you deal with sensitive information 
hardcoded in SQL statements


First, the assumption is that you are working with a 3rd party application, and 
there
is nothing you can do to modify the SQL statements used by the app.

Nor are you able at this time to apply an extra cost option, such as ASO.

How do you deal with sensitive information that may be hardcoded into SQL 
statements?

This kind of SQL presents all kinds of problems.

* statspack/AWR reports showing Top SQL
* queries for cached SQL
* execution plans.
* trace files
* probably many more I am not thinking of at the moment.

The problem arises when any sensitive information (SSN, CC#, etc) appears as a
hardcoded value in a SQL statement, and the SQL in question is a subject of 
current performance discussions, or troubleshooting of database and SQL issues.

The SQL statements get sent to Oracle support as part of AWR or Statspack 
reports,
execution plan analysis, trace files etc.

They can also inadvertently appear in emails, meetings and even presentations.

How do you deal with this?  
This issue has the potential for a fairly serious security breach.

TIA,

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l