PolarDB:DBMS_CRYPTO

Functions and stored procedures in the DBMS_CRYPTO package

• Similar to Oracle databases, PolarDB for Oracle supports the following error messages:

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

• Unlike Oracle databases, PolarDB does not return error ORA-28233 if you attempt to encrypt previously encrypted information.
• Note that RAW and BLOB are synonyms of PostgreSQL BYTEA data types, while CLOB is a synonym of TEXT.

DECRYPT

Parameters

Example

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;

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. Syntax of the ENCRYPT function:

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

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

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

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 value of RAW or CLOB data by using a user-specified hash algorithm. Syntax of the HASH function:

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

Parameters

Example

The following example uses the DBMS_CRYPTO.HASH function 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 MAC function to return the HMAC value of RAW or CLOB data. Syntax of the MAC function:

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

Parameters

Example

The following example uses DBMS_CRYPTO.MAC to retrieve the HMAC 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 a cryptographically secure random sequence of bytes. You can specify the length of the RAW value. Syntax of RANDOMBYTES:

RANDOMBYTES
  (number_bytes IN INTEGER) RETURNS RAW

Parameter

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 in the complete range available for the Oracle BINARY_INTEGER data type. Syntax of RANDOMINTEGER:

RANDOMINTEGER() RETURNS INTEGER

Example

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

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

RANDOMNUMBER

You can use the RANDOMNUMBER function to return an integer in the Oracle NUMBER data type in the range of [0..2**128-1]. Syntax of RANDOMNUMBER:

RANDOMNUMBER() RETURNS NUMBER

Example

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

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