Obfuscating an SQL routine or SQL trigger
SQL functions, procedures, and triggers can be obfuscated so that their routine body logic and statements are not visible on a system.
Once testing of an SQL procedure, function, or trigger is complete, you can obfuscate the text so it is not readable to anyone on the system. To do this, use the WRAP SQL function or the CREATE_WRAPPED SQL procedure. Both are system provided routines in schema SYSIBMADM.
The WRAP scalar function returns an obfuscated form of the SQL statement that was provided as an input argument. The CREATE_WRAPPED procedure will obfuscate the SQL statement and then execute it.
When an obfuscated routine is created, the routine body will not be visible in the catalog tables or in the program object. No listing will be generated and any temporary files used during creation of the routine will be deleted. The routine will always be created as not debuggable so no debug view containing the routine content will exist.
The Generate SQL feature of System i® Navigator has an option to make it easy to obfuscate many SQL routines at one time.
Once a routine is obfuscated, the program or service program object for the SQL routine can be saved and restored to other systems. It can be used as an object for the Generate SQL feature of System i Navigator. Db2 for i will understand how to process the statement correctly and it will remain obfuscated.
CREATE PROCEDURE UPDATE_STAFF (
IN P_EmpNo CHAR(6),
IN P_NewJob CHAR(5),
IN P_Dept INTEGER)
BEGIN
UPDATE STAFF
SET JOB = P_NewJob
WHERE DEPT = P_Dept and ID = P_EmpNo;
END;
VALUES(SYSIBMADM.WRAP(
'CREATE PROCEDURE UPDATE_STAFF (
IN P_EmpNo CHAR(6),
IN P_NewJob CHAR(5),
IN P_Dept INTEGER)
BEGIN
UPDATE STAFF
SET JOB = P_NewJob
WHERE DEPT = P_Dept and ID = P_EmpNo;
END'
));
The result from this statement is a CLOB value that
contains a value that looks something like the following statement.
Since the timestamp is used during the obfuscation process, your result
can be different every time. The value is shown here on several lines
for convenience. New line characters are not allowed in the wrapped
part of the statement text.CREATE PROCEDURE UPDATE_STAFF ( IN P_EMPNO CHAR ( 6 ) , IN P_NEWJOB CHAR ( 5 )
, IN P_DEPT INTEGER ) WRAPPED QSQ07010 aacxW8plW8FjG8pnG8VzG8FD68Fj68:Hl8:dY_p
B2qpdW8pdW8pdW_praqebaqebaGEMj_vsPBs5bOJUUqnHVayEl_ogAlGWqz2jJCIE1dQEjt33hd5Sps
5cYGViD1urv7vGKeOcC4CwpCibbmeMfsW3XzXWnlplyX9wunOCqqFiDqaBl
This
is an executable SQL statement. It can be run just like the original
SQL statement. Altering any of the characters that follow the WRAPPED
keyword will cause the statement to fail.To deploy this statement, the obfuscated form can be embedded in a RUNSQLSTM source member or source stream file. You need to be very careful to include exactly the characters in the obfuscated version.
CALL SYSIBMADM.CREATE_WRAPPED(
'CREATE PROCEDURE UPDATE_STAFF (
IN P_EmpNo CHAR(6),
IN P_NewJob CHAR(5),
IN P_Dept INTEGER)
BEGIN
UPDATE STAFF
SET JOB = P_NewJob
WHERE DEPT = P_Dept and ID = P_EmpNo;
END'
);
This will create the procedure and the entire SQL routine body will be obfuscated. Looking at the ROUTINE_DEFINITION column in SYSROUTINES will show the obfuscated form of the routine body, starting with the WRAPPED keyword. You must save the original routine source if you might need it for future reference since there is no way to generate the original source from the obfuscated text.