Re: Associating two queries

From: <david_at_databasesecurity.com>
Date: Wed, 17 Jun 2015 18:33:03 +0800
Message-ID: <002F0D510D7E46DC94F72A5E0E7EC909_at_NAUTILUS>

Hi Yong,
The program_id is definitely helpful info because we can attempt to eventually) link it back to the parent query. However, if the attacker obfuscates their attack for example by creating a synonym then it makes it incredibly hard to find the link between the two queries. For example, if I create a synonym "hahaha" in my own schema for sys.execsomething() we get the following:

SQL> select sql_text, program_id, parsing_schema_id from v$sql where sql_text like upper('%abcppp%');

SQL_TEXT PROGRAM_ID PARSING_SCHEMA_ID
---------- --------- --------
begin hahaha('begin dbms_output.put_line(''ABCPPP''); end;'); end;

         0 142

begin dbms_output.put_line('ABCPPP'); end;

     93899                 0

So, even if we looked up the object_name for the program_id then parsed all the SQL_TEXT in V$SQL looking for that object_name we wouldn't find it.

Close, but no cigar :(

Cheers,
David

-----Original Message-----
From: "" <dmarc-noreply_at_freelists.org> (Redacted sender "yong321_at_yahoo.com" for DMARC) Sent: Tuesday, June 16, 2015 11:45 PM
To: oracle-l_at_freelists.org
Subject: Re: Associating two queries

David,

The dynamic SQL run by "execute immediate" in a stored procedure has program_id of v$sql pointing to the procedure, and program_line# identifies the line in dba_source. Using your example (except created in my schema instead of sys)

SQL> CREATE OR REPLACE PROCEDURE EXECSOMETHING(P VARCHAR) AS   2 BEGIN
  3 EXECUTE IMMEDIATE P;
  4 END;
  5 /

Procedure created.

SQL> EXEC EXECSOMETHING('BEGIN DBMS_OUTPUT.PUT_LINE(''FOOBAR''); END;'); PL/SQL procedure successfully completed.

Then I see these SQLs in v$sql (I flushed shared pool first to make the output cleaner):

SQL> select sql_id, sql_text from v$sqlarea where lower(sql_text) like '%dbms_output%' and sql_text not like 'select % v$%';

SQL_ID SQL_TEXT

------------- --------------------------------------------------------------------------------

b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; <-- ignore; from somewhere else
3083hkyavt9g8 BEGIN EXECSOMETHING('BEGIN DBMS_OUTPUT.PUT_LINE(''FOOBAR''); END;'); END; <-- PL/SQL "parent"
cxms51ba537p0 BEGIN DBMS_OUTPUT.PUT_LINE('FOOBAR'); END; <-- native dynamic SQL "child"

SQL> select sql_id, PROGRAM_ID,PROGRAM_LINE# from v$sql where sql_id in ('3083hkyavt9g8','cxms51ba537p0');

SQL_ID PROGRAM_ID PROGRAM_LINE#
------------- ---------- -------------

3083hkyavt9g8          0             0  <-- "parent"
cxms51ba537p0     106865             3  <-- "child"

SQL> select owner, object_name, object_type from dba_objects where object_id = 106865;

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -----------------------
YONG       EXECSOMETHING                  PROCEDURE

SQL> select line, text from dba_source where owner = 'YONG' and name = 'EXECSOMETHING';       LINE TEXT

---------- --------------------------------------------------------------------------------
         1 PROCEDURE EXECSOMETHING(P VARCHAR) AS
         2 BEGIN
         3 EXECUTE IMMEDIATE P;  <-- line# 3 where the "child" is run
         4 END;

An arbitrary dynamic SQL run by the procedure execsomething can be identified by the known program_id and program_line#. An arbitrary procedure or any PL/SQL stored program that can launch a native dynamic SQL can be found by searching dba_source for "execute immediate". But if it's anonymous block, you have to search v$sql for the block and dba_source for the line. Obviously the SQLs have to still exist in shared pool. Beginning with 11g, cursor invalidation won't age them out and program_id and program_line# columns retain their values on invalidation.

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



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

Received on Wed Jun 17 2015 - 12:33:03 CEST