Re: Explain Plan and Security

From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
To: Stefan Koehler <contact@xxxxxxxx>
Date: Tue, 19 Jun 2018 20:04:24 +0300

There's an *action* controlc_signal in recent Oracle versions (12.2, maybe
earlier), so you won't have to crash the process:

SQL> *oradebug doc event action controlc_signal*
controlc_signal
- received 1013 signal
Usage
-------
controlc_signal()

SQL> ALTER SESSION SET EVENTS 'sql_trace[SQL: 3vjxpmhhzngu4] {callstack:
fname opifch} *controlc_signal()*';

Session altered.

SQL> SELECT * FROM dual;
ERROR:
*ORA-01013: user requested cancel of current operation*

no rows selected

SQL>

On Tue, Jun 19, 2018 at 12:11 PM Stefan Koehler <contact@xxxxxxxx> wrote:

Hey Lothar,
today I had a little bit time to geek out and here is a working test case
for what I've mentioned here (
https://www.freelists.org/post/oracle-l/Re-Re-Explain-Plan-and-Security,1
).

******************
-- Create test objects
create table t1 (a number);
create index t1_i on t1(a);
insert into t1 select rownum from dba_objects where rownum <= 10;
commit;
exec dbms_stats.gather_table_stats (USER,'T1');

-- Test SQL just for information
select * from t1; --> SQL-ID: 27uhu2q2xuu7r

-- 11.2 solution
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname
opifch2} {pgadep: exactdepth 0} plan_stat=never,wait=false,bind=false
crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL
ALLSTATS OUTLINE PEEKED_BINDS LAST'));

-- 12.2 solution (as event filter "pgadep" in RDBMS library is not
available anymore)
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname
opifch} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL
ALLSTATS OUTLINE PEEKED_BINDS LAST'));
******************

The process aborts with "ORA-03113: end-of-file on communication channel"
right before/at fetching the data - so this should exactly fit your needs /
security requirements :-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK

"l.flatz@xxxxxxxxxx" <l.flatz@xxxxxxxxxx> hat am 14. Juni 2018 um 14:36

geschrieben:

Hi,

you might know Kerry´s classic blog:

http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/.

Normally my work around for explain plan issues is to run the query and

use dbms_xplan.display_cursor.

Now I am working in an environment where I must not run a query, but I

can do explain plan.

But still I think I can not tolerate explain plan weaknesses.
I think it should be possble to use DBMS_SQL to parse a statement and

receive a proper plan without actually running the statement.

Then use dbms_xplan.display_cursor.
Before I spent time, has anybody done it already?

Regards

Lothar

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

References:
- Explain Plan and Security
  - From: l.flatz@xxxxxxxxxx
- Re: Explain Plan and Security
  - From: Stefan Koehler

Re: Explain Plan and Security

Other related posts: