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

From: Jorgensen, Finn <Finn.Jorgensen_at_constellation.com>
Date: Mon, 2 May 2011 17:11:40 -0400
Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF021F6A9E4D65_at_EXM-OMF-04.Ceg.Corp.Net>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of D'Hooge Freek Sent: Monday, May 02, 2011 4:06 PM
To: Kenneth Naim; oratune_at_yahoo.com; jkstill_at_gmail.com; '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_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

---
From: Kenneth Naim [mailto:kennethnaim_at_gmail.com] 
Sent: maandag 2 mei 2011 21:35
To: oratune_at_yahoo.com; D'Hooge Freek; jkstill_at_gmail.com; '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

Received on Mon May 02 2011 - 16:11:40 CDT