Donald K. Burleson Oracle Utilities Tips

PL/SQL Wrap Utility for Encryption

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:\Oracle\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

Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.

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 can use the dbms_profiler utility described in the next section to gain code execution benchmarks
 

To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.