CREATE_WRAPPED stored procedure
The CREATE_WRAPPED procedure encodes a readable data definition statement into an obfuscated data definition statement and then deploys the object in the database. The procedural logic and embedded SQL statements in an obfuscated data definition statement are scrambled in such a way that any intellectual property in the logic cannot be easily extracted.
The schema is SYSIBMADM.
- object-defintion-string
- A string of any built-in character type that contains any of the following data definition
statements:
- CREATE FUNCTION (compiled SQL scalar)
- CREATE FUNCTION (inlined SQL scalar)
- CREATE FUNCTION (SQL table)
- CREATE PROCEDURE (SQL - native)
- CREATE TRIGGER (basic)
- CREATE TRIGGER (advanced)
object-defintion-string must not be bit data, and it cannot contain the VERSION keyword.
The procedure encodes the input into an obfuscated data definition statement string and then dynamically executes that data definition statement. The encoded statement consists of the following parts:
- The original statement up to and including the routine signature or trigger name.
- The WRAPPED keyword.
- The product identifier of the database manager that invoked the function. The product identifier (PRDID) value is an 8-byte character value in pppvvrrm format, where: ppp is a 3-letter product code; vv is the version;rr is the release; and m is the modification level. In Db2 12 for z/OS®, the modification level indicates a range of function levels:
- DSN12015 for V12R1M500 or higher.
- DSN12010 for V12R1M100.
- The encoded remainder of the data definition statement, in the form of a string of letters (a-z and A-Z), digits (0-9), underscores, and colons. It is converted to Unicode UTF-8 before it is encoded. An error is issued if object-definition-string cannot be converted or if the intermediate string exceeds the maximum length for SQL statements. A warning occurs if a character is converted to the substitution character.
The encoded data definition statement can be up to one-third longer than the plain text form of the statement. If the result exceeds the maximum length for SQL statements, an error is issued.
Examples
- Example 1
- The following procedure call produces an obfuscated version of a function that computes a yearly
salary from an hourly wage given a 40 hour work
week.
CALL CREATE_WRAPPED('CREATE FUNCTION salary(wage DECFLOAT) RETURNS DECFLOAT RETURN wage * 40 * 52'); SELECT TEXT FROM SYSIBM.SYSROUTINES WHERE NAME ='SALARY' AND SCHEMA = CURRENT SCHEMA;
The result is similar to the following form:
CREATE FUNCTION salary(wage DECFLOAT) WRAPPED DSN12015 ablGWmdiWmtyTmduTmJqTmtaUmtCUmZqUmdiXodK3idaWmdaWmdaWmZG1mIaG icaGy31TyStm_qGbe3sDxdxjtC8ymVGLpMXnuL8lkmNuRhhZ6qYJ2YYdXGaa
- Example 2
- The following procedure call produces an obfuscated version of a trigger that sets a complex
default value
CALL CREATE_WRAPPED('CREATE OR REPLACE TRIGGER trig1 BEFORE INSERT ON emp REFERENCING NEW AS n FOR EACH ROW WHEN (n.bonus IS NULL) SET n.bonus = n.salary * .04') SELECT STATEMENT FROM SYSIBM.SYSTRIGGERS WHERE NAME='TRIG1' AND SCHEMA= CURRENT SCHEMA;
The result is similar to the following form:
CREATE TRIGGER trig1 WRAPPED DSN12015 ablGWmdiWmtyTmduTmJqTmtaUmtGUnteUmZKWmtqWidaWmdaWmdaXmdyWncaGica GK6ot_81NzyodncdrRIJFp_tBjpJeIwg_dTKNHcdtHPSaNCpmqBKH2pMwExkRTJW Zr:dJd0_gSbehW:4Xx1UGPGnDxvmJfa5ZAGOr_1sfFiyaPrkOXzt5UMTmsASfyJR ksbPfM2dlATbq:0RW