Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Data Encryption and Decryption With Oracle

DZone's Guide to

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!

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

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.

Image title

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:

Image title

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.

Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

Topics:
oracle ,sql ,database ,tutorial ,encryption ,decryption

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}