DBMS_CRYPTO

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

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

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.

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.

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

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

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

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.

Specifies the encryption Key.

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

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

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

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

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

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;