Re: Re: Explain Plan and Security
Date: Tue, 19 Jun 2018 09:23:55 +0000
Message-ID: <MM1P123MB084204A0EEA6CC5B0EFD2E1AA5700_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM>
Fantastic bit of geekery from Stefan!.
Lothar, you may find that the underlying library bundles the open, execute and first fetch, so you may not get a plan until actually do the first fetch.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> Sent: 19 June 2018 09:53
To: contact_at_soocs.de
Cc: oracle-l_at_freelists.org
Subject: Re: Re: Explain Plan and Security
Hi Stefan,
thanks, thats interessting.
I am currently thinking of a much simpler solution though.
We could set up a runner job under the Apps User Authority,
The Job would receive a statement and bind variables, open the cursor but not fetch the data.
Sorry that this is so prosaic.
regards
Lothar
----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
Received on Tue Jun 19 2018 - 11:23:55 CEST