IBM Support

SQL Procedure and Function obfuscation

News


Abstract

SQL Procedure and Function obfuscation

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements > SQL Procedure and Function obfuscation

Obfuscation provides the capability of treating the SQL statements and logic within SQL procedures or functions as an intellectual asset.

Obfuscation is something that is chosen or used when a procedure or function is created. When obfuscation is used, the routine definition is scrambled wherever it is stored. Db2 for i knows how to unscramble it when needed and external users are prevented from seeing the routine definition details.

Software vendors and others can use this support to prevent their customers from seeing or changing SQL routines delivered as part of their solution.


To discover more information on this topic:

  1. Refer to the "Obfuscating an SQL routine" section within the SQL Programming book.
  2. Refer to the "CREATE_WRAPPED procedure" and the "WRAP function" sections within the DB2 for i SQL Reference.

Example 1 - Using WRAP() to build an obfuscated CREATE PROCEDURE statement

Your application uses the following SQL Procedure.
  CREATE PROCEDURE UPDATE_STAFF ( IN P_EmpNo CHAR(6), IN P_NewJob CHAR(5), IN P_Dept INTEGER)      LANGUAGE SQL      TR : BEGIN UPDATE STAFF SET JOB = P_NewJob WHERE DEPT = P_Dept and ID = P_EmpNo;      END TR 

One way of shipping this procedure is to have code that runs at application install time that executes this SQL statement. However, after this SQL statement is executed the logic for the procedure is visible in the system catalogs.

SELECT ROUTINE_DEFINITION FROM QSYS2.SYSROUTINE WHERE ROUTINE_NAME = 'UPDATE_STAFF';

Figure 1. ROUTINE_DEFINITION contents without obfuscation
image-20200116091739-1

To protect this proprietary logic, you can use use the SYSIBMADM.WRAP function to obfuscate the procedure definition.

values(sysibmadm.wrap('CREATE PROCEDURE UPDATE_STAFF ( IN P_EmpNo CHAR(6), IN P_NewJob CHAR(5), IN P_Dept INTEGER)
LANGUAGE SQL
TR : BEGIN UPDATE STAFF SET JOB = P_NewJob WHERE DEPT = P_Dept and ID = P_EmpNo;
END TR'))

Produces:

CREATE PROCEDURE UPDATE_STAFF ( IN P_EMPNO CHAR ( 6 ) , IN P_NEWJOB CHAR ( 5 ) , IN P_DEPT INTEGER )
WRAPPED QSQ07010 aacxW8plW8FjG8prG8VbG8Fb68Fj68pnl9:p28VN5qpdW8pdW8pdX8pvaqebaqebaMzyFeUQ_XRUxb5OQY5enQQoE32oUfeBiyCeePraIiJZ4_t684g:TFNX3EuesCYX403tppXuqA:J2k4H8ZA0Y5jZLndrKS_g4SGf8uj6LWipyyd9vyT1kqHu:kwZcgvkQ7OKKI75ljLMsYzz:

Your application then uses the obfuscated procedure definition when installing the application. After executing this statement, running the query against the catalog shows the obfuscated definition.
Figure 2. ROUTINE_DEFINITION contents after obfuscation
image-20200116092055-2

Example 2 - Using ACS Generate SQL to create an obfuscated install script

You can also use ACS to create an obfuscated SQL script.
Use "Generate SQL" with the obfuscate option.
Figure 3. Generate SQL with the Obfuscate option
image-20200115124546-1
This produces a script similar to what appears in Figure 4. This script can be deployed (executed) on a machine and the details of the routine body are protected.
Figure 4. Obfuscated install script
image-20200115124557-2

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
16 January 2020

UID

ibm11167448