DBMS_CRYPTO to encrypt and decrypt the Data in Oracle Database

DBMS_CRYPTO Let's Encrypt the Data..

Whats' the special in this post?
In this post i will be showing step by step demonstration with scripts and description of DBMS_CRYPTO ,post contains mixture of all blogs and tutorial for easy understanding.

Here we go :-

First we need to understand the basic terminologies :-

What is Encryption and Encryption Algorithm ?

Encryption is the process of converting the data into encoded form , especially to prevent unauthorized access.

Encryption Algorithm is the mechanism by which we can encrypt the data, below are the different categories of the Algorithm :-

Alogrithm Name	Alogrithm Type
Cryptographic algorithms	DES, 3DES, AES, RC4, 3DES_2KEY
Padding forms	PKCS5, zeroes
Block cipher chaining modes	CBC, CFB, ECB, OFB
Cryptographic hash algorithms	MD5, SHA-1, SHA-2 (SHA-256, SHA-384, SHA-512), MD4
Keyed hash (MAC) algorithms	HMAC_MD5, HMAC_SH1, HMAC_SH256, HMAC_SH384, HMAC_SH512

You can get information on each algorithm category on google about its functionality , here we will take few of the algorithm that we will be using to encrypt the data .

DBMS_CRYPTO package includes both encryption and decryption procedure and function, we use procedure to encrypt and decrypt the large object data type such as LOB and CLOB and function to encrypt the RAW data type  or we can say directly crypto package will not encode the varchar2 data type first we need to convert the data into RAW type. 

So lets start step by step :- 

FUNCTION  Encrypt (src  IN            RAW,

                                   typ  IN            PLS_INTEGER,

                                   key IN            RAW,

                                    iv   IN            RAW          DEFAULT NULL)

      RETURN RAW; 

Lets understand the parameters of Encrypt function :- 

SRC :- In parameter refers to input string which we will encode , it is of raw type as first we need to convert the VARCHAR2 to RAW type.

TYP :-  All the algorithm  type which we have shown above is assigned a pre-defined value. 

             for example :-  

                        ENCRYPT_AES256  CONSTANT PLS_INTEGER            :=     8;

                        CHAIN_CBC             CONSTANT PLS_INTEGER            :=   256;

                        PAD_PKCS5             CONSTANT PLS_INTEGER            :=  4096;

we can use individual type or we can combine all of three together , to encode data strongly.

Key :- Once we have selected the type of algorithm we will be using, now we can select the key for encryption by using 

1.  DBMS_CRYPTO.RANDOMBYTES (no_of_bytes) 
2. v_key raw(16):= UTL_RAW.cast_to_raw('mykeytoencode');

IV :- If we use block cipher algorithm then we can specify the IV else Default is NULL.

Conversion Rules

• To convert VARCHAR2 to RAW, use the UTL_I18N.STRING_TO_RAW function to perform the following steps:
  1. Convert VARCHAR2 in the current database character set to VARCHAR2 in the AL32UTF8 database character.
  2. Convert VARCHAR2 in the AL32UTF8 database character set to RAW.
  Syntax example:

  UTL_I18N.STRING_TO_RAW (string, 'AL32UTF8');
  

• To convert RAW to VARCHAR2, use the UTL_I18N.RAW_TO_CHAR function to perform the following steps:
  1. Convert RAW to VARCHAR2 in the AL32UTF8 database character set.
  2. Convert VARCHAR2 in the AL32UTF8 database character set to VARCHAR2 in the database character set you wish to use.
  Syntax example:

  UTL_I18N.RAW_TO_CHAR (data, 'AL32UTF8');

What is AL32UTF8 ?

A Unicode database is a database with a UTF-8 character set as the database character set. There are three Oracle character sets that implement the UTF-8 encoding. The first two are designed for ASCII-based platforms while the third one should be used on EBCDIC platforms. AL32UTF8.

Let's write a simple block to encrypt and decrypt the string by using above knowledge :- 

DECLARE


   input_string       VARCHAR2 (200) :=  'Welcome to the world of Oracle';

   output_string      VARCHAR2 (200);

   encrypted_raw      RAW (2000);             -- stores encrypted binary text

   decrypted_raw      RAW (2000);             -- stores decrypted binary text

   num_key_bytes      NUMBER := 256/8;        -- key length 256 bits (32 bytes)

   key_bytes_raw      RAW (32);               -- stores 256-bit encryption key

   encryption_type    PLS_INTEGER :=          -- total encryption type

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

   iv_raw             RAW (16);



BEGIN

   DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);

   key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);

   iv_raw        := DBMS_CRYPTO.RANDOMBYTES (16);

   encrypted_raw := DBMS_CRYPTO.ENCRYPT

      (

         src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

         typ => encryption_type,

         key => key_bytes_raw,

         iv  => iv_raw

      );

dbms_output.put_line('Encrypted Message: '||encrypted_raw);

   decrypted_raw := DBMS_CRYPTO.DECRYPT

      (

         src => encrypted_raw,

         typ => encryption_type,

         key => key_bytes_raw,

         iv  => iv_raw

      );

   output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

   DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string); 

END;

O/P :-

Original string: Welcome to the world of Oracle
Encrypted Message: F695024419E4AD850590CC3227C6FD8D828C357D08803DA0168771AC4391F540
Decrypted string: Welcome to the world of Oracle

So the above code shows a simple example of encryption and decryption, i have taken the code and most of the information form :-
https://docs.oracle.com/database/121/ARPLS/d_crypto.htm#ARPLS65690

Hope it helps in basic understanding of package how to use and how it can help in encrypting the data and to decrypt the same, before using make sure you have execute grant over the package.

You can test the script live on :-
https://livesql.oracle.com/

Please comment your feedback about the article .. and share if you like :)

Thanks,
Mohit