Re: Explain Plan and Security

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

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_at_soocs.de> 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: _at_OracleSK
>
> > "l.flatz_at_bluewin.ch" <l.flatz_at_bluewin.ch> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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