Friday, June 18, 2010

Obfuscate your DDL statements (aHJgFSG127_henRIK)

It's always interesting to take a look at the new functionality in a new (fixpack) release. DB2 9.7 FP2 adds functionality to obfuscate DDL statements. You might state that with all the parameters and options or page-long spaghetti code inside stored procedures DDL statements already are confusing. Well, now you can add another layer of obfuscation with the help of DB2.

The key to obfuscation is to know about the new WRAP function and CREATE_WRAPPED procedure in the DBMS_DDL module and the WRAPPED keyword. You can pass a string with a regular DDL statement to either routines. In the case of WRAP a string (CLOB) with the encoded, i.e., obfuscated statement is returned. When CREATE_WRAPPED is used, the statement is directly deployed into the database, i.e., it is executed and thereby the obfuscated statement text stored in the system catalog.

Obfuscation works for the following types of objects:
  • procedure
  • functions
  • triggers
  • views
  • PL/SQL packages and package bodies
  • modules to which functions or procedure are added or which are published
An obfuscated statement could look like the following:
CREATE PROCEDURE henrik.otest(col1 varchar(20)) WRAPPED SQL09072 aHJgFSG123_henRIKobFU3cAtEDtHIsSTRinGANdmaDEITun4eaDABle

The SQL09072 is a version identifier (DB2 9.7 FP2), so that DB2 remembers what version of the encoding function was used.

So try to not use any spaghetti code anymore, but start WRAPing your procedures...