You can use the functions and stored procedures in the DBMS_CRYPTO package to encrypt or decrypt RAW, BLOB, or CLOB data. You can also use DBMS_CRYPTO functions to generate cryptographically secure random values.

Table 1. DBMS_CRYPTO functions and stored procedures
Function/stored procedure Return type Description
DECRYPT(src, typ, key, iv) RAW Decrypts RAW data.
DECRYPT(dst INOUT, src, typ, key, iv) N/A Decrypts BLOB data.
DECRYPT(dst INOUT, src, typ, key, iv) N/A Decrypts CLOB data.
ENCRYPT(src, typ, key, iv) RAW Encrypts RAW data.
ENCRYPT(dst INOUT, src, typ, key, iv) N/A Encrypts BLOB data.
ENCRYPT(dst INOUT, src, typ, key, iv) N/A Encrypts CLOB data.
HASH(src, typ) RAW Applies a hash algorithm to RAW data.
HASH(src) RAW Applies a hash algorithm to CLOB data.
MAC(src, typ, key) RAW Returns the hashed MAC value of the given RAW data. The hash algorithm and key are user-specified.
MAC(src, typ, key) RAW Returns the hashed MAC value of the given CLOB data. The hash algorithm and key are user-specified.
RANDOMBYTES(number bytes) RAW Returns a specified number of cryptographically secure random bytes.
RANDOMINTEGER() INTEGER Returns a random integer.
RANDOMNUMBER() NUMBER Returns a random number.

Similar to Oracle databases, PolarDB-O supports the following error messages:

ORA-28239-DBMS_CRYPTO.KeyNull
ORA-28829-DBMS_CRYPTO.CipherSuiteNull
ORA-28827-DBMS_CRYPTO.CipherSuiteInvalid

Different from Oracle databases, PolarDB-O will not return error ORA-28233 if you re-encrypt previously encrypted information.

Note that RAW and BLOB are synonyms of PostgreSQL BYTEA data types, while CLOB is a synonym of TEXT.

DECRYPT

You can use the DECRYPT function or stored procedure to decrypt data based on a specified encryption algorithm, key, and optional initialization vector. The following code describes the syntax of the DECRYPT function:

DECRYPT
  (src IN RAW, typ IN INTEGER, key IN RAW, iv IN RAW 
   DEFAULT NULL) RETURN RAW
            

The following code describes the syntax of the DECRYPT stored procedure:

DECRYPT
  (dst INOUT BLOB, src IN BLOB, typ IN INTEGER, key IN RAW, 
   iv IN RAW DEFAULT NULL)

Or

DECRYPT
  (dst INOUT CLOB, src IN CLOB, typ IN INTEGER, key IN RAW, 
   iv IN RAW DEFAULT NULL) 

When DECRYPT is called as a stored procedure, DECRYPT returns BLOB or CLOB data to the user-specified BLOB.

Parameters

dst

Specifies the name of a BLOB. The DECRYPT stored procedure writes the output into the BLOB and overwrites any existing data in the BLOB.

src

Specifies the source data to be decrypted. If you call DECRYPT as a function, you must specify RAW data. If you call DECRYPT as a stored procedure, you must specify BLOB or CLOB data.

typ

Specifies the block cipher type and any modifiers. The value of the parameter must match the type specified when the source data was encrypted. PolarDB-O supports the following block cipher algorithms, modifiers, and cipher suites.

Block cipher algorithms
ENCRYPT_DES CONSTANT INTEGER := 1;
ENCRYPT_3DES CONSTANT INTEGER := 3;
ENCRYPT_AES CONSTANT INTEGER := 4;
ENCRYPT_AES128 CONSTANT INTEGER := 6;
Block cipher modifiers
CHAIN_CBC CONSTANT INTEGER := 256;
CHAIN_ECB CONSTANT INTEGER := 768;
Block cipher padding modifiers
PAD_PKCS5 CONSTANT INTEGER := 4096;
PAD_NONE CONSTANT INTEGER := 8192;
Block cipher suites
DES_CBC_PKCS5 CONSTANT INTEGER := ENCRYPT_DES + CHAIN_CBC + PAD_PKCS5;
DES3_CBC_PKCS5 CONSTANT INTEGER := ENCRYPT_3DES + CHAIN_CBC + PAD_PKCS5;
AES_CBC_PKCS5 CONSTANT INTEGER := ENCRYPT_AES + CHAIN_CBC + PAD_PKCS5;
key

Specifies the user-defined decryption key. The value of the parameter must match the key specified when the source data was encrypted.

iv

Optional. Specifies the initialization vector. If you specify an initialization vector when encrypting the source data, you must specify the parameter when decrypting the source data. The default value is NULL.

Example

The following example uses the DBMS_CRYPTO.DECRYPT function to decrypt the encrypted password that is retrieved from the passwords table.

CREATE TABLE passwords
(
  principal  VARCHAR2(90) PRIMARY KEY,  -- username
  ciphertext RAW(9)                     -- encrypted password
);
CREATE FUNCTION get_password(username VARCHAR2) RETURN RAW AS
 typ        INTEGER := DBMS_CRYPTO.DES_CBC_PKCS5;
 key        RAW(128) := 'my secret key';
 iv         RAW(100) := 'my initialization vector';
 password   RAW(2048);
BEGIN

  SELECT ciphertext INTO password FROM passwords WHERE principal = username;

  RETURN dbms_crypto.decrypt(password, typ, key, iv);
END;

Note that when you call DECRYPT, you must pass the same password type, key value, and initialization vector used when you encrypted the object.

ENCRYPT

You can use the ENCRYPT function or stored procedure to encrypt RAW, BLOB, or CLOB data based on a user-defined algorithm, key, and optional initialization vector. The following code describes the syntax of the DECRYPT function:

ENCRYPT
  (src IN RAW, typ IN INTEGER, key IN RAW, 
   iv IN RAW DEFAULT NULL) RETURN RAW

The following code describes the syntax of the DECRYPT stored procedure:

ENCRYPT
  (dst INOUT BLOB, src IN BLOB, typ IN INTEGER, key IN RAW, 
   iv IN RAW DEFAULT NULL)

Or

ENCRYPT
  (dst INOUT BLOB, src IN CLOB, typ IN INTEGER, key IN RAW, 
   iv IN RAW DEFAULT NULL) 

When you call ENCRYPT as a stored procedure, ENCRYPT returns BLOB or CLOB data to the user-specified BLOB.

Parameters

dst

Specifies the name of a BLOB. The ENCRYPT stored procedure writes the output into the BLOB and overwrites any existing data in the BLOB.

src

Specifies the source data to be encrypted. If you call ENCRYPT as a function, you must specify RAW data. If you call ENCRYPT as a stored procedure, you must specify BLOB or CLOB data.

typ
Specifies the block cipher type and any modifiers. PolarDB-O supports the following block cipher algorithms, modifiers, and cipher suites.
Block cipher algorithms
ENCRYPT_DES CONSTANT INTEGER := 1;
ENCRYPT_3DES CONSTANT INTEGER := 3;
ENCRYPT_AES CONSTANT INTEGER := 4;
ENCRYPT_AES128 CONSTANT INTEGER := 6;
Block cipher modifiers
CHAIN_CBC CONSTANT INTEGER := 256;
CHAIN_ECB CONSTANT INTEGER := 768;
Block cipher padding modifiers
PAD_PKCS5 CONSTANT INTEGER := 4096;
PAD_NONE CONSTANT INTEGER := 8192;
Block cipher suites
DES_CBC_PKCS5 CONSTANT INTEGER := ENCRYPT_DES + CHAIN_CBC + PAD_PKCS5;
DES3_CBC_PKCS5 CONSTANT INTEGER := ENCRYPT_3DES + CHAIN_CBC + PAD_PKCS5;
AES_CBC_PKCS5 CONSTANT INTEGER := ENCRYPT_AES + CHAIN_CBC + PAD_PKCS5;
key

Specifies the encryption Key.

iv

Optional. Specifies the initialization vector. The default value is NULL.

Example

The following example uses the DBMS_CRYPTO.DES_CBC_PKCS5 block cipher suite (a set of predefined algorithms and modifiers) to encrypt the value that is retrieved from the passwords table:

CREATE TABLE passwords
(
  principal  VARCHAR2(90) PRIMARY KEY,  -- username
  ciphertext RAW(9)                     -- encrypted password
);
CREATE PROCEDURE set_password(username VARCHAR2, cleartext RAW) AS
 typ        INTEGER := DBMS_CRYPTO.DES_CBC_PKCS5;
 key        RAW(128) := 'my secret key';
 iv         RAW(100) := 'my initialization vector';
 encrypted  RAW(2048);
BEGIN
  encrypted := dbms_crypto.encrypt(cleartext, typ, key, iv); 
  UPDATE passwords SET ciphertext = encrypted WHERE principal = username;
END;

During password encryption, ENCRYPT uses "my secret key" as the key and "my initialization vector" as the initialization vector. You must use the same key and initialization vector when decrypting the password.

HASH

You can use the HASH function to return the hash values of RAW or CLOB data. The hash algorithm is user-specified. The HASH function supports the following syntax:

HASH
  (src IN RAW, typ IN INTEGER) RETURN RAW
HASH
  (src IN CLOB, typ IN INTEGER) RETURN RAW 

Parameters

src

Specifies the data for which the hash value will be calculated. The RAW, BLOB, or CLOB data types are supported.

typ
Specifies the hash algorithm. PolarDB-O supports the following hash algorithms.
Hash algorithms
HASH_MD4 CONSTANT INTEGER := 1;
HASH_MD5 CONSTANT INTEGER := 2;
HASH_SH1 CONSTANT INTEGER := 3;

Example

The following example uses DBMS_CRYPTO.HASH to retrieve the MD5 hash value of the "cleartext source" string:

DECLARE
  typ        INTEGER := DBMS_CRYPTO.HASH_MD5;
  hash_value RAW(100);
BEGIN

  hash_value := DBMS_CRYPTO.HASH('cleartext source', typ);

END;

MAC

You can use a specified MAC function to return the hashed MAC value of RAW or CLOB data. The HASH function supports the following syntax:

MAC
  (src IN RAW, typ IN INTEGER, key IN RAW) RETURN RAW
MAC
  (src IN CLOB, typ IN INTEGER, key IN RAW) RETURN RAW

Parameters

src

Specifies the data for which the hash value will be calculated. The RAW, BLOB, or CLOB data types are supported.

typ
Specifies the MAC function type. PolarDB-O supports the following MAC function types.
MAC functions
HMAC MD5 CONSTANT INTEGER := 1;
HMAC SH1 CONSTANT INTEGER := 2;
key

Specifies the key that is used to calculate the hashed MAC value.

Example

The following example uses DBMS_CRYPTO.MAC to retrieve the hash value of the "cleartext source" string:

DECLARE
  typ       INTEGER := DBMS_CRYPTO.HMAC_MD5;
  key       RAW(100) := 'my secret key';
  mac_value RAW(100);
BEGIN

  mac_value := DBMS_CRYPTO.MAC('cleartext source', typ, key);

END;

During the calculation, DBMS_CRYPTO.MAC uses "my secret key" as the key.

RANDOMBYTES

You can use the RANDOMBYTES function to return a RAW value that contains cryptographically random bytes. You can specify the length for the RAW value. The following code describes the syntax of the RANDOMBYTES function:

RANDOMBYTES
  (number_bytes IN INTEGER) RETURNS RAW

Parameters

number_bytes

Specifies the number of random bytes that are returned by the function.

Example

The following example uses RANDOMBYTES to return a value that is 1,024 bytes in length:

DECLARE
  result RAW(1024);
BEGIN
  result := DBMS_CRYPTO.RANDOMBYTES(1024);
END;

RANDOMINTEGER

You can use the RANDOMINTEGER function to return a random integer between 0 and 268, 435, or 455. The following code describes the syntax of the RANDOMINTEGER function:

RANDOMINTEGER() RETURNS INTEGER

Example

The following example uses the RANDOMINTEGER function to return a cryptographically secure random integer:

DECLARE
  result INTEGER;
BEGIN
  result := DBMS_CRYPTO.RANDOMINTEGER();
  DBMS_OUTPUT.PUT_LINE(result);
END;

RANDOMNUMBER

You can use the RANDOMNUMBER function to return a random number between 0 and 268, 435, or 455. The following code describes the syntax of the RANDOMNUMBER function:

RANDOMNUMBER() RETURNS NUMBER

Example

The following example uses the RANDOMINTEGER function to return a cryptographically secure random number:

DECLARE
  result NUMBER;
BEGIN
  result := DBMS_CRYPTO.RANDOMNUMBER();
  DBMS_OUTPUT.PUT_LINE(result);
END;