PL/SQL Wrap Utility and DBMS_DDL.WRAP Function to Encrypt your PL/SQL Code

Often developers want to wrap their code to prevent the misuse of their code and also sometimes they don’t want to expose their algorithm to the world. For such cases Oracle has come up with a utility called ‘wrap utility’ (wrap.exe) which 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.

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.

How to run?

To run the Wrap Utility, enter the wrap command at your operating system prompt using the following syntax:

wrap iname=input_file [oname=output_file]

Where

• 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.

Note:

• Leave no space around the equal signs because spaces delimit individual arguments.
• The wrap command requires only one argument, which is iname=input_file where input_file is the name of the Wrap Utility input file. You need not specify the file extension because it defaults to sql.
• However, you can specify a different file extension such as ‘wrap iname=/mydir/myfile.src’
• Optionally, the wrap command takes a second argument, which is oname=output_file where output_file is the name of the Wrap Utility output file. You need not specify the output file because its name defaults to that of the input file and its extension defaults to plb (PL/SQL binary).
• Generally, the output file is much larger than the input file.
• The input file can contain any combination of SQL statements. However, the Wrap Utility encrypts only the CREATE statements, which define subprograms, packages, or object types.
• If your input file contains syntax errors, the Wrap Utility detects and reports them. However, the Wrap Utility cannot detect semantic errors because it does not resolve external references.

To test the Wrap Utility, let’s first create a procedure (test_wrap_proc.sql)

CREATE OR REPLACE PROCEDURE test_wrap_proc
AS
  CURSOR c_languages
  IS
  SELECT ISO_LANGUAGE,NLS_TERRITORY
  FROM FND_LANGUAGES;
BEGIN
  DBMS_OUTPUT.PUT_LINE('LANGUAGE'||'---'||'TERRITORY');
  DBMS_OUTPUT.PUT_LINE('----------------------------');
  FOR r_languages IN c_languages LOOP
  DBMS_OUTPUT.PUT_LINE(r_languages.ISO_LANGUAGE||'---'||r_languages.NLS_TERRITORY);
  END LOOP;
END test_wrap_proc;

Then go to the command prompt and run the Wrap Utility as shown below.

It will create an encrypted file (test_wrap_proc.plb) as shown below.

CREATE OR REPLACE PROCEDURE test_wrap_proc wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
181 10b
4hl/uWH5YOAzmM6zJquzeGEGdNAwgxBpDJ5qyi/Ng15TAwLsmVNle72sUpHD8uqHGgdkod4n
bybKy04ihhNaEd1v4UGoQ5LKpPZ+AOi2hmiO8lpow2RqcdJ09MTiums2HFlIKrmPAM7Dgi++
X9J+bvS47l5LJL7g70fRIUdKDOVUwFZCEcQjH4/FvKIII70+Q5KKQAbo3DKbcjp96KfWRXbR
duBC2zCLGfpAIrv5xkG4tK/fiKxVipS6CobsPUOy3ioBPA==

/

To run and view the results of this encrypted file, go to SQL*Plus and compile the procedure.

Oracle has given few Guidelines for Wrapping as below:

• Wrap only the body of a package or object type, not the specification. This allows other developers to see the information they must use the package or type, but prevents them from seeing its implementation.
• Wrap code only after you have finished editing it. You cannot edit PL/SQL source code inside wrapped files. Either wrap your code after it is ready to ship to users or include the wrapping operation as part of your build environment. To change wrapped PL/SQL code, edit the original source file and then wrap it again.
• Before distributing a wrapped file, view it in a text editor to be sure that all important parts are wrapped.

There are few Limitations also:

• Wrapping is not a secure method for hiding passwords or table names.
• Wrapping does not hide the source code for triggers. To hide the workings of a trigger, write a one-line trigger that invokes a wrapped subprogram.
• Wrapping does not detect syntax or semantic errors.
• Wrapped PL/SQL units are upward-compatible between Oracle Database releases, but are not downward-compatible. For example, you can load files processed by the V8.1.5 wrap utility into a V8.1.6 Oracle Database, but you cannot load files processed by the V8.1.6 wrap utility into a V8.1.5 Oracle Database.

Using DBMS_DDL for Obfuscation:

In prior releases you have been able to use the wrap utility to obfuscate code. However any program unit created dynamically by using EXECUTE IMMEDIATE or DBMS_SQL is not automatically wrapped. This need has been satisfied in the Oracle10gR2.

The DBMS_DDL package contains the WRAP and   CREATE_WRAPPED   methods for obfuscating a single PL/SQL unit that can only be a package specification, package body, function, procedure, type specification, or type body. 

The wrap functions provide a mechanism for obfuscating dynamically generated PL/SQL program units that are created in a database.

Benefits of Dynamic Obfuscation:

Added security to dynamically generated code.

• Ability to create wrapped code without compiling the program unit
• Ability to create obfuscated program units on the fly using end-user specification

Use the overloaded WRAP function with EXECUTE IMMEDIATE to create the wrapped code, as the following example illustrates:

DECLARE
  v_Procedure VARCHAR2(4000);
BEGIN
  v_Procedure :=  'CREATE OR REPLACE PROCEDURE test_wrap_proc '
                  || 'AS '
                  || 'CURSOR c_languages '
                  || 'IS '
                  || 'SELECT ISO_LANGUAGE,NLS_TERRITORY '
                  || 'FROM FND_LANGUAGES; '
                  || 'BEGIN '
                  || 'FOR r_languages IN c_languages LOOP '
                  || 'DBMS_OUTPUT.PUT_LINE(r_languages.ISO_LANGUAGE||r_languages.NLS_TERRITORY); '
                  || 'END LOOP; '
                  || 'END test_wrap_proc; ';
  EXECUTE IMMEDIATE DBMS_DDL.WRAP(v_Procedure);
END;

To see the wrapped procedure, select the text from the USER_SOURCE view.

SELECT text
FROM user_source WHERE lower(name) = 'test_wrap_proc';

Now the million dollar question is– Can we unwrap a PL/SQL Code?

Please note: there is no legal way to unwrap a *.plb binary file (as said by Oracle). You are supposed to backup and keep your source files after wrapping them. However this world is filled with lots of skilled hackers and it is not impossible to unwrap PL/SQL Code. If you are more interested, you can check out the below links which contain very interesting discussion about this topic.

• Forum.oracle.com:Thread: How to un wrap a package
• Unwrapping 10G wrapped PL/SQL
• AskTom
• MOS Note: Wrap Utility – Unwrap PL/SQL Code? [ID 376303.1]

Reference:

Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1)

Thanks

Dibyajyoti Koch