Re: Associating two queries
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 08 Jun 2015 14:16:24 +0000
Message-ID: <1125211281.75385.1433772984017.JavaMail.yahoo_at_mail.yahoo.com>
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
Date: Mon, 08 Jun 2015 14:16:24 +0000
Message-ID: <1125211281.75385.1433772984017.JavaMail.yahoo_at_mail.yahoo.com>
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, "david_at_databasesecurity.com" <david_at_databasesecurity.com> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 08 2015 - 16:16:24 CEST