Category: Security

1)DBMS_OBFUSCATION_TOOLKIT package:

Encryption Function:

SQL> CREATE OR REPLACE FUNCTION encrypt (p_text IN VARCHAR2, p_key VARCHAR2 )
 RETURN RAW
 IS
 lc_text VARCHAR2(32767) := p_text;
 lr_key RAW(255) := UTL_RAW.cast_to_raw(p_key);
 lt_enc_text RAW(32767);
BEGIN
lc_text := RPAD( lc_text, (TRUNC(LENGTH(lc_text)/8)+1)*8, CHR(0) );
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input => UTL_RAW.cast_to_raw(lc_text),
                                      key => lr_key,
                           encrypted_data => lt_enc_text);
RETURN lt_enc_text;
END;
 /

Function created.

SQL> SELECT ENCRYPT('HELLO WORLD !','1234567890') from dual;

ENCRYPT('HELLOWORLD!','1234567890')
----------------------------------------------
F9D853C25CAB740D930D7DCA709D0994

SQL>

Decryption Function:

SQL> CREATE OR REPLACE FUNCTION decrypt (p_raw IN RAW, p_key VARCHAR2 )
RETURN VARCHAR2 IS
 lc_decrypted VARCHAR2(32767);
 lc_return_dec VARCHAR2(32767);
 lr_key RAW(255) := UTL_RAW.cast_to_raw(p_key);
 BEGIN
  DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
                                        key => lr_key,
                             decrypted_data => lc_decrypted);
   lc_return_dec := UTL_RAW.cast_to_varchar2(lc_decrypted);
  RETURN RTRIM( lc_return_dec, CHR(0) );
END; 
/

Function created.


SQL> SELECT decrypt('F9D853C25CAB740D930D7DCA709D0994','1234567890')
     FROM   dual;

DECRYPT('F9D853C25CAB740D930D7DCA709D0994','1234567890')
--------------------------------------------------------------
HELLO WORLD !

2) DBMS_CRYPTO Package:

set serveroutput on;
DECLARE
  l_text VARCHAR2(32767) :='1234-5678-9012-3456';
  l_key RAW(128) := UTL_I18N.STRING_TO_RAW('0123456789', 'AL32UTF8');
  l_encrypted_raw RAW(2048);
  l_decrypted_raw RAW(2048);
BEGIN
  dbms_output.put_line('Original : ' || l_text );
  l_encrypted_raw := dbms_crypto.encrypt(
            src =>  UTL_I18N.STRING_TO_RAW (l_text , 'AL32UTF8'),
            typ =>  dbms_crypto.des_cbc_pkcs5,
            key =>  l_key
                                        );
dbms_output.put_line('Encrypted ( CHAR ) : ' ||
                UTL_I18N.RAW_TO_CHAR(l_encrypted_raw, 'AL32UTF8'));
 
dbms_output.put_line('Encrypted ( HEX ) : ' ||
                RAWTOHEX(l_encrypted_raw));
 
 l_decrypted_raw := dbms_crypto.decrypt(
                                src => l_encrypted_raw,
                                typ => dbms_crypto.des_cbc_pkcs5,
                                key => l_key);

 dbms_output.put_line('Decrypted : '|| 
                UTL_I18N.RAW_to_CHAR(l_decrypted_raw,'AL32UTF8'));
 END;

   /

Original : 1234-5678-9012-3456
Encrypted ( CHAR ) : .n?k.z????L??E??AmdM
Encrypted ( HEX ) : 2E1F6EAE186B2E7A9DBCFA834C3FBD45A6B0C3806D64044D
Decrypted : 1234-5678-9012-3456

PL/SQL procedure successfully completed.

DBMS_CRYPTO Block Cipher Suites