Re: what package/procedure did SQL come from?

From: Pete Finnigan <pete_at_petefinnigan.com>
Date: Thu, 04 Jun 2009 09:26:33 +0100
Message-ID: <4A278539.8040300_at_petefinnigan.com>

Hi Yurry,

yes its possible to unwrap PL/SQL. From 7-9i using one method; then the wrap mechanism changed in 10g but its weaker in my opinion as the process is deterministic - i.e. reversable. Anton Scheffer released a 10g unwrapper some time back; you can find a link in my blog - http://www.petefinnigan.com/weblog/entries/index.html.

Also I spoke about unwrapping mostly around 9i and lower at Blackhat in 2006 - the presentation is on my white papers page - http://www.petefinnigan.com/orasec.htm. 9i and lower is reversable but its much harder to do - See the Blackhat paper for some details on the method. Oracle has in-fact shipped an unwrapper with the database since wrap came out. Look at the function SYS.PSTUB. This shows how it works but its limited to PL/SQL signatures only.

Complete unwrapping is possible - loading a simple wrapped procedure:

SQL> _at_sample1.plb

Procedure created.

SQL> select substr(text,1,60)
  2 from dba_source
  3 where name='TEST_PROC'
  4 and rownum=1;

SUBSTR(TEXT,1,60)

procedure test_proc wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd

SQL> Then unwrapping:

SQL> _at_unwrap_c

NAME OF OBJECT TO CHECK                 [P1]: TEST_PROC
OWNER OF OBJECT TO CHECK               [SYS]: SYS
TYPE OF THE OBJECT               [PROCEDURE]: PROCEDURE
OUTPUT METHOD Screen/File                [S]: S
FILE NAME FOR OUTPUT              [priv.lst]:

OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

create or replace procedure TEST_PROC( PV_NUM in NUMBER, PV_VAR in VARCHAR2, PV_VAR3 in out INTEGER) is L_NUM NUMBER:=3;
L_VAR NUMBER;
J NUMBER:=1;
procedure NESTED( PV_LEN in out NUMBER) is X NUMBER;
begin
X:= PV_LEN * 5;
end;
begin
case L_NUM
when 1 then
L_VAR:=3;

 DBMS_OUTPUT. PUT_LINE('This is a header');
 DBMS_OUTPUT. PUT_LINE('The number is ' ||  L_VAR);
 DBMS_OUTPUT. PUT_LINE('The case var is ' ||  L_NUM);

when 2 then
L_VAR:=4;

 DBMS_OUTPUT. PUT_LINE('This is a header');
 DBMS_OUTPUT. PUT_LINE('The number is ' ||  L_VAR);
 DBMS_OUTPUT. PUT_LINE('The case var is ' ||  L_NUM);

when 3 then
L_VAR:=6;

 DBMS_OUTPUT. PUT_LINE('This is a header');
 DBMS_OUTPUT. PUT_LINE('The number is ' ||  L_VAR);
 DBMS_OUTPUT. PUT_LINE('The case var is ' ||  L_NUM);

else
DBMS_OUTPUT. PUT_LINE('wrong choice');
end case;
if ( ( J = 1) and ( J = 3)) then
DBMS_OUTPUT. PUT_LINE('here is IF');
elsif ( ( J = 2) or ( J != 3)) then
DBMS_OUTPUT. PUT_LINE('The elsif clause'); else
DBMS_OUTPUT. PUT_LINE('else clause');
end if;
J:=4;
NESTED( J);
DBMS_OUTPUT. PUT_LINE('nested=:' || J); for J in reverse 1.. PV_NUM loop
if MOD( J,2) = 0 then
DBMS_OUTPUT. PUT_LINE('for loop with reverse'); end if;
end loop;
end;
/

INFO: Elapsed time = [.1 Seconds]

PL/SQL procedure successfully completed.

...

cheers

Pete

Jurijs Velikanovs wrote:
>>> the code can be unwrapped

> Can you tell me more? I thought wrapped PL/SQL code is impossible to
> unwrap. It looks like I am out of date ;)
> 
> Yury
> 
> On Wed, Jun 3, 2009 at 7:55 PM, Pete Finnigan <pete_at_petefinnigan.com> wrote:

>> Hi Yury,
>>
>> Thanks for your response. Yes I saw Marks, solution. In regards to
>> yours, the code can be unwrapped and parsed of course but this is very
>> slow.
>>
>> cheers
>>
>> Pete
>>
>> Jurijs Velikanovs wrote:
>>> Hi Pete,
>>>
>>> 100% Agree with you.
>>> On the other hand if an object is wrapped there is not much you can do
>>> to address an issue yourself. The only option you probably have is to
>>> send problem report to a development organization.
>>> I have been working with EBS environments for last few years where
>>> most of the code is opened. We use the source$ (on pre-prod) often.
>>>
>>> Mark Bobak posted a excellent solution anyway. If a SQL is in SGA
>>> during an investigation time I would try X$ method next time, if it is
>>> post-problem time research i would try X$ (as it seams a lighter
>>> solution) and if not successful SOURCE$.
>>>
>>> Yury
>>>
>>> On Wed, Jun 3, 2009 at 12:56 AM, Pete Finnigan <pete_at_petefinnigan.com> wrote:
>>>> Hi Yury,
>>>>
>>>> That would only work if the code is not wrapped of course..:-(
>>>>
>>>> cheers
>>>>
>>>> Pete
>>>>
>>>> Jurijs Velikanovs wrote:
>>>>> Hi Kyle,
>>>>>
>>>>>> Is there a way to do this before 10.2.0.4?
>>>>> The only way I could think about is to search through the code:
>>>>> select --+ FULL(s) PARALLEL (s,8)
>>>>> OBJ#, LINE, SOURCE from sys.source$ s where 1=1
>>>>> and upper(s.source) like upper('%< good part of SQL you are looking for >%');
>>>>>
>>>>> I know that it isn't something that you asked for but it might help.
>>>>>
>>>>> Yury
>>>>>
>>>>> On Tue, Jun 2, 2009 at 7:58 AM, kyle Hailey <kylelf_at_gmail.com> wrote:
>>>>>> I want to correlate SQL to the packages and procedures they came from.
>>>>>> Is there a way to do this before 10.2.0.4?
>>>>>>
>>>>>> Starting in 10.2.0.4 this is pretty easy thanks to the fields
>>>>>>
>>>>>> PLSQL_ENTRY_OBJECT_ID
>>>>>> PLSQL_ENTRY_SUBPROGRAM_ID
>>>>>> PLSQL_OBJECT_ID
>>>>>> PLSQL_SUBPROGRAM
>>>>>>
>>>>>> in v$session and v$active_session_history. A nice output can be put
>>>>>> out using a script like
>>>>>>
>>>>>> http://www.perfvision.com/ash/ashpl2.sql
>>>>>>
>>>>>> to give
>>>>>>
>>>>>> COUNT(*) SQL_ID calling_code
>>>>>> --------- -------------
>>>>>> --------------------------------------------------------------------
>>>>>> 2 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_RANDOM.VALUE
>>>>>> 2 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_LOCK.SLEEP
>>>>>> 3 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_APPLICATION_INFO.SET_ACTION
>>>>>> 13 1xxksrhwtz3zf ORDERENTRY.NEWORDER
>>>>>> 76 dw2zgaapax1sg ORDERENTRY.NEWORDER
>>>>>> 131 75621g9y3xmvd ORDERENTRY.BROWSEANDUPDATEORDERS
>>>>>> 163 0uuqgjq7k12nf ORDERENTRY.NEWORDER
>>>>>>
>>>>>> (the count could be changed to %activity or average active sessions )
>>>>>>
>>>>>> Best
>>>>>> Kyle Hailey
>>>>>> http://oraclemonitor.com
>>>>>> --
>>>>>> http://www.freelists.org/webpage/oracle-l
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>> --
>>>>
>>>> Pete Finnigan
>>>> Director
>>>> PeteFinnigan.com Limited
>>>>
>>>> Specialists in database security.
>>>>
>>>> If you need help to audit or secure an Oracle database, please ask for
>>>> details of our courses and consulting services
>>>>
>>>> Phone: +44 (0)1904 791188
>>>> Fax : +44 (0)1904 791188
>>>> Mob : +44 (0)7742 114223
>>>> email: pete_at_petefinnigan.com
>>>> site : http://www.petefinnigan.com
>>>>
>>>> Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
>>>> Company No : 4664901
>>>> VAT No. : 940 6681 14
>>>>
>>>> Please note that this email communication is intended only for the
>>>> addressee and may contain confidential or privileged information. The
>>>> contents of this email may be circulated internally within your
>>>> organisation only and may not be communicated to third parties without
>>>> the prior written permission of PeteFinnigan.com Limited. This email is
>>>> not intended nor should it be taken to create any legal relations,
>>>> contractual or otherwise.
>>>>
>>>>
>>>
>>>
>> --
>>
>> Pete Finnigan
>> Director
>> PeteFinnigan.com Limited
>>
>> Specialists in database security.
>>
>> If you need help to audit or secure an Oracle database, please ask for
>> details of our courses and consulting services
>>
>> Phone: +44 (0)1904 791188
>> Fax : +44 (0)1904 791188
>> Mob : +44 (0)7742 114223
>> email: pete_at_petefinnigan.com
>> site : http://www.petefinnigan.com
>>
>> Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
>> Company No : 4664901
>> VAT No. : 940 6681 14
>>
>> Please note that this email communication is intended only for the
>> addressee and may contain confidential or privileged information. The
>> contents of this email may be circulated internally within your
>> organisation only and may not be communicated to third parties without
>> the prior written permission of PeteFinnigan.com Limited. This email is
>> not intended nor should it be taken to create any legal relations,
>> contractual or otherwise.
>>
>>

> 
> 
>

-- 

Pete Finnigan
Director
PeteFinnigan.com Limited

Specialists in database security.

If you need help to audit or secure an Oracle database, please ask for
details of our courses and consulting services

Phone: +44 (0)1904 791188
Fax  : +44 (0)1904 791188
Mob  : +44 (0)7742 114223
email: pete_at_petefinnigan.com
site : http://www.petefinnigan.com

Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
Company No       : 4664901
VAT No.          : 940 6681 14

Please note that this email communication is intended only for the
addressee and may contain confidential or privileged information. The
contents of this email may be circulated internally within your
organisation only and may not be communicated to third parties without
the prior written permission of PeteFinnigan.com Limited.  This email is
not intended nor should it be taken to create any legal relations,
contractual or otherwise.

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

Received on Thu Jun 04 2009 - 03:26:33 CDT

This message: [ Message body ]
Next message: Johnson, George: "RE: app is slow"
Previous message: Steve Baldwin: "Re: dbms_aq.dequeue - FIFO when using Correlation ID"
Maybe in reply to: kyle Hailey: "what package/procedure did SQL come from?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message