DBMS_DDL- Securing the source code

DBMS_DDL Package

DBMS_DDL is one of the package that guard the casual viewing of the source code, also it has one of the procedure to compile the database object , we will see the procedure and functions one by one.

1. DBMS_DDL.ALTER_COMPILE :-

DBMS_DDL.ALTER_COMPILE (
   type             VARCHAR2, 
   schema           VARCHAR2, 
   name             VARCHAR2
   reuse_settings   BOOLEAN := FALSE);

 the above procedure is deprecated, but it is still available in the package for backward compatibility.  

We can use below statement to re-compile the database object :-

ALTER PROCEDURE|FUNCTION|PACKAGE [<schema>.] <name> COMPILE [BODY]   

2. DBMS_DDL.WRAP :- 

Used to hide (obfuscate) your PL/SQL source code. Traditionally this has been done using the wrap utility, but Oracle 10g Release 2 also allows this to be done dynamically using the DBMS_DDL package.

Lets do the particle step by step :-

Before starting we should know how to extract the source code for the procedure or function or package which we want to encrypt , that will learn in the code it self while wrapping the PL/SQL Code. Two ways we can do :-

1.  Using the ALL_SOURCES :- You can get the whole code from all_source and same you can use while obfuscating or encoding the code

For example :-

  DECLARE
   code_extract             DBMS_SQL.varchar2a;
   l_wrap     VARCHAR2 (32767);
   l_encode   VARCHAR2 (32767);
BEGIN
   SELECT text
     BULK COLLECT INTO code_extract
     FROM all_source
    WHERE TYPE = 'PROCEDURE' AND name = 'ENCODE_TEST';
   l_wrap := 'create or replace ';
   FOR i IN 1 .. code_extract.LAST
   LOOP
      l_wrap := l_wrap || code_extract (i);
   END LOOP;
   DBMS_DDL.CREATE_WRAPPED (l_wrap);
END;

You can see the following things in the code

•  First we extracted the code from all_source , after that we have used create_wrapped procedure to encode the source code. Once the above block completed , the source code of the procedure will be in encoding format, make sure you take the backup of source code before performing the above steps.

Encoded Source Code Post Block Execution :-

"procedure encode_test wrapped
a000000
1f
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
5e 8d
RPPtb62smzxirFNbCnlmJtt4RPEwg5nnm7+fMr2ywFxa1wz/rqHwlqFi0e4JpuFycHxDXfy5
KVvJvlc6Ocb4B0A2RkB9+1I00LBPCkBFgCfc3LzmMD/M3BhF6CpUawpPmUavpqbGhgTf
"

I Hope the post helps in basic understanding of package how to use and how it can help in compiling the invalid object ,you use with the dynamic SQL as well to create the automated script , before using  the package make sure you have execute grant over the package.

You can test the script live on :-
https://livesql.oracle.com/



Please comment your feedback about the article .. and share if you like :)

Wish you a Happy new year!!!