Re: Re: Re: Explain Plan and Security
Date: Tue, 19 Jun 2018 09:41:41 +0000
Message-ID: <MM1P123MB084278E008A9D55FF26EACA7A5700_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM>
----Ursprüngliche Nachricht----
Von : contact_at_soocs.de
Datum : 19/06/2018 - 10:19 (GMT)
An : oracle-l_at_freelists.org, l.flatz_at_bluewin.ch
Betreff : Re: Explain Plan and Security
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 19 2018 - 11:41:41 CEST