OracleBlog

There are two broad categories of reasons why PL/SQL programmers wouldn't want others to read their code:

Reason #1 Protection
The programmers wish to protect their code from theft, misuse or alteration (among other things).

Reason #2 Malicious Reasons
The code is meant to do harm, or may have some potentially dangerous errors they wish to cover up.

I don't believe in hidden code, because open code is easier to debug, reuse, and it is obviously easier to detect (deliberately or accidentally) harmful code.

But regardless of whether code you have received from a vendor or contractor is protected or not, you should be testing it thoroughly before using it anyway. Plus, these methods are already thoroughly documented in a number of places, not the least of which by expert Steven Feuerstein and also in Oracle's documentation.

In fact, the example I'm using is from Oracle's documentation.

Normally, after creating a stored procedure, the source code can be access through a SOURCE table, like so:

SQL> SELECT text FROM USER_SOURCE
SQL> WHERE name = 'WRAPTEST' order by line;

TEXT
-----------------------------------------------------------

PROCEDURE wraptest IS
TYPE emp_tab IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
all_emps emp_tab;
BEGIN
SELECT * BULK COLLECT INTO all_emps FROM emp;
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).empno);
END LOOP;
END;

How can we avoid this?

My first thought is simply not to load the procedure into the database at all. Leave the procedure as an SQL file on the server, and execute it remotely. About a year ago, I engaged some Oracle programmers in such an approach, check that earlier article for a brief discussion on the advantages and disadvantages of keeping your code out of the database:
PL/SQL Code Storage: Files vs In-DB Packages

Anyone with access to the server will can still read your code, using an editor of some kind. But perhaps your server is capable of restricting access to your satisfaction.

You'll note that those Oracle programmers largely agreed that if your code is in the database, it should be wrapped and/or part of a package. First let's look at wrapping.

Wrapping, otherwise known as obfuscation, is a method of changing your human-readable PL/SQL code into text that is readable only by Oracle. In that sense, it can be compiled and executed like any other code, but it is protected in the sense that it won't make sense to anyone except Oracle.

You can learn how to use the wrap utility from either Dan Morgan, or Oracle's PL/SQL User's Guide and Reference (Appendix C), but I'll show you what I mean right now.

1. Put your PL/SQL code in an SQL file.
2. From the command line, issue the wrap command. The syntax is wrap iname=input_file [oname=output_file]

wrap iname=wraptest.sql oname=wraptest.plb

3. From SQL*Plus, execute the resulting PLB file to load the stored procedure, and even execute it for verification (omitted here).

SQL> @wraptest.plb

Procedure created.

4. Try to read the code, either by opening the PLB file, or querying a SOURCE table. PLB is a standard Oracle extension, sometimes referred to as "PL/SQL Binary." But it isn't binary, it's text: you can go ahead and read it.

SQL> SELECT text FROM USER_SOURCE WHERE name = 'WRAPTEST' order by line;

(garbage removed)

Now is wrapping a perfect solution? Well no. First of all, Oracle doesn't claim that it is undecipherable. Edited: In fact, leading Oracle security expert Pete Finnigan has made a presentation available that summarizes how to read wrapped code.

It also has a few annoying limitations, none of which I'll prove here, but they include the fact that you can't wrap triggers (just call a wrapped proc from your trigger), can't be copied to an earlier release, and all Oracle comments (--) are deleted.

I hear there may be a superior solution in Oracle 10, as part of the DBMS_DDL package. That solution doesn't even require a command-line step, it can be done directly in the database. If you're ahead of me, and using Oracle 10, give it a try using Dan Morgan's reference for the syntax.

Speaking of packages, the more important lesson to learn from those Oracle programmers was to put your code into packages. You can make the specification public, so everyone can understand them, and then restrict access (and wrap) the implementation details.

Using packages and wrapping your code is the standard method of protection. As a closing note, there are plenty of other compelling reasons to use Oracle packages too. I wrote about that once before, and also included some links where you can get the finer details on how to use them:
Oracle Packages - And why/how you should use them.