All Products
Search

This Product

    PolarDB:DBMS_CRYPTO

    Document Center

    PolarDB:DBMS_CRYPTO

    Last Updated:Mar 27, 2024

    The DBMS_CRYPTO package provides functions and stored procedures that allow you to encrypt or decrypt RAW, binary large object (BLOB), and character large object (CLOB) data. You can use functions in the DBMS_CRYPTO package to generate cryptographically secure random numbers.

    Subprograms

    Subprogram

    Description

    ENCRYPT Function

    This function supports the Advanced Encryption Standard (AES), AES128, AES192, AES256, Data Encryption Standard (DES), and Triple Data Encryption Standard (3DES) encryption algorithms. It also supports the none and pkcs padding modes, as well as the cbc and ecb encryption modes.

    ENCRYPT Procedures

    These stored procedures support the AES, AES128, AES192, AES256, DES, and 3DES encryption algorithms. They also support the none and pkcs padding modes, as well as the cbc and ecb encryption modes.

    DECRYPT Function

    This function can be used to decrypt the content encrypted by the ENCRYPT function and procedures.

    DECRYPT Procedures

    These stored procedures can be used to decrypt the content encrypted by the ENCRYPT function and procedures.

    HASH Function

    This function supports the md4, md5, sha1, sha256, sha384, and sha512 message digest algorithms available for RAW, BLOB, and CLOB data.

    MAC Function

    This function supports the md5, sha1, sha256, sha384, and sha512 message authentication algorithms available for RAW, BLOB, and CLOB data.

    RANDOMBYTES Function

    This function generates random strings of up to 1,024 bytes in length.

    ENCRYPT Function

    This function uses a specified encryption algorithm to encrypt RAW data.

    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 that you want to encrypt.

    typ

    The encryption type and modifier of the stream or block.

    key

    The encryption key.

    iv

    The initialization vector. Default value: NULL.

    Example

    The following example shows how to encrypt RAW data by using a specified encryption algorithm: 

    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 Procedure

    This stored procedure uses a specified encryption algorithm to encrypt BLOB or CLOB data.

    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 data of the BLOB or CLOB type. The original value of the dst parameter is overwritten by the specified value.

    src

    BLOB or CLOB data that you want to encrypt.

    typ

    The encryption type and modifier of the stream or block.

    key

    The encryption key.

    iv

    The initialization vector. Default value: NULL.

    Example

    The following example shows how to encrypt BLOB or CLOB data by using a specified encryption algorithm: 

    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

    This function uses a specified encryption algorithm to decrypt RAW data that is encrypted.

    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

    RAW data that you want to decrypt.

    typ

    The encryption type and modifier of the stream or block.

    key

    The encryption key.

    iv

    The initialization vector. Default value: NULL.

    Example

    The following example shows how to use a specified encryption algorithm to decrypt RAW data that is encrypted: 

    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 Procedures

    These stored procedures use a specified encryption algorithm to decrypt BLOB or CLOB data that is encrypted.

    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 data of the BLOB or CLOB type. The original value of the dst parameter is overwritten by the specified value.

    src

    BLOB or CLOB data that you want to decrypt.

    typ

    The encryption type and modifier of the stream or block.

    key

    The encryption key.

    iv

    The initialization vector. Default value: NULL.

    Example

    The following example shows how to use a specified encryption algorithm to decrypt BLOB or CLOB data: 

    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

    This function is used to calculate the hash value of the input RAW 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 for which you want to calculate the hash value.

    typ

    The hash algorithm to use.

    Example

    The following example shows how to calculate the hash value of RAW data: 

    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 Functions

    These functions are used to calculate the HMAC value of input RAW data.

    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

    Input data for which you want to calculate the HMAC value.

    typ

    The MAC algorithm to use.

    key

    The key used for the MAC algorithm.

    Example

    The following example shows how to calculate the HMAC value of RAW data: 

    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

    This function is used to return a random value that has a specified length for RAW data.

    Syntax

    DBMS_CRYPTO.RANDOMBYTES (
   number_bytes IN INTEGER)
 RETURN RAW;

    Parameters

    Parameter

    Description

    number_bytes

    The number of bytes to be returned.

    Example

    The following example shows how to return a random value for a specified number of bytes:

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