#

| Chapter 23 Managing Code in the Database |

## 23.7 Encrypting Stored Code

It's kind of fun to watch a programming language like PL/SQL evolve over time. First there was Version 1.0 -- very useful for batch processing scripts, but not much else. And very buggy. Then with Version 2.0 we started to see the real promise of the language. Stored program units, packages, and more. Why, it was so useful that third-party vendors began to write applications in PL/SQL. And when they sold these applications to customers, they shipped them their software for installation -- and then ran smack into a depressing discovery: there wasn't any way to hide their proprietary formulas, knowledge, and plain hard work from the eyes of competitors!

To install a PL/SQL-based application in another database instance, you must CREATE OR REPLACE those program units from the source code. Source code must, therefore, be supplied to each customer. "Wake up, Oracle!" came the cry from value-added resellers (VARs), "we can't let everyone see our secrets." Mostly in response to this basic need of VARs, Oracle added (with PL/SQL Release 2.2 and beyond) the ability to encrypt or "wrap" source code.

When you encrypt PL/SQL source (I prefer that word to "wrap," since the concept of wrapping code is common to many languages, but generally means "encapsulation"), you convert your readable ASCII text source code into unreadable ASCII text source code. This unreadable code can then be distributed to customers or regional offices or whatever for creation in new database instances. It is as portable as your original PL/SQL code, and is included in imports and exports. The Oracle database maintains dependencies for this encrypted code as it would programs compiled from readable text. In short, an encrypted program is treated within the database just as normal PL/SQL programs are treated; the only difference is that prying eyes can't query the USER_SOURCE data dictionary to extract trade secrets.

### 23.7.1 How to Encrypt Code

To encrypt PL/SQL source code, you run the wrap executable. This program may be found in the bin directory of the Oracle instance. In UNIX, this directory is located at $ORACLE_HOME/bin. In Windows NT, you can cd to c:\OraNT\bin, where "c:" denotes the drive on which Oracle has been installed. You will then find in your bin directory a program whose name has this format: wrapNN.exe, where NN is the version number of the database. So if you have Oracle 7.3 installed, you will have a wrap73.exe file in the bin directory.

The format of the encryption command is:

>     wrapNN iname=readablefile [oname=encryptedfile]

where NN is your version number (72, 73, 80, etc.), readablefile points to the original, readable version ("in" file) of your program, and encryptedfile is the name of the file which will contain the encrypted version of code ("out" file).

If readablefile does not contain a file extension, then the default of sql is assumed.

If you do not provide an oname argument, then wrapNN creates a file with the same name as readablefile, but with a default extention of plb, which stands for "PL/SQL binary" (a misnomer, but it gets the idea across: binaries are unreadable).

Here are some examples of using the wrap73 executable:

1. Wrap a program relying on all of the defaults:

   >     c:\orant\bin\wrap73 iname=secretprog

2. Wrap a package body, specifying overrides of all the defaults. Notice that the encrypted file does not have to have the same filename or extension as the original:

   >     c:\orant\bin\wrap73 iname=secretbody.spb oname=shhhhhh.bin

### 23.7.2 Working with Encrypted Code

I have found the following steps to be useful in working with encrypted code:

- Establish standard file extensions which clearly identify encrypted code. I use the following extensions:

Expression | Contents
--- | ---
sps | Readable package specifications
spb | Readable package bodies
pls | Encrypted package specifications
plb | Encrypted package bodies

- In Windows NT and Windows 95, you will have to open an MS-DOS window and then execute the wrapNN command from there. My suggestion is that you do not execute the program from within the Oracle bin directory, but instead cd to the directory containing your source code and execute the wrapNN.exe file from there.

- Create batch files so that you can easily, quickly, and uniformly encrypt one or more files. In Windows NT, I create bat files in the directories containing my source code which contain lines like this:

  >     c:\orant\bin\wrap73 iname=plvrep.sps oname=plvrep.pls

  Of course, you can also create parameterized scripts and pass in the names of the files you want to encrypt.

### 23.7.3 Impact of Encrypting Code

There are several points to consider as you move to encrypting your PL/SQL code base:

- If you are an Oracle VAR, you probably are supporting multiple Oracle versions, from 7.0 or 7.1 to Oracle 8.x. If you are a lucky Oracle VAR, you will have (in many cases) one version of PL/SQL code which will work across all of these versions. If this is the case, then you can decide whether you want to have different encrypted versions of that same program for each Oracle release. You can do this (execute wrap71, wrap72, etc., for each of your versions) or you can simply encrypt at the highest version number (say, wrap 80). This most recent encryption will compile in earlier versions. Earlier versions of encrypted code will not, however, compile properly in later versions of Oracle.

- To encrypt your source code, you must place that code in an operating system file. If you are working within a PL/SQL development environment which allows you to build and maintain source directly in the database, you will have to "dump" this code to a file, wrap it, and then compile it back into the database -- thereby wiping out your original, readable, and maintainable source code. This is not an issue as you deploy software to customers, but it could cause some uncomfortable situations as you develop and maintain applications.

- You can only encrypt package specifications, package bodies, and standalone functions and procedures. You can run the wrapNN binary against any other sort of SQL and PL/SQL statement, but those files will not be changed.

- You can tell when a program is encrypted by examining the program header. If there are no comments in the program header, then you will see this text in the first line of USER_SOURCE for a encrypted package body:

  >     PACKAGE BODY <package_name> WRAPPED

  Even if you don't notice the keyword WRAPPED on the first line, you will immediately know that you are looking at encrypted code because the text in USER_SOURCE will look like this:

  >        LINE TEXT
  >     ------- ----------------------
  >          45 abcd
  >          46 95a425ff
  >          47 a2
  >          48 7 PACKAGE:

  and you know that no matter how bad your coding style, it surely isn't that bad.

- Comment lines are removed from the encrypted program (who's going to read them?) except for comment text which appears in the header of the program definition. That is, any comments that appear before the AS or IS keywords are preserved. This allows you to provide documentation on usage and copyright to all users of your software without revealing any proprietary information. For example, the following program description will appear in readable format in USER_SOURCE:

  >     CREATE OR REPLACE PACKAGE
  >     /*
  >     || Author: Steven Feuerstein
  >     || Overview: Collect all financial calcs together.
  >     */
  >     financials WRAPPED

  I have found that when using large, complex comment blocks, the wrapNN program (at least through wrap73) either rejects valid source code for encryption, or encrypts successfully but then fails to compile. You may find that you need to simplify or shorten your standard headers when using the wrap utility.

- Encrypted code is much larger than the original source. I have found in my experience that a 57KB readable package body turns into a 153KB encrypted package body, while a 86KB readable package body turns into a 357KB encrypted package body. These increases in file size do result in increased requirements for storing source code in the database. The size of compiled code stays the same.

> NOTE: As of fall 1997, no one has yet admitted to having been able to (or bothering to) crack the encryption of wrapped PL/SQL code. But don't get your hopes up too high!

23.6 Using SQL to Examine Stored Objects |  | 24. Debugging PL/SQL

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.