Tips and Tricks in Oracle Advanced Concepts: October 2017

We have encrypted our data while storing it in the database, but have we ever tried to encrypt our PL/SQL subprograms while creating them? There are scenarios where we must hide our code from our client for few reasons like stopping anyone from reading and then recreating our code, and much more.

There is an Oracle provided utility to achieve this task, that is the Oracle's WRAP utility. The WRAP utility doesn't encrypt our code but just obfuscates it. Here is an example,

CREATE OR replace PROCEDURE Proc_wrap(ip_date DATE)
IS
BEGIN
dbms_output.Put_line('The input date is '
||ip_date);
END;
/

Consider that we have planned to make the procedure unreadable to anybody who tries to understand it. For this scenario we can use the WRAP utility in Oracle to get this done as below,

SELECT dbms_ddl.wrap(q'[CREATE OR REPLACE PROCEDURE proc_encrypt( ip_date DATE) IS BEGIN dbms_output.put_line('the input DATE IS '||ip_date); END; /]')
FROM dual;

The above SELECT query returns the obfuscated form of this PROC_WRAP procedure as below,

CREATE OR REPLACE PROCEDURE proc_encrypt wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
71 ae
g10MIi6Tv0Y9j0IvM3mHKN7UxXIwg5nnm7+fMr2ywFwWFpeuSq7XDNHlVhazaabh0XKpMB/G
L+pEL2v6Q4hHQ138uSlbibhSm7JK/iiyveeysx0GMCyuJOqygchfwwJHNKwl0wKJ29PKA1Q1
7eg5J0DoKpwKT20qnCIdpgxFynY=

This obfuscated procedure can be still executed just like a normal procedure.

But wait! Can we Unwrap this obfuscated script to its readable format? The answer is YES!!

There are lots of online utilities like codecrete.net, which does the job. So, this is not the most efficient way to hide our code.

While thinking of a better approach, I tumbled upon the DBMS_CRYPTO package.

Now, what is the DBMS_CRYPTO package and what is it used for?

The DBMS_CRYPTO package encrypts and decrypts messages and was introduced to replace the DBMS_OBFUSCATION_TOOLKIT package by providing support for a range of new secure algorithms with easy usage. The algorithms 3DES_2KEY and MD4 are only provided for backward compatibility and the algorithms 3DES, AES, MD-5, SHA-1, and SHA-2 provide more security compared to them.

The DBMS_CRYPTO package consists of ENCRYPT and DECRYPT subprograms for encrypting and decrypting the string messages respectively.

The prototype of the ENCRYPT and the DECRYPT functions in this package are shown below,

DBMS_CRYPTO.ENCRYPT( src IN RAW, typ IN PLS_INTEGER, KEY IN RAW, iv IN RAW DEFAULT NULL)
RETURN RAW;

DBMS_CRYPTO.DECRYPT( src IN RAW, typ IN PLS_INTEGER, KEY IN RAW, iv IN RAW DEFAULT NULL)
RETURN RAW;

SRC parameter accepts RAW and LOB input data for the encryption for the overloaded function and the procedures respectively.
TYP parameter accepts the stream or block ciphers and the modifiers to be used.
KEY parameter accepts the encryption key used by the user for the encryption process.
IV parameter is also called as the initialization vector. This is for the block ciphers. The default is Null.

Now, consider the above same PROC_WRAP procedure for the encryption process. As a initial step, we must encrypt it using the below block. Note that the procedure is forward declared inside an anonymous block and this block is encrypted as shown below,

DECLARE
  l_vc_input_string clob:= q'[DECLARE PROCEDURE proc_encrypt(     ip_date DATE) IS BEGIN   dbms_output.put_line('The input date is '||ip_date); END; BEGIN   proc_encrypt(sysdate); END;]';
  l_rw_encryption_key raw(32);
  l_rw_encrypted_string raw(32767);
  l_pi_encryption_type pls_integer:=DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
  l_rw_encryption_key   :=dbms_crypto.randombytes(32);
  l_rw_encrypted_string := DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (l_vc_input_string, 'AL32UTF8'), l_pi_encryption_type, l_rw_encryption_key);
  dbms_output.put_line('Input string before encryption=> '||l_vc_input_string);
  dbms_output.put_line('Encryption Key=> '||l_rw_encryption_key);
  dbms_output.put_line('Encrypted Script=> '||l_rw_encrypted_string);
END;
/

Input string before encryption=> DECLARE

PROCEDURE proc_encrypt(

ip_date DATE)

BEGIN

dbms_output.put_line('The input date is '||ip_date);

END;

BEGIN

proc_encrypt(sysdate);

END;

Encryption Key=> B3012F6470338E0DE91E47B219E175593919CFC41FC5A50E7FD1D768034CD813

Encrypted Script=> A5B2FDEA81B528B5546A11A51EB5368DC750AA8B4E671E62CD977AD4203923E451131934E2BBE11981D75B9BC8F3AF65160F22A6018839C6E48E8FB70C273515A76D67ACD3081FD23BE0D671A93B19CC61DE0DC37E41B7607EABB41DF8397F0D540257B3196071468CEBE0B0F5353F57D8F8B1273F1DC8643F3347B159E5E9F6C1C6A769426F5FE8E3F3BEF8AE5241CF5E67FC33735F30777EB1C0DD231A4909

Here, we have generated the KEY randomly of 32 bytes using the DBMS_CRYPTO.RANDOMBYTES function.

After getting the encrypted form of the procedure, we must create our "client facing procedure" as like below,

CREATE OR REPLACE PROCEDURE proc_test(
    ip_rw_encryption_key raw)
IS
  l_vc_encrypted_code VARCHAR2(32767):='750C744D1CCC6380303EACCD0A9ED8805980C9F3FFDA6EC67688396E1DDDFC87D2B8055E9A9256EF1DD4FAED42AC13569C25FFBE260107AF060651B7836E75E2CC20C19BE8E5AFE3F46FCA37BE0A62EAE0B3B15241D60B11A1E40C3F80A9A4DA6A575614B1BB4AF98A222919D1D654996D4567F475D99FD53021433A3940A38AE7F14B7C0EA4C374956D1BBDDA5C6145C1E0B000FFCF1622F3250160EC721C65';
  l_pi_encryption_type pls_integer   :=DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
  EXECUTE immediate UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.DECRYPT(l_vc_encrypted_code, l_pi_encryption_type, ip_rw_encryption_key), 'AL32UTF8');
END;

The above procedure just decrypts our encrypted message and then executes it using the EXECUTE IMMEDIATE clause, considering it as a dynamic code. Not everyone can decrypt and run the procedure successfully as the KEY which is used for encryption is required for the decryption also and this KEY is to be passed as input to the procedure.

If the KEY is wrong, the procedure exits with an error as below,

EXEC proc_test('ABCDEFFBE508071AA1EC5FF4D301A5DF12572742BCA5420C78B2');

Error report -
ORA-28234: key length too short
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 44
ORA-06512: at "SYS.PROC_TEST", line 7
ORA-06512: at line 1
28234. 00000 - "key length too short"

When we execute the procedure with the right KEY,

EXEC proc_test('6A0B656248D9955AEEFBE508071AA1EC5FF4D301A5DF12572742BCA5420C78B2');

Result:
The input date is 10-OCT-17

Thus, with this trick, we have made sure that our PL/SQL code is unreadable and can be accessed only if we have the right KEY.

Thank you,

Boobal Ganesan

Author of Advanced PL/SQL Programming - The Definitive Reference