[SQL] Encrypting and Decrypting Data Using Oracle's DBMS_CRYPTO Package

 

Security is a top priority when handling sensitive data in databases. Oracle provides the DBMS_CRYPTO package to help encrypt and decrypt data securely using industry-standard algorithms like AES.

In this blog, we'll walk through:

• Encrypting plaintext using AES-128 in CBC mode with PKCS5 padding

• Decrypting that data back to plaintext

• A full working example in PL/SQL

🔐 Encrypting Data using DBMS_CRYPTO




DECLARE
   l_key        RAW(128) := UTL_RAW.cast_to_raw('1234567890999999'); -- 16 bytes key for AES128
   l_data       RAW(2000);
   l_encrypted  RAW(2000);
BEGIN
   -- Convert plain text to RAW
   l_data := UTL_RAW.cast_to_raw('Hello World');
 
   -- Encrypt using AES128 + CBC + PKCS5 padding
   l_encrypted := DBMS_CRYPTO.ENCRYPT(
                     src => l_data,
                     typ => DBMS_CRYPTO.ENCRYPT_AES128 + 
                            DBMS_CRYPTO.CHAIN_CBC + 
                            DBMS_CRYPTO.PAD_PKCS5,
                     key => l_key);
 
   -- Output encrypted value in HEX format
   DBMS_OUTPUT.put_line('Encrypted (HEX): ' || RAWTOHEX(l_encrypted));
END;
/

🔓 Decrypting the Encrypted Data

Suppose the encrypted value from the above output is:
D35264E8079CCAA958116F5F2FCDD33E

Use the following block to decrypt it:

DECLARE
   l_key        RAW(128) := UTL_RAW.cast_to_raw('1234567890999999');
   l_data       RAW(2000);
   l_decrypted  VARCHAR2(4000);
BEGIN
   -- Assign the encrypted hex value as RAW
   l_data := HEXTORAW('D35264E8079CCAA958116F5F2FCDD33E');
 
   -- Decrypt the data
   l_decrypted := UTL_RAW.cast_to_varchar2(
                     DBMS_CRYPTO.DECRYPT(
                        src => l_data,
                        typ => DBMS_CRYPTO.ENCRYPT_AES128 + 
                               DBMS_CRYPTO.CHAIN_CBC + 
                               DBMS_CRYPTO.PAD_PKCS5,
                        key => l_key));

   DBMS_OUTPUT.put_line('Decrypted text: ' || l_decrypted);
END;
/



🔐 DBMS_CRYPTO.ENCRYPT_AES128

What it means: Use the AES algorithm with a 128-bit key size for encryption.
AES (Advanced Encryption Standard) is a strong, widely-used symmetric encryption algorithm.
128-bit means the key is 16 bytes long.



🔄 DBMS_CRYPTO.CHAIN_CBC

CBC = Cipher Block Chaining mode
It’s a block cipher mode that links each block of plaintext with the previous ciphertext block before encrypting it.
CBC provides stronger security than ECB (Electronic Codebook) mode because identical plaintext blocks produce different ciphertext (as long as the IV is different).
CBC requires an Initialization Vector (IV), which Oracle internally generates if you don't provide it.

📦 DBMS_CRYPTO.PAD_PKCS5

PKCS5 Padding is a standard padding scheme used when the plaintext doesn’t align exactly with the block size (typically 16 bytes for AES).
It adds extra bytes to the last block of data so that it fits the encryption block size.
During decryption, the padding is automatically removed.



🧮 Why + is used
Oracle uses the + operator to combine these constants into a single TYP value passed to DBMS_CRYPTO.ENCRYPT() or DECRYPT().
So this line (with value of 4353):

      typ => DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5

tells Oracle:
Use AES-128 encryption, with CBC chaining, and PKCS5 padding.