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.
>>-DBMS_DDL.CREATE_WRAPPED--(--object-definition-string--)-----><
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® Database
for Linux®, UNIX®, and Windows® 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