Data Encryption and Decryption With Oracle
DBMS_CRYPTO is an awesome package offered by Oracle for data encryption support. Check out its features, what it supports, how to use it, and more!
Join the DZone community and get the full member experience.Join For Free
In this article, I would like to talk about Oracle's data encryption support. Oracle offers two different packages for data encryption to software developers. They are:
DBMS_CRYPTO(came with Oracle 10g)
DBMS_OBFUSCATION_TOOLKIT(came with Oracle 8i)
DBMS_CRYPTO has more and more features than
DBMS_OBFUSCATION_TOOLKIT because it is more up-to-date. Below, you can see the differences between these packages.
DBMS_CRYPTO supports many encryption and hashing algorithms. You can easily identify the algorithms you need, encrypt or decrypt your data, or open encrypted data.
You can take a look at the basic functions of the
DBMS_CRYPTO package here:
Let's look at an example with this package, which is easy to understand and use.
Use case: Let's make an encryption method that encrypts the incoming value a parameter using the DES algorithm (CBC as Cipher and PCKS5 as Padding must be selected) and creates an output in base64 format. Let's write another method that will do the process of decryption with the same method.
Cipher mode: CBC
Key to be used for encryption: 52AB32; ^ $! ER94988OPS3W21
Initialization vector (IV) to be used for encryption: TY54ABCX
Note: IV is not a mandatory parameter. It is being used to make the example richer.
The signature of the
ENCRYPT function of the
DBMS_CRYPTO.ENCRYPT( dst IN OUT NOCOPY BLOB, src IN CLOB CHARACTER SET ANY_CS, typ IN PLS_INTEGER, KEY IN RAW, iv IN RAW DEFAULT NULL);
Before I write functions, I want to convert the
iv parameters to be hexadecimal and store them in a table. I'll read the table that I created this information with in the encryption and decryption steps.
CREATE TABLE ALGPARAMETERS ( NAME VARCHAR2(100 BYTE), VALUE NVARCHAR2(100) ); INSERT INTO ALGPARAMETERS SELECT 'key' NAME, RAWTOHEX ('52AB32;^$!ER94988OPS3W21') VALUE FROM DUAL UNION SELECT 'iv' NAME, RAWTOHEX ('TY54ABCX') VALUE FROM DUAL; COMMIT; SELECT * FROM ALGPARAMETERS;
Now, let's write our encryption function:
CREATE OR REPLACE FUNCTION F_ENCRYPT (p_input VARCHAR2) RETURN VARCHAR2 AS v_encrypted_raw RAW (2000); v_key RAW (320); v_encryption_type PLS_INTEGER := DBMS_CRYPTO.DES_CBC_PKCS5; v_iv RAW (320); BEGIN SELECT VALUE INTO v_key FROM algparameters WHERE name = 'key'; SELECT VALUE INTO v_iv FROM algparameters WHERE name = 'iv'; v_encrypted_raw := DBMS_CRYPTO.encrypt (src => UTL_I18N.STRING_TO_RAW (p_input, 'AL32UTF8'), typ => v_encryption_type, key => v_key, iv => v_iv); RETURN UTL_RAW.CAST_TO_VARCHAR2 (UTL_ENCODE.base64_encode (v_encrypted_raw)); END;
Now, let's write the function that opens the encrypted data.
DBMS_CRYPTO.DECRYPT( dst IN OUT NOCOPY BLOB, src IN BLOB, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL);
The signature of the
DECRYPT function of the
DBMS_CRYPTO package is:
CREATE OR REPLACE FUNCTION F_DECRYPT (p_input VARCHAR2) RETURN VARCHAR2 AS v_decrypted_raw RAW (2000); v_key RAW (320); v_encryption_type PLS_INTEGER := DBMS_CRYPTO.DES_CBC_PKCS5; v_iv RAW (320); BEGIN SELECT VALUE INTO v_key FROM algparameters WHERE name = 'key'; SELECT VALUE INTO v_iv FROM algparameters WHERE name = 'iv'; v_decrypted_raw := DBMS_CRYPTO.DECRYPT ( src => UTL_ENCODE.base64_decode (UTL_RAW.CAST_TO_RAW (p_input)), typ => v_encryption_type, key => v_key, iv => v_iv); RETURN UTL_I18N.RAW_TO_CHAR (v_decrypted_raw, 'AL32UTF8'); END;
It's time to test these functions:
SELECT 'TEST123TEST' INPUT, F_ENCRYPT('TEST123TEST') ENCRYPTED_RESULT, F_DECRYPT(F_ENCRYPT('TEST123TEST')) DECRYPT_RESULT FROM DUAL;
As it turns out, we have been able to successfully perform encryption and decryption operations in as per our needs successfully.
To access more detailed information about the
DBMS_CRYPTO package, check out this link.
Opinions expressed by DZone contributors are their own.