Dynamic wrapping using DBMS_DDL
Posted by decipherinfosys on September 10, 2007
In Oracle 10g Release 2, Oracle introduced a way of wrapping PL/SQL Code by using the DBMS_DDL package. Prior to 10g, PL/SQL WRAP utility was used to wrap the code. Only difference between WRAP utility and the subprograms of the DBMS_DDL package which wraps the PL/SQL Code is that the WRAP utility can take multiple routines separated by ‘/’ and wrap the context leaving other text as is while DBMS_DDL package can only take single PL/SQL unit (Procedure, function etc.) for wrapping.
DBMS_DDL package has an overloaded function called WRAP and an overloaded procedure called CREATE_WRAPPED to wrap the code. Signatures of function are as shown below:
Similarly, CREATE_WRAPPED stored procedure also takes the exact same arguments and has the exact same overloaded procedure signature.
First form of the function returns the wrapped or obfuscated text of the passed in string. Remaining two forms of the function return the table of strings that will be concatenated to form the original text but in an obfuscated manner. DBMS_SQL.VARCHAR2 and DBMS_SQL.VARCHAR2A are predefined types, which have limits of 256 bytes per line and 32K bytes per line respectively. So, the function overload with DBMS_SQL.VARCHAR2A can take larger inputs. Let’s use an example to demonstrate this functionality. Here is our original test procedure:
CREATE OR REPLACE PROCEDURE test_proc AS
Now we will obfuscate it using CREATE_WRAPPED procedure of DBMS_DDL package. Following is the PL/SQL block which does that:
SET SERVEROUTPUT ON SIZE 1000000
SET PAGESIZE 80
l_ddl(1) := ‘CREATE OR REPLACE PROCEDURE test_proc AS
SYS.DBMS_DDL.CREATE_WRAPPED(ddl => l_ddl,
lb => 1,
ub => l_ddl.count);
When we run the above PL/SQL block, CREATE_WRAPPED procedure will mask the source of the procedure passed as an argument and will compile it. Masked source code will reside in user_Source view. Following is the SQL to get the source code from the view followed by the result:
WHERE NAME = ‘TEST_PROC’
ORDER BY line;
PROCEDURE test_proc wrapped
In the same fashion, we can use DBMS_DDL.WRAP function but function will return back the encrypted string.
Sorry, the comment form is closed at this time.