Re: Associating two queries
Date: Thu, 11 Jun 2015 16:10:16 +0800
Message-ID: <510A1F8969D64F3EBFEF259135ED0B30_at_NAUTILUS>
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: david_at_databasesecurity.com
Sent: Monday, June 08, 2015 10:04 AM
To: oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 11 2015 - 10:10:16 CEST