A Obfuscating PL/SQL Source Code

Tips When Obfuscating PL/SQL Units

When obfuscating (wrapping) PL/SQL units note the following:

• When wrapping a package or object type, wrap only the body, not the specification. This allows other developers see the information they need to use the package or type, but they cannot see its implementation.

• PL/SQL source inside wrapped files cannot be edited. To change wrapped PL/SQL code, edit the original source file and wrap it again. You can either hold off on wrapping your code until it is ready for shipment to end-users, or include the wrapping operation as part of your build environment.

• To be sure that all the important parts of your source code are obfuscated, view the wrapped file in a text editor before distributing it.

Limitations of Obfuscation

The following are limitations when obfuscating PL/SQL source code:

• Although wrapping a compilation unit helps to hide the algorithm and makes reverse-engineering difficult, Oracle Corporation does not recommend it as a secure method for hiding passwords or table names. Obfuscating a PL/SQL unit prevents most users from examining the source code, but might not stop all attempts.

• The wrapping does not obfuscate the source code for triggers. To hide the workings of a trigger, you can write a one-line trigger that calls a wrapped procedure.

• Wrapping only detects tokenization errors, such as a runaway string, when obfuscating PL/SQL code. Wrapping does not detect syntax or semantic errors, such as tables or views that do not exist. Those errors are detected during PL/SQL compilation or when executing the output file in SQL*Plus.

• Obfuscated PL/SQL program units cannot be imported into a database of a previous (lower) release. Wrapped compilation units are upward-compatible between Oracle 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.

Input and Output Files for the PL/SQL wrap Utility

The input file can contain any combination of SQL statements. Most statements are passed through unchanged. CREATE statements that define subprograms, packages, or object types are obfuscated; their bodies are replaced by a scrambled form that the PL/SQL compiler understands.

The following CREATE statements are obfuscated:

The CREATE [OR REPLACE] TRIGGER statement, and [DECLARE] BEGIN..END anonymous blocks, are not obfuscated. All other SQL statements are passed unchanged to the output file.

All comment lines in the unit being wrapped are deleted, except for those in a CREATE OR REPLACE header and C-style comments (delimited by /* */).

The output file is a text file, which you can run as a script in SQL*Plus to set up your PL/SQL procedures, functions, and packages. Run a wrapped file as follows:

SQL> @wrapped_file_name.plb;

Running the wrap Utility

For example, assume that the wrap_test.sql file contains the following:

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

To wrap the file, run the following from the operating system prompt:

wrap iname=wrap_test.sql

The output of the wrap utility is similar to the following:

If you view the contents of the wrap_test.plb text file, the first line is CREATE PROCEDURE wraptest wrapped and the rest of the file contents is hidden.

You can run wrap_test.plb in SQL*Plus to execute the SQL statements in the file:

SQL> @wrap_test.plb

After the wrap_test.plb is run, you can execute the procedure that was created:

SQL> CALL wraptest();

Using the DBMS_DDL create_wrapped Procedure

Example A-1 illustrates how CREATE_WRAPPED can be used to dynamically create and wrap a package specification and a package body in a database.

DECLARE
-- the package_text variable contains the text to create the package spec and body
  package_text VARCHAR2(32767);
  FUNCTION generate_spec (pkgname VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
     RETURN 'CREATE PACKAGE ' || pkgname || ' AS
       PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);
       PROCEDURE fire_employee (emp_id NUMBER);
       END ' || pkgname || ';';
  END generate_spec;
  FUNCTION generate_body (pkgname VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
     RETURN 'CREATE PACKAGE BODY ' || pkgname || ' AS
       PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS
       BEGIN
         UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
       END raise_salary;
       PROCEDURE fire_employee (emp_id NUMBER) IS
       BEGIN
         DELETE FROM employees WHERE employee_id = emp_id;
       END fire_employee;
       END ' || pkgname || ';';
  END generate_body;

BEGIN
  package_text := generate_spec('emp_actions'); -- generate package spec
  SYS.DBMS_DDL.CREATE_WRAPPED(package_text);  -- create and wrap the package spec
  package_text := generate_body('emp_actions'); -- generate package body
  SYS.DBMS_DDL.CREATE_WRAPPED(package_text); -- create and wrap the package body
END;
/

-- call a procedure from the wrapped package
CALL emp_actions.raise_salary(120, 100);

When you check the *_SOURCE views, the source is wrapped, or hidden, so that others cannot view the code details. For example:

SELECT text FROM USER_SOURCE WHERE name = 'EMP_ACTIONS';

The resulting output appears similar to: