Associating two queries
Date: Mon, 8 Jun 2015 10:04:26 +0800
Message-ID: <723E6501049D423D971176455ADB4051_at_NAUTILUS>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 08 2015 - 04:04:26 CEST