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

I tested this on 11.2.0.1 on Solaris and it was definitely not true :

select /* literals only */ count(*) from dba_tables where owner = 'KINTITG' and 
table_name = 'WP_TASKS';

  COUNT(*)
----------
         1


select sql_text from v$sql where sql_text like '%literals only%';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
select /* literals only */ count(*) from dba_tables where owner = :"SYS_B_0" 
and table_name = :"SYS_B_1"


var v_tab_name varchar2(30);
exec :v_tab_name:='WP_TASKS'

select /* literals plus bind */ count(*) from dba_tables where owner = 
'KINTITG' and table_name = :v_tab_name;

  COUNT(*)
----------
         1

select sql_text from v$sql where sql_text like '%literals plus bind%';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
select /* literals plus bind */ count(*) from dba_tables where owner = 
:"SYS_B_0" and table_name = :v_tab_name


Thanks,
Finn

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of D'Hooge Freek
Sent: Monday, May 02, 2011 4:06 PM
To: Kenneth Naim; oratune@xxxxxxxxx; jkstill@xxxxxxxxx; 'Oracle-L Freelists'
Subject: RE: Security Question - how do you deal with sensitive information 
hardcoded in SQL statements

Kenneth,

Are you sure about this?
I thought I had seen a query when investigating a different problem, which had 
both "normal" bind variablen and system generated ones.
I can't directly find the example again, but I will see if I can reproduce it.


Regards,


Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
---
From: Kenneth Naim [mailto:kennethnaim@xxxxxxxxx] 
Sent: maandag 2 mei 2011 21:35
To: oratune@xxxxxxxxx; D'Hooge Freek; jkstill@xxxxxxxxx; 'Oracle-L Freelists'
Subject: RE: Security Question - how do you deal with sensitive information 
hardcoded in SQL statements

Another caveat with cursor sharing is if the application uses bind variables 
and literals in the same statement, the literals won't be replaced as the 
optimizer assumes the developer that choose to use bind variables was smart 
enough to use them everywhere they should be used.

Ken

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


>>> This e-mail and any attachments are confidential, may contain legal, 
>>> professional or other privileged information, and are intended solely for 
>>> the addressee.  If you are not the intended recipient, do not use the 
>>> information in this e-mail in any way, delete this e-mail and notify the 
>>> sender. CEG-IP1

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


Other related posts: