Encrypt and Decrypt Data with Oracle DBMS_CRYPTO

DBMS_CRYPTO is a library that provides an interface to encrypt and decrypt stored data. It supports several encryption algorithms. This covers reversible encryption with the AES algorithm.

Unlike one-way hashing, PII handling usually needs reversible encryption: store data encrypted, then decrypt it when showing it to users. Here we use AES-128 with a 128-bit (16-byte) symmetric key.

Install DBMS_CRYPTO

1. Log in to the Oracle account with SYSDBA privileges:

sqlplus / as sysdba

2. Add the CRYPTO package:

@$ORACLE_HOME/rdbms/admin/dbmsobtk.sql
@$ORACLE_HOME/rdbms/admin/prvtobtk.plb

3. Grant package execute privileges:

grant execute on dbms_crypto to public;
grant execute on dbms_obfuscation_toolkit to public;

Encryption algorithms

Algorithm	Description
DES	A symmetric-key algorithm that splits data into 64-bit blocks and uses a 56-bit key for each. Security is no longer considered sufficient, so usage is declining.
3DES	Applies the DES algorithm two or three times. Uses 112-bit and 168-bit keys. Because it repeats DES, encryption and decryption take longer than other symmetric algorithms.
AES 128, 192, 256	Splits data into 128-bit blocks and uses 128-, 192-, or 256-bit keys. Designed to be stronger than DES and established as an encryption standard.

Chaining modes

Mode	Description
ECB	Encrypts each original data block independently.
CBC (Cipher Block Chaining)	XORs the current block with the previously encrypted block, then encrypts it. Prevents identical plaintext from producing identical ciphertext, which can happen with ECB.

Padding modes

Mode	Description
PKCS5	PKCS #5 (Password-Based Cryptography Standard) padding.
NONE	No padding. Encryption fails unless the data size is a multiple of the block size (128 bits), so you must check the data size.
ZERO	Fills remaining bytes in the last block with zeros. Should be used only for data that marks the end with a zero value, such as some strings.

Function signatures

DBMS_CRYPTO.ENCRYPT (
  src          IN RAW,          -- data to encrypt
  cipher_type  IN PLS_INTEGER,  -- algorithm, chain, and padding
  key          IN RAW,          -- encryption key
  init_vector  IN RAW DEFAULT NULL -- IW: NULL or 0
) RETURN RAW;

DBMS_CRYPTO.DECRYPT(
  src IN RAW,          -- data to decrypt
  typ IN PLS_INTEGER,
  key IN RAW,
  iv  IN RAW DEFAULT NULL
) RETURN RAW;

Create a package and use the functions

test1234test1234 is the default encryption key and is used when no key parameter is passed.

Package specification

CREATE OR REPLACE PACKAGE TEST.CRYPTO IS
  FUNCTION ENCRYPT (
    input_string IN VARCHAR2,
    key_data IN VARCHAR2 := 'test1234test1234'
  ) RETURN RAW;

  FUNCTION DECRYPT (
    input_string IN VARCHAR2,
    key_data IN VARCHAR2 := 'test1234test1234'
  ) RETURN VARCHAR2;
END CRYPTO;

Package body

Uses the AES128 + CBC + PKCS5 combination.

CREATE OR REPLACE PACKAGE BODY TEST.CRYPTO IS
  SQLERRMSG VARCHAR2(255);
  SQLERRCDE NUMBER;

  -- Encrypt function
  FUNCTION encrypt (
    input_string IN VARCHAR2,
    key_data IN VARCHAR2 := 'test1234test1234'
  ) RETURN RAW IS
    input_raw RAW(1024);
    -- Convert plaintext key to RAW
    key_raw RAW(16) := UTL_RAW.CAST_TO_RAW(key_data);
    v_out_raw RAW(1024);
    -- Use AES algorithm, CBC chaining, PKCS5 padding
    AES_CBC_PKCS5 CONSTANT PLS_INTEGER :=
      DBMS_CRYPTO.ENCRYPT_AES128
      + DBMS_CRYPTO.CHAIN_CBC
      + DBMS_CRYPTO.PAD_PKCS5;
  BEGIN
    IF input_string IS NULL THEN
      RETURN NULL;
    END IF;

    -- Convert the string to encrypt into RAW
    input_raw := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
    v_out_raw := DBMS_CRYPTO.ENCRYPT(
      src => input_raw,
      typ => AES_CBC_PKCS5,
      key => key_raw
    );
    -- Return encrypted RAW data
    RETURN v_out_raw;
  END encrypt;

  -- Decrypt function
  FUNCTION decrypt (
    input_string IN VARCHAR2,
    key_data IN VARCHAR2 := 'test1234test1234'
  ) RETURN VARCHAR2 IS
    -- Convert plaintext key to RAW
    key_raw RAW(16) := UTL_RAW.CAST_TO_RAW(key_data);
    output_raw RAW(1024);
    v_out_string VARCHAR2(1024);
    -- Use AES algorithm, CBC chaining, PKCS5 padding
    AES_CBC_PKCS5 CONSTANT PLS_INTEGER :=
      DBMS_CRYPTO.ENCRYPT_AES128
      + DBMS_CRYPTO.CHAIN_CBC
      + DBMS_CRYPTO.PAD_PKCS5;
  BEGIN
    IF input_string IS NULL THEN
      RETURN NULL;
    END IF;

    output_raw := DBMS_CRYPTO.DECRYPT(
      src => input_string,
      typ => AES_CBC_PKCS5,
      key => key_raw
    );
    -- Convert decrypted RAW data to a UTF-8 string
    v_out_string := UTL_I18N.RAW_TO_CHAR(output_raw, 'AL32UTF8');
    -- Return decrypted string data
    RETURN v_out_string;
  END decrypt;
END CRYPTO;

Packages, functions, and variables used

Type	Name	Description
Data type	RAW	Stores binary data or a hexadecimal byte string (maximum length: 2000 bytes).
Data type	PLS_INTEGER	Unlike INTEGER or NUMBER, which use library-based numeric operations, PLS_INTEGER uses machine arithmetic and is faster.
Package	UTL_RAW	Provides functions related to RAW data.
Function	UTL_RAW.CAST_TO_RAW	Converts a STRING to RAW.
Package	UTL_I18N	Provides compatibility features across countries and languages.
Function	UTL_I18N.STRING_TO_RAW	Converts a STRING to RAW.

UTF8 vs AL32UTF8

Character set	Unicode version	Unicode encoding	Variable length	Supplementary characters	Oracle version
AL32UTF8	3.0~3.1	UTF-8	4 bytes	Supports U+10000~U+10FFFF (emoji and more CJK characters)	9i and later
UTF8	2.1~3.0	UTF-8	3 bytes	No	8~9i

Because the default database character set in this Oracle environment is AL32UTF8, the two functions return the same result.

-- Current database character set
-- Result: NLS_CHARACTERSET AL32UTF8
SELECT * FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';

-- Result: 5445535431323334
SELECT UTL_RAW.CAST_TO_RAW('test1234') FROM DUAL;

-- Result: 5445535431323334
SELECT UTL_I18N.STRING_TO_RAW('test1234', 'AL32UTF8') FROM DUAL;

Use the created functions

-- Encrypt
SELECT CRYPTO.ENCRYPT('01012345678') FROM DUAL;

-- Decrypt
SELECT CRYPTO.DECRYPT('357788CC5A3C8551550D62EA308CCF30') FROM DUAL;