RE: Security Question - how do you deal with sensitive information hardcoded in SQL statements
- From: "Jorgensen, Finn" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
- To: "'Freek.DHooge@xxxxxxxxx'" <Freek.DHooge@xxxxxxxxx>, 'Kenneth Naim' <kennethnaim@xxxxxxxxx>, "'oratune@xxxxxxxxx'" <oratune@xxxxxxxxx>, "'jkstill@xxxxxxxxx'" <jkstill@xxxxxxxxx>, 'Oracle-L Freelists' <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 May 2011 17:11:40 -0400
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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
