Re: Associating two queries

From: David Fitzjarrell <>
Date: Mon, 08 Jun 2015 14:16:24 +0000
Message-ID: <>

Not that I know of.  EXECUTE IMMEDIATE executes a context switch and executes the resulting dynamic query in a new session so there may not be a link to the source of the resulting statement.  This isn't like parallel execution where slaves are spawned from a parent session; the session opened  by the EXECUTE IMMEDIATE call isn't a child session.  David Fitzjarrell

Principal author, "Oracle Exadata Survival Guide"  

     On Monday, June 8, 2015 12:28 AM, "" <> wrote:    

 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) ASBEGINEXECUTE 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 SYS4u2rt637qymsw 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 

Received on Mon Jun 08 2015 - 16:16:24 CEST

Original text of this message