CREATE_WRAPPED procedure - Deploy an obfuscated object

The CREATE_WRAPPED procedure transforms a plain text DDL object definition into an obfuscated DDL object definition and then deploys the object in the database.

Syntax

In an obfuscated DDL statement, the procedural logic and embedded SQL statements are encoded in such a way that any intellectual property in the logic cannot be easily extracted.

Parameters

object-definition-string

A string of type CLOB(2M) containing a DDL statement text which can be one of the following (SQLSTATE 5UA0O):

create procedure
create function
create package (PL/SQL)
create package body (PL/SQL)
create trigger
create view
alter module add function
alter module publish function
alter module add procedure
alter module publish procedure

The procedure transforms the input into an obfuscated DDL statement string and then dynamically executes that DDL statement. Special register values such as PATH and CURRENT SCHEMA in effect at invocation as well as the current invoker's rights are being used.

The encoding consists of a prefix of the original statement up to and including the routine signature or the trigger, view or package name, followed by the keyword WRAPPED. This keyword is followed by information about the application server that executes the procedure. The information has the form "pppvvrrm," where:

ppp identifies the product as Db2® using the letters SQL
vv is a two-digit version identifier, such as '09'
rr is a two-digit release identifier, such as '07'
m is a one-character modification level identifier, such as '0'.

For example, Fixpack 2 of Version 9.7 is identified as 'SQL09072'. This application server information is followed by a string of letters (a-z, and A-Z), digits (0-9), underscores and colons. No syntax checking is done on the input statement beyond the prefix that remains readable after obfuscation.

The encoded DDL statement is typically longer than the plain text form of the statement. If the result exceeds the maximum length for SQL statements an error is raised (SQLSTATE 54001).

Note: The encoding of the statement is meant to obfuscate the content and should not be considered as a form of strong encryption.

Authorization

EXECUTE privilege on the DBMS_DDL module.

Example

Create an obfuscated function computing a yearly salary from an hourly wage given a 40 hour workweek

CALL DBMS_DDL.CREATE_WRAPPED('CREATE FUNCTION ' ||                                          
                   'salary(wage DECFLOAT) ' ||
                   'RETURNS DECFLOAT ' ||
                   'RETURN wage * 40 * 52');
SELECT text FROM SYSCAT.ROUTINES 
 WHERE routinename = 'SALARY' 
    AND routineschema = CURRENT SCHEMA;

Upon successful execution of the CALL statement, The SYSCAT.ROUTINES.TEXT column for the row corresponding to routine 'SALARY' would be something of the form:

CREATE FUNCTION salary(wage DECFLOAT) WRAPPED SQL09072 obfuscated-text

Create an obfuscated trigger setting a complex default

CALL DBMS_DDL.CREATE_WRAPPED('CREATE OR REPLACE TRIGGER ' ||
                   'trg1 BEFORE INSERT ON emp ' ||
                   'REFERENCING NEW AS n ' ||
                   'FOR EACH ROW ' ||
                   'WHEN (n.bonus IS NULL) ' ||
                   'SET n.bonus = n.salary * .04');
SELECT text FROM SYSCAT.TRIGGERS
 WHERE trigname = 'TRG1' 
   AND trigschema = CURRENT SCHEMA;

Upon successful execution of the CALL statement, The SYSCAT.TRIGGERS.TEXT column for the row corresponding to trigger 'TRG1' would be something of the form:

CREATE OR REPLACE TRIGGER trg1 WRAPPED SQL09072  obfuscated-text