Oracle Tips and Tricks — David Fitzjarrell

June 2, 2008

That’s A Wrap

Filed under: General,pl/sql — dfitzjarrell @ 16:10

Security is at the top of most management To-Do lists these days, and keeping application code obscured from prying eyes can be a priority in some organizations. Usually that’s solved by the nature of the compiler-based application (where one writes source code, compiles and links that into an executable and then distributes that executable and its ancillary files to end users), but PL/SQL isn’t a compiled language, so how does one obfuscate the source code so it can’t be read or modified by those not authorized to do so? Oracle comes to the rescue with the wrap utility.

The wrap utility (an external program installed with the Oracle software) has been around for years and uses a proprietary algorithm to ‘scramble’ the source code so as to make it essentially unreadable by the human eye. To illustrate what the wrap utility can accomplish let’s look at a pair of files declaring a package specification and a package body. First, the package specification:

CREATE PACKAGE dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE )
        RETURN NUMBER;

    FUNCTION minutes_since_midnight
        ( timevalue DATE )
        RETURN NUMBER;

    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER;

END dates_pkg;
/

Nothing here the everyday user can’t see, so we’ll leave this one unscrambled. Now let’s look at the package body:

CREATE PACKAGE BODY dates_pkg
AS
    FUNCTION julian_date
        ( date_to_convert DATE)
        RETURN NUMBER
    IS
        varch_value VARCHAR (10);
        num_value NUMBER (20);
    BEGIN
 --
 -- First, we take a date and convert it to a date by converting it
 -- to a character string using the same format we will use to
 -- convert it BACK to a date again
 --
 -- Oh, then we convert it back to a character string
 --
 -- In Julian format, which is a number
 --
        SELECT TO_CHAR
               ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
               , 'J')
        INTO   varch_value
        FROM   dual;

 --
 -- Okay, so we had a Julian date as a number but we changed it to
 -- a character string so we could go back and make it a ...
 -- NUMBER ... again
 --
        SELECT TO_NUMBER (varch_value)
        INTO   num_value
        FROM   dual;

 --
 -- So, we finally make up our mind and keep it a number and
 -- return it from the function
 --
        RETURN (num_value);
    END julian_date;


    FUNCTION minutes_since_midnight (
        timevalue DATE)
        RETURN NUMBER
    IS
        secs_elapsed NUMBER (20);
        mins_elapsed NUMBER (20);
    BEGIN
 --
 -- So now we take a date and extract the time portion of it,
 -- convert that BACK to a date, then convert THAT to a string
 -- of seconds and convert THAT to a number
 --
 -- Is it me, or are we essentially driving across town just to
 -- go next door?
 --
        SELECT TO_NUMBER
               ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
               , 'SSSSS') )
        INTO   secs_elapsed
        FROM   dual;

 --
 -- Oooo, now we divide that total number of seconds by ...
 -- wait for it ...
 -- any second now ...
 -- 60!  Who would have thought that 60 seconds equals
 -- one minute?
 --
        SELECT (secs_elapsed / 60)
        INTO   mins_elapsed
        FROM   dual;

 --
 -- Before we rest on our laurels we return the minutes since midnight
 --
        RETURN (mins_elapsed);
    END minutes_since_midnight;


    FUNCTION minutes_elapsed
        ( lowdate DATE
        , highdate DATE )
        RETURN NUMBER
    IS
        final_number NUMBER (20);
        low_julian NUMBER (20);
        high_julian NUMBER (20);
        num_days NUMBER (20);
        num_minutes NUMBER (20);
        temp_mins NUMBER (20);
        min_low NUMBER (20);
        min_high NUMBER (20);
    BEGIN
 --
 -- Now, why didn't we use this julian_date function in the
 -- last installment of Julian conversions?
 --
 -- Oh, yeah, because we just WROTE that wonderful function
 --
 -- So, okay, we take our date values and return the Julian
 -- representations of them using all of the mathematical
 -- aerobics from earlier
 --
 -- I guess this is so much easier than simply subtracting
 -- them
 --
        SELECT julian_date (lowdate)
        INTO   low_julian
        FROM   dual;

        SELECT julian_date (highdate)
        INTO   high_julian
        FROM   dual;

 --
 -- Woo-hoo! Higher math time!  Subtract the Julian dates
 -- and get the number of days
 --
 -- Isn't that what we'd get if we just subtracted the
 -- submitted dates as-is?
 --
 -- Of course it is
 --
        SELECT (high_julian - low_julian)
        INTO   num_days
        FROM   dual;

 --
 -- Now we calculate the total minutes elapsed
 -- using our values generated by our extreme
 -- gyrations
 --
 -- I'm out of breath just thinking about all of this work
 --
        SELECT (num_days * 1440)
        INTO   num_minutes
        FROM   dual;

 --
 -- And now we put those other mathematical moves
 -- to use
 --
 -- Tell me again why we think we're smarter than
 -- the average bear?
 --
        SELECT minutes_since_midnight (lowdate)
        INTO   min_low
        FROM   dual;

        SELECT minutes_since_midnight (highdate)
        INTO   min_high
        FROM   dual;

 --
 -- Now this is disgusting
 --
 -- Using a TEMP variable to aid in simple mathematical
 -- processing
 --
        SELECT (min_high - min_low)
        INTO   temp_mins
        FROM   dual;

 --
 -- And this is better than:
 -- select (end_date - start_date)*1440 because?
 --
        SELECT (num_minutes + temp_mins)
        INTO   final_number
        FROM   dual;

        RETURN (final_number);

    END minutes_elapsed;
END dates_pkg;
/

There are some areas here which would be good to obscure (such as the actual program logic and my dripping sarcasm). Let’s run this through wrap and see what results:

wrap iname=datepkg.pls

PL/SQL Wrapper: Release 10.2.0.3.0- Production on Mon Jun 02 12:04:26 2008

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing datepkg.pls to datepkg.plb

(Note that wrap requires only an input file name; it will supply the output file name from the input file and change the extension to plb. You can, of course, supply an output file name using the oname parameter:

wrap iname=myfile.sql oname=yourfile.plq

and the wrap utility will be perfectly happy. I prefer to accept the default behaviour.)

So now we look at the contents of datepkg.plb and see what wrap hath wrought:

CREATE PACKAGE BODY dates_pkg wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
8fd 2ea
HqtWzGgdR01NBtyeAlYcu2V4y4Awg5DDLtAFyi/NDfmUx4K5yY1+DfxrrfMT24qrdohuXNxN
CQocIyZ3+aqU08q0OcZMwOM4QHgsu3+AcbwGHTdMupDu+MSdJPlNmp0/yVSRDrG8wWMaYgec
vGt1Cp4MwH91At1+jX5cMZu2KV0BDzASgJGaUo8ABfe5wPICeEG46jz8RhNBcZ1SW33eXn7t
B7wXzeo2Z0+QQoDLiRcsSSS/XMhAidNZyauxLPg0Da8aGNgdqz0tl4i66j2S2OqHdL8LS5Rd
lq8+Q8KkK8Eu+OIBkmT8UgPXybefgTkWt+e82r+lgJHKqnJh8aXFFOqadtNyFrts8/Jph9OV
EZUpa6epeksaX3HekigzRD4CZrIingkuKSoMxpAI45nKPfpNptHb0AKPbZNDMnONzN1H548z
pGsPdY0ffGsWVpvinmY6jDWcPKUBgr3zn97f21Q57pVnvVJht1EEgGBQoCDoa3wEjtVNKl8k
BwJmMV5K/f4EUWazs3dcNFKYvdoecIeEAnJmeQ1j1KQUOICGqPGrK3uVoLNdxM9FbJHTNIdn
YbW2a2h7jcj6dk0LIKyr0r/33QtmKK0jdJtf3HpqPZhB8UmStnoAJCBRj6f8/o+w6JEYywpf
AXEa3begV95iTZFoSWSXgJyGNlVt+cvPX+H+7BxIFrbkpTGa3sgTye6NFYNqUsnBd9+CB+gB
5qQPKAUotaZBuVV7

/

It doesn’t look the same as the source code, does it. The wrap utility was provided to do just that, obscure PL/SQL code/logic so the end user can’t see it. Installing either of the package bodies (datepkg.pls or datepkg.plb) results in the same functional code in the database, it’s just that the datepkg.plb script can’t be decoded by anyone except Oracle (and, in any of the SOURCE views [DBA_SOURCE, USER_SOURCE] all that is revealed is the wrapped text). That, of course, means THERE IS NO ORACLE-SUPPLIED OR ORACLE SUPPORTED UNWRAP UTILITY; you’ll need to save your original source code in the event you need to modify it due to bug fixes (heaven forbid) and/or functionality changes since you won’t get it back from the wrapped file.

How do you execute a .plb file? Pretty much like you’d run any other .sql script:

SQL> @datepkg
SQL> CREATE OR REPLACE PACKAGE dates_pkg
  2  AS
  3      FUNCTION julian_date
  4          ( date_to_convert DATE )
  5          RETURN NUMBER;
  6
  7      FUNCTION minutes_since_midnight
  8          ( timevalue DATE )
  9          RETURN NUMBER;
 10
 11      FUNCTION minutes_elapsed
 12          ( lowdate DATE
 13          , highdate DATE )
 14          RETURN NUMBER;
 15
 16  END dates_pkg;
 17  /

Package created.

SQL>
SQL> @datepkg.plb
SQL> CREATE or replace PACKAGE BODY dates_pkg wrapped
  2  a000000
  3  b2
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  b
 20  8fd 2ea
 21  HqtWzGgdR01NBtyeAlYcu2V4y4Awg5DDLtAFyi/NDfmUx4K5yY1+DfxrrfMT24qrdohuXNxN
 22  CQocIyZ3+aqU08q0OcZMwOM4QHgsu3+AcbwGHTdMupDu+MSdJPlNmp0/yVSRDrG8wWMaYgec
 23  vGt1Cp4MwH91At1+jX5cMZu2KV0BDzASgJGaUo8ABfe5wPICeEG46jz8RhNBcZ1SW33eXn7t
 24  B7wXzeo2Z0+QQoDLiRcsSSS/XMhAidNZyauxLPg0Da8aGNgdqz0tl4i66j2S2OqHdL8LS5Rd
 25  lq8+Q8KkK8Eu+OIBkmT8UgPXybefgTkWt+e82r+lgJHKqnJh8aXFFOqadtNyFrts8/Jph9OV
 26  EZUpa6epeksaX3HekigzRD4CZrIingkuKSoMxpAI45nKPfpNptHb0AKPbZNDMnONzN1H548z
 27  pGsPdY0ffGsWVpvinmY6jDWcPKUBgr3zn97f21Q57pVnvVJht1EEgGBQoCDoa3wEjtVNKl8k
 28  BwJmMV5K/f4EUWazs3dcNFKYvdoecIeEAnJmeQ1j1KQUOICGqPGrK3uVoLNdxM9FbJHTNIdn
 29  YbW2a2h7jcj6dk0LIKyr0r/33QtmKK0jdJtf3HpqPZhB8UmStnoAJCBRj6f8/o+w6JEYywpf
 30  AXEa3begV95iTZFoSWSXgJyGNlVt+cvPX+H+7BxIFrbkpTGa3sgTye6NFYNqUsnBd9+CB+gB
 31  5qQPKAUotaZBuVV7
 32
 33  /

Package body created.

SQL>

In 10g and later releases of Oracle a second method of wrapping PL/SQL source code is available with the DBMS_DDL.WRAP function. This is designed for use with dynamic PL/SQL statements:

declare
     ddl varchar2(32767);
begin
     ddl := 'create or replace procedure ...';
     execute immediate dbms_ddl.wrap(ddl);  -- 'Wraps' the procedure then executes it
end;
/

[There is a known bug with dbms_ddl.wrap and multibyte character sets (like Japanese) in base release 10.2.0.1; bug 4577670 causes an ORA-22921 error to be generated. This is fixed in patchset 10.2.0.2 and in release 11.1.0.6.]

If you’re concerned with unauthorized persons viewing sensitive PL/SQL source code then the wrap utility is probably something you should investigate. Remember, though, that you CAN’T* ‘unwrap’ the wrapped code later, so save your original source code somewhere safe for when you next need it.

* There are third-party unwrap tools for 9iR2 and earlier releases of Oracle, but, again, these are not supported by Oracle Corporation. As a result of this the wrap mechanism in 10g and later releases has changed and these tools will not work on wrapped code in any release after 9.2.0.x. You’re welcome to search google.com for them; I’ll not supply any links to them here.

And, that’s a wrap.

Advertisements

4 Comments »

  1. Hello everybody,First I would like to apologize my english, I am not a native speaker, so I may be writting something wrong…My problem has to do with crypticing procedures and functions on Oracle database. I need to be sure noone can see the code.I have already used and tried the Oracle app "Wrapper", however I haven't been successful.I did cryptic the procedures, but one of my co-workers could see the the code, that is, the wrapper did not work.So, what could I do? I do need to prevent other people from seeing the code, which means everyone, even a DBA.Is there any other way to do it?Will I have to invent a new one??Thank you very much for your time.And if it is possible add me on MSN: felipe_getup@hotmail.comor e-mail me: filipe.miranda@totvs.com.br

    Comment by Anonymous — June 2, 2011 @ 02:00 | Reply

  2. As mentioned in the post there ARE 'utilities' that can 'unwrap' such code; none are sactioned by Oracle but are available on the web. Nothing is fool-proof, not even wrap. A problem with your proposed solution is that the text will be obscured but will need to be brought back into clear text to get Oracle to actually use it (only obscured code produced by the wrap utiility can be installed in oracle as there is an internal 'unwrap' functionality built into the Oracle kernel).Generally speaking wrapped code is unreadable to users; though there are ways to 'unwrap' it use of such 'tools' should be considered as security issues and those using them without internal authorization should be dealt with accordingly.My two cents.

    Comment by d_d_f — June 5, 2011 @ 21:18 | Reply

  3. […] can also create a sequence which cycles (or ‘wraps’, but since Oracle provides a wrap utility I’d prefer to not use that terminology for such a […]

    Pingback by Out Of Sequence « Oracle Tips and Tricks — July 23, 2012 @ 08:54 | Reply

  4. […] developers (and the development team hasn’t obscured the package or procedure code with the wrap utility) it may be necessary to look at that code and see exactly what did generate the error. Of […]

    Pingback by "I’ve never seen THAT error before …" « Oracle Tips and Tricks — July 23, 2012 @ 09:13 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: