Perform cryptographic operations with DBMS_CRYPTO - PolarDB

DBMS_CRYPTO provides functions and stored procedures for encrypting and decrypting RAW, BLOB, and CLOB data, computing one-way hash digests, generating message authentication codes (MAC), and producing cryptographically secure random bytes.

Subprograms

All subprograms accept a typ parameter that combines algorithm, chaining mode, and padding mode constants using integer addition (for example, ENCRYPT_DES + CHAIN_CBC + PAD_PKCS5). Pre-built cipher suite constants such as DES_CBC_PKCS5 are also available as shorthand.

Subprogram	Return type	Description
`ENCRYPT(src, typ, key, iv)`	`RAW`	Encrypts RAW data.
`ENCRYPT(dst INOUT, src BLOB, typ, key, iv)`	—	Encrypts BLOB data; writes the result to `dst`.
`ENCRYPT(dst INOUT, src CLOB, typ, key, iv)`	—	Encrypts CLOB data; writes the result to `dst`.
`DECRYPT(src, typ, key, iv)`	`RAW`	Decrypts encrypted RAW data.
`DECRYPT(dst INOUT BLOB, src BLOB, typ, key, iv)`	—	Decrypts encrypted BLOB data; writes the result to BLOB `dst`.
`DECRYPT(dst INOUT CLOB, src BLOB, typ, key, iv)`	—	Decrypts encrypted BLOB data; writes the result to CLOB `dst`.
`Hash(src RAW\|CLOB, typ)`	`RAW`	Computes a hash digest of RAW or CLOB data.
`MAC(src RAW\|CLOB, typ, key)`	`RAW`	Computes an HMAC of RAW or CLOB data.
`RANDOMBYTES(number_bytes)`	`RAW`	Returns a random byte string of the specified length.

When to use which subprogram

Functions vs. stored procedures

Use the ENCRYPT and DECRYPT functions for RAW data. To encrypt a VARCHAR2 value, first convert it to RAW using utl_raw.cast_to_raw().
Use the ENCRYPT and DECRYPT stored procedures for BLOB and CLOB data. The procedures write output to the dst parameter, overwriting its original value.

HASH vs. MAC

Use Hash when you need a one-way fingerprint of data for integrity checking. Hash functions take only the data as input.
Use MAC when you need to authenticate both the data and the sender. MAC functions require a shared secret key, making the output impossible to reproduce without that key.

Encryption algorithms and constants

All typ values are constructed by adding one constant from each of the following groups.

Block cipher algorithms

Constant	Algorithm
`ENCRYPT_DES`	Data Encryption Standard (DES)
`ENCRYPT_3DES`	Triple Data Encryption Standard (3DES)
`ENCRYPT_AES`	Advanced Encryption Standard (AES)
`ENCRYPT_AES128`	AES with fixed 128-bit key
`ENCRYPT_AES192`	AES with fixed 192-bit key
`ENCRYPT_AES256`	AES with fixed 256-bit key

Block cipher chaining modes

Constant	Mode
`CHAIN_CBC`	Cipher Block Chaining (CBC)
`CHAIN_ECB`	Electronic Codebook (ECB)

Padding modes

Constant	Padding
`PAD_PKCS5`	PKCS5 padding
`PAD_NONE`	No padding

Pre-built cipher suite constants

Constant	Equivalent to
`DES_CBC_PKCS5`	`ENCRYPT_DES + CHAIN_CBC + PAD_PKCS5`

Hash algorithms (for the Hash function)

Constant	Algorithm
`HASH_MD4`	MD4 — 128-bit digest
`HASH_MD5`	MD5 — 128-bit digest
`HASH_SH1`	SHA1 — 160-bit digest
`HASH_SH256`	SHA256 — 256-bit digest
`HASH_SH384`	SHA384 — 384-bit digest
`HASH_SH512`	SHA512 — 512-bit digest

MAC algorithms (for the MAC function)

Constant	Algorithm
`HMAC_MD5`	HMAC-MD5
`HMAC_SH1`	HMAC-SHA1
`HMAC_SH256`	HMAC-SHA256
`HMAC_SH384`	HMAC-SHA384
`HMAC_SH512`	HMAC-SHA512

MD4, MD5, and SHA1 are considered cryptographically weak. Avoid them in new applications. Use SHA256 or stronger algorithms for security-sensitive workloads.

ENCRYPT function

Encrypts RAW data using the specified algorithm and returns the encrypted RAW value.

Syntax

DBMS_CRYPTO.ENCRYPT(
   src IN RAW,
   typ IN PLS_INTEGER,
   key IN RAW,
   iv  IN RAW DEFAULT NULL)
 RETURN RAW;

Parameters

Parameter	Description
`src`	RAW data to encrypt.
`typ`	The encryption type. Combine one algorithm constant, one chaining mode constant, and one padding constant using `+`. See Encryption algorithms and constants.
`key`	The encryption key.
`iv`	The initialization vector. Default: `NULL`.

Example

DECLARE
    enc_typ integer;
    enc_res raw;
BEGIN
    enc_typ := dbms_crypto.ENCRYPT_DES + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5;
    enc_res := dbms_crypto.encrypt(utl_raw.cast_to_raw('1234567890!@#$%^&*qwertyuiop'), enc_typ, utl_raw.cast_to_raw('314639'), NULL);
    dbms_output.put_line(enc_res);
END;
-- output --
-- \x12dcbddc1c6f743401e8c383855e9a06dc8dde2b997a07067afd386754f3a84d

ENCRYPT stored procedure

Encrypts BLOB or CLOB data and writes the result to the dst parameter.

Syntax

DBMS_CRYPTO.ENCRYPT(
 dst IN OUT BLOB,
 src IN BLOB,
 typ IN PLS_INTEGER,
 key IN RAW,
 iv IN RAW DEFAULT NULL);

DBMS_CRYPTO.ENCRYPT(
 dst IN OUT BLOB,
 src IN CLOB,
 typ IN PLS_INTEGER,
 key IN RAW,
 iv IN RAW DEFAULT NULL);

Parameters

Parameter	Description
`dst`	The output BLOB. The original value is overwritten.
`src`	The BLOB or CLOB data to encrypt.
`typ`	The encryption type. Combine one algorithm constant, one chaining mode constant, and one padding constant using `+`. See Encryption algorithms and constants.
`key`	The encryption key.
`iv`	The initialization vector. Default: `NULL`.

Example

DECLARE
    enc_typ integer;
    enc_res raw;
BEGIN
    enc_typ := dbms_crypto.DES_CBC_PKCS5;

    -- procedure with blob
    dbms_crypto.encrypt(enc_res, utl_raw.cast_to_raw('1234567890!@#$%^&*qwertyuiop'), enc_typ, utl_raw.cast_to_raw('314639'), NULL);
    dbms_output.put_line(enc_res);
    -- procedure with clob
    dbms_crypto.encrypt(enc_res, '1234567890!@#$%^&*qwertyuiop'::clob, enc_typ, utl_raw.cast_to_raw('314639'), NULL);
    dbms_output.put_line(enc_res);
END;
-- output
-- \x12dcbddc1c6f743401e8c383855e9a06dc8dde2b997a07067afd386754f3a84d
-- \x12dcbddc1c6f743401e8c383855e9a06dc8dde2b997a07067afd386754f3a84d

DECRYPT function

Decrypts encrypted RAW data using the same algorithm, key, and initialization vector used during encryption.

Syntax

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

Parameters

Parameter	Description
`src`	The encrypted RAW data to decrypt.
`typ`	The same encryption type used during encryption.
`key`	The same encryption key used during encryption.
`iv`	The same initialization vector used during encryption. Default: `NULL`.

Example

DECLARE
    enc_typ integer;
    dec_res raw;
BEGIN
    enc_typ := dbms_crypto.DES_CBC_PKCS5;
    dec_res := dbms_crypto.decrypt(dbms_crypto.encrypt(utl_raw.cast_to_raw('1234567890!@#$%^&*qwertyuiop'), enc_typ, utl_raw.cast_to_raw('314639'), NULL), enc_typ, utl_raw.cast_to_raw('314639'), NULL);
    dbms_output.put_line(dec_res);
END;
-- output
-- \x3132333435363738393021402324255e262a71776572747975696f70

DECRYPT stored procedures

Decrypts encrypted BLOB or CLOB data and writes the result to the dst parameter.

Syntax

DBMS_CRYPTO.DECRYPT(
   dst IN OUT        BLOB,
   src IN            BLOB,
   typ IN            PLS_INTEGER,
   key IN            RAW,
   iv  IN            RAW          DEFAULT NULL);

DBMS_CRYPT.DECRYPT(
   dst IN OUT        CLOB,
   src IN            BLOB,
   typ IN            PLS_INTEGER,
   key IN            RAW,
   iv  IN            RAW          DEFAULT NULL);

Parameters

Parameter	Description
`dst`	The output BLOB or CLOB. The original value is overwritten.
`src`	The encrypted BLOB data to decrypt.
`typ`	The same encryption type used during encryption.
`key`	The same encryption key used during encryption.
`iv`	The same initialization vector used during encryption. Default: `NULL`.

Example

DECLARE
    enc_typ integer;
    enc_res raw;
    dec_res raw;
BEGIN
    enc_typ := dbms_crypto.DES_CBC_PKCS5;

    -- procedure with blob
    dbms_crypto.encrypt(enc_res, utl_raw.cast_to_raw('1234567890!@#$%^&*qwertyuiop'), enc_typ, utl_raw.cast_to_raw('314639'), NULL);
    dbms_crypto.decrypt(dec_res, enc_res, enc_typ, utl_raw.cast_to_raw('314639'), NULL);
    dbms_output.put_line(dec_res);

    -- procedure with clob
    dbms_crypto.encrypt(enc_res, '1234567890!@#$%^&*qwertyuiop'::clob, enc_typ, utl_raw.cast_to_raw('314639'), NULL);
    dbms_crypto.decrypt(dec_res, enc_res::clob, enc_typ, utl_raw.cast_to_raw('314639'), NULL);
    dbms_output.put_line(dec_res);
END;
-- output
-- \x3132333435363738393021402324255e262a71776572747975696f70
-- \x3132333435363738393021402324255e262a71776572747975696f70

HASH function

Computes the hash digest of RAW or CLOB input data.

Syntax

DBMS_CRYPTO.Hash (
   src IN RAW,
   typ IN PLS_INTEGER)
 RETURN RAW;

DBMS_CRYPTO.Hash (
   src IN CLOB,
   typ IN PLS_INTEGER)
 RETURN RAW;

Parameters

Parameter	Description
`src`	The input data to hash. Accepts RAW or CLOB.
`typ`	The hash algorithm constant. See Hash algorithms.

Example

DECLARE
    hash_res raw;
BEGIN
    hash_res := dbms_crypto.hash(utl_raw.cast_to_raw('1234567890!@#$%^&*qwertyuiop'), dbms_crypto.HASH_SH256);
    dbms_output.put_line(hash_res);
END;
-- output
-- \x17220452ab8b4134747fbb5ca594106fa06cef7e0571a88ca31e19447d42c894

MAC function

Computes an HMAC (Hash-based Message Authentication Code) of RAW or CLOB input data using a shared secret key. Unlike Hash, the output is tied to both the data and the key, so only parties with the key can verify or reproduce it.

Syntax

DBMS_CRYPTO.MAC (
   src IN RAW,
   typ IN PLS_INTEGER,
   key IN RAW)
 RETURN RAW;

DBMS_CRYPTO.MAC (
   src IN CLOB,
   typ IN PLS_INTEGER,
   key IN RAW)
 RETURN RAW;

Parameters

Parameter	Description
`src`	The input data to authenticate. Accepts RAW or CLOB.
`typ`	The MAC algorithm constant. See MAC algorithms.
`key`	The secret key for the MAC algorithm.

Example

DECLARE
    mac_res raw;
BEGIN
    mac_res := dbms_crypto.mac(utl_raw.cast_to_raw('1234567890!@#$%^&*qwertyuiop'), dbms_crypto.HMAC_MD5, utl_raw.cast_to_raw('314639'));
    dbms_output.put_line(mac_res);
END;
-- output
-- \xad8641796c66172f187357ca12ceb211

RANDOMBYTES function

Returns a cryptographically secure random byte string of the specified length. Use this function to generate encryption keys and initialization vectors.

Syntax

DBMS_CRYPTO.RANDOMBYTES (
   number_bytes IN INTEGER)
 RETURN RAW;

Parameters

Parameter	Description
`number_bytes`	The number of random bytes to return. Maximum: 1,024.

Example

select dbms_crypto.randombytes(16);
            randombytes
------------------------------------
 \x560d5037ee298b187a421df030f5ee35
(1 row)