In today’s data-driven world, securing sensitive information is paramount. Oracle’s DBMS_CRYPTO package offers robust encryption and decryption capabilities to help protect your data. In this blog, we will walk through creating Oracle procedures to encrypt and decrypt data, demonstrating how to securely handle sensitive information.

Table of Contents

1. Setting Up the Database Table
2. Creating the Encryption/Decryption Procedure
3. Creating the Insert Procedure
4. Creating the Retrieve and Decrypt Procedure
5. Testing the Procedures

Setting Up the Database Table

First, we need to create a table to store the encrypted data. This table will hold an ID and the encrypted value.

CREATE TABLE encrypted_data (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    encrypted_value RAW(2000)
);

Creating the Encryption/Decryption Procedure

This procedure handles both encryption and decryption of data using AES-256 in CBC mode with PKCS#5 padding. For simplicity, the encryption key and initialization vector (IV) are hard-coded.

CREATE OR REPLACE PROCEDURE encrypt_decrypt_demo (
    p_value IN VARCHAR2,
    p_encrypted_value OUT RAW,
    p_decrypted_value OUT VARCHAR2
) IS
    l_key RAW(32) := UTL_RAW.CAST_TO_RAW('12345678901234567890123456789012'); -- 32 bytes key for AES-256
    l_iv RAW(16) := UTL_RAW.CAST_TO_RAW('1234567890123456'); -- 16 bytes IV for AES
    l_encrypted_value RAW(2000);
    l_decrypted_value RAW(2000);
BEGIN
    -- Encrypt the input value
    l_encrypted_value := DBMS_CRYPTO.ENCRYPT(
        src => UTL_I18N.STRING_TO_RAW(p_value, 'AL32UTF8'),
        typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
        key => l_key,
        iv => l_iv
    );
    
    -- Store the encrypted value in the output parameter
    p_encrypted_value := l_encrypted_value;

    -- Decrypt the encrypted value
    l_decrypted_value := DBMS_CRYPTO.DECRYPT(
        src => l_encrypted_value,
        typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
        key => l_key,
        iv => l_iv
    );
    
    -- Convert the decrypted RAW value back to VARCHAR2 and store it in the output parameter
    p_decrypted_value := UTL_I18N.RAW_TO_CHAR(l_decrypted_value, 'AL32UTF8');
END encrypt_decrypt_demo;

Creating the Insert Procedure

Next, we create a procedure to encrypt a given value and insert it into the encrypted_data table.

CREATE OR REPLACE PROCEDURE insert_encrypted_data (
    p_value IN VARCHAR2
) IS
    l_encrypted_value RAW(2000);
    l_decrypted_value VARCHAR2(2000);
BEGIN
    -- Encrypt the value
    encrypt_decrypt_demo(p_value, l_encrypted_value, l_decrypted_value);
    
    -- Insert the encrypted value into the table
    INSERT INTO encrypted_data (encrypted_value) VALUES (l_encrypted_value);
    
    COMMIT;
END insert_encrypted_data;

Creating the Retrieve and Decrypt Procedure

This procedure retrieves the encrypted value from the encrypted_data table by ID and decrypts it.

CREATE OR REPLACE PROCEDURE retrieve_and_decrypt_data (
    p_id IN NUMBER
) IS
    l_encrypted_value RAW(2000);
    l_decrypted_value VARCHAR2(2000);
BEGIN
    -- Retrieve the encrypted value by ID
    SELECT encrypted_value INTO l_encrypted_value
    FROM encrypted_data
    WHERE id = p_id;
    
    -- Decrypt the value
    encrypt_decrypt_demo(UTL_I18N.RAW_TO_CHAR(l_encrypted_value, 'AL32UTF8'), l_encrypted_value, l_decrypted_value);
    
    -- Output the decrypted value
    DBMS_OUTPUT.PUT_LINE('Decrypted Value: ' || l_decrypted_value);
END retrieve_and_decrypt_data;

Testing the Procedures

Finally, we test our procedures by inserting and then retrieving an encrypted value.

1. Insert Data

BEGIN
    insert_encrypted_data('Hello, World!');
END;
/

2. Retrieve and Decrypt Data

Assuming the ID of the inserted row is 1, you can retrieve and decrypt it as follows:

BEGIN
    retrieve_and_decrypt_data(1);
END;
/

Conclusion

In this blog, we have demonstrated how to use Oracle’s DBMS_CRYPTO package to encrypt and decrypt data through stored procedures. By separating the insertion and retrieval processes, we have created a modular approach to handle sensitive data securely. Remember, for real-world applications, always manage your encryption keys and IVs securely and follow best practices for data security.