Thanks – will look into that!
From: tefetufe .
Sent: Thursday, June 11, 2015 4:27 PM
To: david@xxxxxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Associating two queries
Long shot, but If you can manage to trackback how oracle recorda
top_level_sql_id on v$active_session_history you may get what you want. that
info is magically coming from somewhere not documented
On Thursday, June 11, 2015, <david@xxxxxxxxxxxxxxxxxxxx> wrote:
Hey all,
So from the lack of responses I’m guessing there’s no way to tell what the
parent query is of a child query executed via “EXECUTE IMMEDIATE”... I’ve pored
through V$SQL and X$KGLCURSOR but no cigar. Anyone got any ideas?
Cheers,
David
From: javascript:_e(%7B%7D,'cvml','david@xxxxxxxxxxxxxxxxxxxx');
Sent: Monday, June 08, 2015 10:04 AM
To: javascript:_e(%7B%7D,'cvml','oracle-l@xxxxxxxxxxxxx');
Subject: Associating two queries
Hey all,
PL/SQL injection vulnerabilities present a clear danger to a database's
security and I'm trying to nail down a method of discovering cases where such
flaws have been exploited. Consider the following scenario:
SYS owns a procedure called EXECSOMETHING and PUBLIC has the execute
privilege on it:
CREATE OR REPLACE PROCEDURE EXECSOMETHING(P VARCHAR) AS
BEGIN
EXECUTE IMMEDIATE P;
END;
/
Along comes SCOTT and executes
SQL> EXEC SYS.EXECSOMETHING('BEGIN DBMS_OUTPUT.PUT_LINE(''FOOBAR''); END;');
(Before you say, "what a silly contrived example!" recall
CTXSYS.DRILOAD.VALIDATE_STMT ;-)
If we look at V$SQL we can see SCOTT's original SQL and the SQL eventually
executed by SYS:
SQL> SELECT SQL_ID, PARSING_SCHEMA_NAME FROM V$SQL WHERE SQL_TEXT LIKE
UPPER('%foobar%');
SQL_ID PARSING_SCHEMA_NAME
------------- ------------------------------
6ck2d14sn6gtb SYS
4u2rt637qymsw SCOTT
Other than the txt of the SQL there's nothing to connect these two SQL
queries as far as I can tell.
Is this correct?
Is there a parent/child relationship I can query somewhere to say query x
spawned query y?
I've looked at CHILD_ADDRESS etc in V$SQL to see if there's a link but
there's none I can see. The best I have so far is that the time of the SYS
query falls with FIRST_LOAD_TIME and FIRST_LOAD_TIME+ELAPSED_TIME of SCOTT's
query - but other non-related queries may fulfil this criteria too.
Anyone got any ideas? Are there other fixed views I can query to prove a firm
relationship between SCOTT's query and the subsequent SYS query?
Thanks all!
Cheers,
David
--
--
Coskan GUNDOGAR
Oracle DBA
Email: coskan@xxxxxxxxx
Blog: http://coskan.wordpress.com
Twitter: http://www.twitter.com/coskan
Linkedin: http://uk.linkedin.com/in/coskan
