Data Encryption and Decryption With Oracle

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:

1. DBMS_CRYPTO (came with Oracle 10g)

2. 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.

Source

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:

Source

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

• Padding: PCKS5

• 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 package:

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 KEY and 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.