The wrap utility (wrap

wrap

The wrap utility (wrap.exe) provides a way for PL/SQL developers to protect their intellectual property by making their PL/SQL code unreadable. These encryption options have long been available for other programming languages and were introduced for PL/SQL in version 7. It still amazes me at the number of proprietary procedures and packages that are installed in a readable format – plain PL/SQL.

Unfortunately there is no such command as:

ALTER PACKAGE BODY [name] WRAP;

Instead, the wrap utility takes a readable, ASCII text file as input and converts it to a file containing byte code. The result is that the DBA, developers or anyone with database access cannot view the source code in any readable format.

The command line options for wrap are:

wrap iname=[file] oname=[file]

· iname – The name of the unencrypted PL/SQL file to be used as input (your source file).

· oname – The name of the output file. This file will be encrypted.

Below is a sample procedure that increases an employee’s salary by 15%, if they scored a 5 in their performance rating:

create or replace procedure give_raise

(emp_id in employee.id%type,

emp_rating in NUMBER )

BEGIN

-- raises are only given to those that scored a 5

-- in their rating

if emp_rating = 5 then

update employee

set salary = salary + (salary * .15)

where id = emp_id;

end if;

END ;

Once added to the dictionary, the data dictionary views display the source code for this procedural object:

SQL> select text from user_source where name = 'GIVE_RAISE';

TEXT

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

procedure give_raise

(emp_id in employee.id%type,

emp_rating in NUMBER )

BEGIN

-- raises are only given to those that scored a 5

-- in their rating

if emp_rating = 5 then

update employee

set salary = salary + (salary * .15)

where id = emp_id;

end if;

END ;

13 rows selected.

Any user that has been granted the DBA privilege can see the contents of the procedure (dba_source), including in this case, the company formula for raises. To prevent this, the stored procedure needs to be wrapped and replaced in the database.

C:\oracle9i\bin>wrap iname=giveraise.sql oname=give_raise_encrypted.sql

PL/SQL Wrapper: Release 9.2.0.1.0- Production on Sun Dec 08 14:28:41 2002

Processing giveraise.sql to give_raise_encrypted.sql

Once wrapped, the procedure can be resubmitted to the database:

SQL> @c:\oracle9i\bin\give_raise_encrypted.sql

Procedure Created.

Now, another query against user_source shows the newly wrapped code:

SQL> select text from user_source where name = 'GIVE_RAISE';

TEXT

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

procedure give_raise wrapped

abcd

0 9a 8f :2 a0 6b :2 a0 f b0

3d 8f a0 b0 3d b4 55 6a

a0 7e 51 b4 2e :3 a0 7e 51

b4 2e e7 :2 a0 7e b4 2e ef

f9 e9 b7 19 3c b7 a4 b1

11 68 4f 1d 17 b5

...

Notice the output of the query above appears like garbage, or at least not readable code. Nothing except Oracle.exe can now read the logic for generating raises.

Best Practices for Using Wrap

· Always wrap code that contains sensitive information or commercial software that is owned and distributed by your company. The give_raise procedure is highly sensitive and should not reveal the code to anyone that can access a DBA view.

· Although the wrap utility does in fact work in a straightforward manner, it will not work when wrapping code that is specific to a version of the database. For instance, our example above would wrap fine in version 7, and the same encrypted output can be used in 9i. But, if the code contains PL/SQL commands specific to a version of the database (execute immediate), then the wrap executable must be at least at that level of the database.

· Wrapping a procedure in 9i will not compile when submitted to an Oracle7 database. For the same reason that a file created in Word/XP cannot be loaded into Word95, newer versions of wrap only work with that version of the database. The wrap utility does have a “loose” connection to the database, although it does not ask for one (username, password, SID). Attempting to wrap code that will not compile, will result in errors like the one below:

C:\oracle9i\bin>wrap iname=giveraise.sql oname=giveraise.wrp

PL/SQL Wrapper: Release 9.2.0.1.0- Production on Sun Dec 08 15:42:23 2002

Processing giveraise.sql to giveraise.wrp

PSU(103,1,8,1):Encountered the symbol "IF" when expecting one of the following:

constant exception <an identifier>

<a double-quoted delimited-identifier> table LONG_ double ref

char time timestamp interval date binary national character

nchar

PL/SQL Wrapper error: Compilation error(s) for:

create or replace procedure give_raise

Outputting source and continuing.

It would seem to make sense to just wrap all code with the oldest version of the wrap utility, but that will not work. For example, trying to wrap a procedure that contained a version specific command (like execute immediate) would require that specific version of the wrap executable. In fact, it is much easier to wrap a file on each version of the database that you plan to support. Also, code that is wrapped is portable to any platform. Therefore, PL/SQL code could be wrapped on Windows and distributed to any UNIX platform.

· Give careful consideration to wrapping code since it increases the size of the procedural object (function, procedure, and package) by as much as 200-250%. The size of the wrapped object is the only down side to wrapping; the execution benchmarks are the same.

· Do not wrap package specifications (headers), since they serve as great documentation. Good development practice is to only wrap the implementation, the package body.

· Provide a version of the wrap utility for developers to use. Since $ORACLE_HOME/bin is usually very restricted, copy the wrap executable to a shared drive that everyone can use.

No utility exists that will unwrap a wrapped package; otherwise, the wrap utility would be useless.

Now that encryption is addressed, the next step for a developer would be to ensure that the code performs well. Developers, to gain code execution benchmarks, can use the dbms_profiler utility described in the next section.