Re: Associating two queries

From: tefetufe . <coskan_at_gmail.com>
Date: Thu, 11 Jun 2015 12:27:48 +0400
Message-ID: <CAGLGTvPokcB9WavdT+b6cYpme4yeJkYdUVGuSGCEP8RUaoitnA_at_mail.gmail.com>

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_at_databasesecurity.com> 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:* david_at_databasesecurity.com
> <javascript:_e(%7B%7D,'cvml','david_at_databasesecurity.com');>
> *Sent:* Monday, June 08, 2015 10:04 AM
> *To:* oracle-l_at_freelists.org
> <javascript:_e(%7B%7D,'cvml','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
>
>

-- 
-- 
Coskan GUNDOGAR

Oracle DBA

Email: coskan_at_gmail.com
Blog: http://coskan.wordpress.com
Twitter: http://www.twitter.com/coskan
Linkedin: http://uk.linkedin.com/in/coskan

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 11 2015 - 10:27:48 CEST