DB:: Encryption using DBMS_CRYPTO package

Recently I had a requirement to store and retrieve confidential data from Oracle database in a secure manner. So I used DBMS_CRYPTO package to achieve the same.

Pre-Requisites:
Initially, we need to make sure the user has privileges to execute DBMS_CRYPTO package.

If not, execute following command as SYS user to grant access to respective application user.

GRANT EXECUTE ON sys.dbms_crypto TO <APP_USER_ID>;

Followed by that, I created a PLSQL package to use encrypt and decrypt text.


Package:
Here I used combination of 3 cipher suites(AES256/CBC/PKCS5) and random 32 bit key for encryption.

 CREATE OR REPLACE PACKAGE "ENC_DEC_PKG" IS   
   l_enc_type       PLS_INTEGER := dbms_crypto.encrypt_aes256  
                     + dbms_crypto.chain_cbc  
                     + dbms_crypto.pad_pkcs5;  
   l_key         RAW(128)  := dbms_crypto.randombytes(32);  
   l_decrypted_raw    RAW(2048);  
 /*  
  * Encrypt  
 */  
   PROCEDURE encrypt_txt(  
     p_plain_text IN VARCHAR2,  
     p_enc_text  OUT RAW,  
     p_secret_key OUT RAW  
   );  
 /*  
  * Decrypt  
 */  
   FUNCTION decrypt_txt(  
     p_enc_text  IN RAW,  
     p_secret_key IN RAW  
   ) RETURN VARCHAR2;  
 END ENC_DEC_PKG;  

Package Body:

 CREATE OR REPLACE PACKAGE BODY ENC_DEC_PKG AS  
   PROCEDURE encrypt_txt(  
     p_plain_text IN VARCHAR2,  
     p_enc_text  OUT RAW,  
     p_secret_key OUT RAW  
   ) IS   
   BEGIN  
     p_secret_key := l_key;  
     p_enc_text := dbms_crypto.encrypt(src => UTL_I18N.STRING_TO_RAW(p_plain_text,'AL32UTF8'),  
                      typ => l_enc_type,   
                      key => p_secret_key);  
   END encrypt_txt;  
   FUNCTION decrypt_txt (  
     p_enc_text  IN RAW,  
     p_secret_key IN RAW  
   ) RETURN VARCHAR2 AS  
     l_text_raw  RAW(128)  := utl_raw.cast_to_raw(p_enc_text);  
     l_key_raw  RAW(128)  := utl_raw.cast_to_raw(p_secret_key);  
   BEGIN  
     l_decrypted_raw := dbms_crypto.decrypt(src => p_enc_text,  
                         typ => l_enc_type,   
                         key => p_secret_key);  
     RETURN utl_raw.cast_to_varchar2(l_decrypted_raw);  
   END;  
 END ENC_DEC_PKG;  

Execute following anonymous PLSQL block to test the same.

 DECLARE  
  l_text    VARCHAR2(100) := 'lkakarla';  
  l_enc_txt   RAW(128);  
  l_enc_key   RAW(128);  
  l_dec_txt   VARCHAR2(100);  
 BEGIN  
  ENC_DEC_PKG.encrypt_txt(l_text,l_enc_txt,l_enc_key);  
  DBMS_OUTPUT.PUT_LINE('Plain Text   :: ' || l_text);  
  DBMS_OUTPUT.PUT_LINE('Encrypted Text :: ' || l_enc_txt);  
  DBMS_OUTPUT.PUT_LINE('Encryption Key :: ' || l_enc_key);  
  l_dec_txt := ENC_DEC_PKG.decrypt_txt(l_enc_txt, l_enc_key);  
  DBMS_OUTPUT.PUT_LINE('Decrypted Text :: ' || l_dec_txt);  
 END;  

Sample Output: