Systems Engineering and RDBMS

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:

DBMS_DDL.WRAP(
ddl VARCHAR2
)
RETURN VARCHAR2;

DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2S,
lb PLS_INTEGER,
ub PLS_INTEGER
)
RETURN DBMS_SQL.VARCHAR2S;

DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2A,
lb PLS_INTEGER,
ub PLS_INTEGER
)
RETURN DBMS_SQL.VARCHAR2A;

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
BEGIN
null;
END;
/

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
DECLARE
l_ddl  DBMS_SQL.VARCHAR2A;
BEGIN

l_ddl(1) := ‘CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
NULL;
END test_proc;’;

SYS.DBMS_DDL.CREATE_WRAPPED(ddl => l_ddl,
lb  => 1,
ub  => l_ddl.count);

END;
/

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:

SELECT text
FROM User_Source
WHERE NAME = ‘TEST_PROC’
ORDER BY line;

TEXT
———————————————————————–
PROCEDURE test_proc wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
7
62 71
3NOVVKvzTETNLTS5l4TpQf2gO4Qwg5nnm7+fMr2ywWoWLR9JYWl64ruHSLCWdE3OK5QT99
rGePH/yGC/DRufzBUS8Gq3bTLKYhBez7pq2cjq4=

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.

 
%d bloggers like this: