[DBMS_CRYPTO] Data Encryption and Decryption in Oracle

Encryption: The process of converting information or data into a code, especially to prevent unauthorized access.

Decryption: The conversion of encrypted data into its original form is called Decryption. It is generally a reverse process of encryption. It decodes the encrypted information so that an authorized user can only decrypt the data because decryption requires a secret key or password.

Here I am giving an example to encrypt and decrypt the data in Oracle using the DBMS_CRYPTO package.

DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications.

To demonstrate this, I have created the following function to return the encrypted and decrypted data.

Step 1: Create function to return encrypted raw as below,

CREATE OR REPLACE 

FUNCTION encrypt_string (p_string VARCHAR2)

RETURN RAW AS

   l_key        VARCHAR2 (2000) := '1234567890999899';

   l_mod NUMBER

         :=   dbms_crypto.encrypt_aes128

            + dbms_crypto.chain_cbc

            + dbms_crypto.pad_pkcs5;

   l_encrypted_raw        RAW (2000);

   l_return RAW (2000);

BEGIN

   l_encrypted_raw :=

      dbms_crypto.encrypt (utl_i18n.string_to_raw (p_string, 'AL32UTF8'),

                           l_mod,

                           utl_i18n.string_to_raw (l_key, 'AL32UTF8'));

   

     -- dbms_output.put_line ('Encrypted=' || l_encrypted_raw);

     

      RETURN l_encrypted_raw;

END encrypt_string;

/

Step 2: Create function to return decrypted string as below,

CREATE OR REPLACE 

FUNCTION decrypt_string (p_string VARCHAR2)

RETURN VARCHAR2 AS

   l_key        VARCHAR2 (2000) := '1234567890999899';

   l_mod NUMBER

         :=   dbms_crypto.encrypt_aes128

            + dbms_crypto.chain_cbc

            + dbms_crypto.pad_pkcs5;

   l_decrypted_raw        RAW (2000);

   l_decrypted_string     VARCHAR2(2000);

BEGIN

   l_decrypted_raw :=

      dbms_crypto.decrypt (p_string,

                           l_mod,

                           utl_i18n.string_to_raw (l_key, 'AL32UTF8'));

   l_decrypted_string := utl_i18n.raw_to_char (l_decrypted_raw);                        

   

   --DBMS_OUTPUT.put_line ('Decrypted=' || UTL_I18N.raw_to_char (l_decrypted_raw));

   

   RETURN l_decrypted_string;

END decrypt_string;

/

Step 3: Query the function as below to get the results,

SELECT 'MY_STRING' original_string,

       encrypt_string ('MY_STRING') encrypted_string,

       decrypt_string(encrypt_string ('MY_STRING')) decrypted_string

FROM dual;

Output:

Restrictions:

The VARCHAR2 datatype is not directly supported by DBMS_CRYPTO. Before you can perform cryptographic operations on data of the type VARCHAR2, you must convert it to the uniform database character set AL32UTF8, and then convert it to the RAW datatype. After performing these conversions, you can then encrypt it with the DBMS_CRYPTO package.

Conversion Rules:

Rule 1: To convert VARCHAR2 to RAW, use the UTL_I18N.STRING_TO_RAW function to perform the following steps:

• Convert VARCHAR2 in the current database character set to VARCHAR2 in the AL32UTF8 database character.  

• Convert VARCHAR2 in the AL32UTF8 database character set to RAW.

Syntax example:

UTL_I18N.STRING_TO_RAW (string, 'AL32UTF8');

Rule 2: To convert RAW to VARCHAR2, use the UTL_I18N.RAW_TO_CHAR function to perform the following steps:

• Convert RAW to VARCHAR2 in the AL32UTF8 database character set.

• 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');

Reference:

• DBMS_CRYPTO

That's it.

Happy APEXing!!!...