Wrapping / encrypting PL/SQL code

A Wrapping PL/SQL Source Code

Wrapping is the process of hiding PL/SQL source code. Wrapping helps to protect your source code from business competitors and others who might misuse it.

You can wrap PL/SQL source code with either the wrap utility or DBMS_DDL subprograms. The wrap utility wraps a single source file, such as a SQL*Plus script. The DBMS_DDL subprograms wrap a single dynamically generated PL/SQL unit, such as a single CREATE PROCEDURE statement.

Wrapped source files can be moved, backed up, and processed by SQL*Plus and the Import and Export utilities, but they are not visible through the static data dictionary views *_SOURCE.

Guidelines for Wrapping

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

Limitations of Wrapping

·         Wrapping is not a secure method for hiding passwords or table names.

Wrapping a PL/SQL unit prevents most users from examining the source code, but might not stop all of them.

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

Wrapping detects only tokenization errors (for example, runaway strings), not syntax or semantic errors (for example, nonexistent tables or views). Syntax or semantic errors are detected during PL/SQL compilation or when executing the output file in SQL*Plus.

·         Wrapped PL/SQL units are not downward-compatible.

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.

There are two way to wrap your pl/sql code.

·         Wrap

·         DBMS_DDL

Wrapping can be done with the wrap utility and DBMS_DDL subprograms. 

The wrap utility is run from the command line and processes an input SQL file, such as a SQL*Plus installation script. The DBMS_DDL subprograms wrap a single PL/SQL unit, such as a single CREATE PROCEDURE command, that has been generated dynamically.

WRAPPING ORACLE PL/SQL CODE WITH THE DBMS_DDL CREATE_WRAPPED PROCEDURE:

1. Create an anonymous block with your code (which needs to be encrypted) in generate_spec and generate_body, enclosed in quotes.

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 add (x NUMBER, y NUMBER); END ' || pkgname || ';';

  END generate_spec;

 

  FUNCTION generate_body (pkgname VARCHAR2) RETURN VARCHAR2 AS

  BEGIN

     RETURN 'CREATE PACKAGE BODY ' || pkgname || ' AS

PROCEDURE add (x NUMBER, y NUMBER) IS

BEGIN dbms_output.put_line (x+y); END add; END ' || pkgname || ';';

  END generate_body;

 

BEGIN

  -- generate package spec

  package_text := generate_spec('Calculation');

  -- create and wrap the package spec

  SYS.DBMS_DDL.CREATE_WRAPPED(package_text); 

  -- generate package body 

  package_text := generate_body('Calculation'); 

  -- create and wrap the package body 

  SYS.DBMS_DDL.CREATE_WRAPPED(package_text);

END;

2. The package object – calculation will get created in the data base in encrypted format.

Sql> Calculation.add(10, 20);

3. Check the all sources table. You can see an encrypted version of your code.

WRAPPING ORACLE PL/SQL CODE WITH THE WRAP UTILITY:

The wrap utility processes an input SQL file and obfuscates only the PL/SQL units in the file, such as a package specification, package body, function, procedure, type specification, or type body. It does not obfuscate PL/SQL content in anonymous blocks or triggers or non-PL/SQL code.
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]
Do not use any spaces around the equal signs.
Example:

1.      Write a procedure abc(), in a file abc.sql (do not compile it in database) and save it in C:\

create procedure abc is

begin

dbms_output.put_line('Hello World');

end abc;

2.      First generate the wrapped code for this sql file using the following command.

wrap iname=C:\abc.sql

You will get the following message:

      PL/SQL Wrapper: Release 9.0.1.3.1- Production on Sat Jun 0412:59:59  2011

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

Processing C:\abc.sql to abc.plb

3.      The wrapped file code abc.plb is created in the same directory (C:\abc.sql). It will look like this

     4.      Now compile the encrypted code (abc.plb) in the database. 

SQL> @C:\abc.plb

Procedure created.

5.      So, our source code is not visible through the USER_SOURCE, ALL_SOURCE, or DBA_SOURCE data dictionary views.

6.      Now execute the procedure

SQL> ed;

Wrote file afiedt.buf

  1  begin

  2  abc();

  3* end;

SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> /

Hello World

PL/SQL procedure successfully completed.