7.2 DBMS_CRYPTO

Table of Contents Previous Next


7 Built-In Packages : 7.2 DBMS_CRYPTO

The DBMS_CRYPTO package provides functions and procedures that allow you to encrypt or decrypt RAW, BLOB or CLOB data. You can also use DBMS_CRYPTO functions to generate cryptographically strong random values.
DECRYPT(src, typ, key, iv)
Decrypts RAW data.
DECRYPT(dst INOUT, src, typ, key, iv)
Decrypts BLOB data.
DECRYPT(dst INOUT, src, typ, key, iv)
Decrypts CLOB data.
ENCRYPT(src, typ, key, iv)
Encrypts RAW data.
ENCRYPT(dst INOUT, src, typ, key, iv)
Encrypts BLOB data.
ENCRYPT(dst INOUT, src, typ, key, iv)
Encrypts CLOB data.
HASH(src, typ)
HASH(src)
MAC(src, typ, key)
Returns the hashed MAC value of the given RAW data using the specified hash algorithm and key.
MAC(src, typ, key)
Returns the hashed MAC value of the given CLOB data using the specified hash algorithm and key.
RANDOMBYTES(number_bytes)
DBMS_CRYPTO functions and procedures support the following Oracle Compatible error messages:
Unlike Oracle, Advanced Server will not return error ORA-28233 if you re-encrypt previously encrypted information.
Please note that RAW and BLOB are synonyms for the PostgreSQL BYTEA data type, and CLOB is a synonym for TEXT.
7.2.1 DECRYPT
The DECRYPT function or procedure decrypts data using a user-specified cipher algorithm, key and optional initialization vector. The signature of the DECRYPT function is:
The signature of the DECRYPT procedure is:
When invoked as a procedure, DECRYPT returns BLOB or CLOB data to a user-specified BLOB.
dst specifies the name of a BLOB to which the output of the DECRYPT procedure will be written. The DECRYPT procedure will overwrite any existing data currently in dst.
src specifies the source data that will be decrypted. If you are invoking DECRYPT as a function, specify RAW data; if invoking DECRYPT as a procedure, specify BLOB or CLOB data.
typ specifies the block cipher type and any modifiers. This should match the type specified when the src was encrypted. Advanced Server supports the following block cipher algorithms, modifiers and cipher suites:
key specifies the user-defined decryption key. This should match the key specified when the src was encrypted.
iv (optional) specifies an initialization vector. If an initialization vector was specified when the src was encrypted, you must specify an initialization vector when decrypting the src. The default is NULL.
The following example uses the DBMS_CRYPTO.DECRYPT function to decrypt an encrypted password retrieved from the passwords table:
Note that when calling DECRYPT, you must pass the same cipher type, key value and initialization vector that was used when ENCRYPTING the target.
7.2.2 ENCRYPT
The ENCRYPT function or procedure uses a user-specified algorithm, key, and optional initialization vector to encrypt RAW, BLOB or CLOB data. The signature of the ENCRYPT function is:
ENCRYPT
(
src IN RAW, typ IN INTEGER, key IN RAW,
iv IN RAW DEFAULT NULL) RETURN RAW
The signature of the ENCRYPT procedure is:
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 invoked as a procedure, ENCRYPT returns BLOB or CLOB data to a user-specified BLOB.
dst specifies the name of a BLOB to which the output of the ENCRYPT procedure will be written. The ENCRYPT procedure will overwrite any existing data currently in dst.
src specifies the source data that will be encrypted. If you are invoking ENCRYPT as a function, specify RAW data; if invoking ENCRYPT as a procedure, specify BLOB or CLOB data.
typ specifies the block cipher type that will be used by ENCRYPT, and any modifiers. Advanced Server supports the block cipher algorithms, modifiers and cipher suites listed below:
key specifies the encryption key.
iv (optional) specifies an initialization vector. By default, iv is NULL.
The following example uses the DBMS_CRYPTO.DES_CBC_PKCS5 Block Cipher Suite (a pre-defined set of algorithms and modifiers) to encrypt a value retrieved from the passwords table:
ENCRYPT uses a key value of my secret key and an initialization vector of my initialization vector when encrypting the password; specify the same key and initialization vector when decrypting the password.
7.2.3 HASH
The HASH function uses a user-specified algorithm to return the hash value of a RAW or CLOB value. The HASH function is available in three forms:
HASH
(
src IN RAW, typ IN INTEGER) RETURN RAW
HASH
(
src IN CLOB, typ IN INTEGER) RETURN RAW
src specifies the value for which the hash value will be generated. You can specify a RAW, a BLOB, or a CLOB value.
typ specifies the HASH function type. Advanced Server supports the HASH function types listed below:
The following example uses DBMS_CRYPTO.HASH to find the md5 hash value of the string, cleartext source:
7.2.4 MAC
The MAC function uses a user-specified MAC function to return the hashed MAC value of a RAW or CLOB value. The MAC function is available in three forms:
src specifies the value for which the MAC value will be generated. Specify a RAW, BLOB, or CLOB value.
typ specifies the MAC function used. Advanced Server supports the MAC functions listed below.
key specifies the key that will be used to calculate the hashed MAC value.
The following example finds the hashed MAC value of the string cleartext source:
DBMS_CRYPTO.MAC uses a key value of my secret key when calculating the MAC value of cleartext source.
The RANDOMBYTES function returns a RAW value of the specified length, containing cryptographically random bytes. The signature is:
RANDOMBYTES
(
number_bytes IN INTEGER) RETURNS RAW
number_bytes specifies the number of random bytes to be returned
The following example uses RANDOMBYTES to return a value that is 1024 bytes long:
The RANDOMINTEGER() function returns a random INTEGER between 0 and 268,435,455. The signature is:
The following example uses the RANDOMINTEGER function to return a cryptographically strong random INTEGER value:
The RANDOMNUMBER() function returns a random NUMBER between 0 and 268,435,455. The signature is:
The following example uses the RANDOMNUMBER function to return a cryptographically strong random number:

7 Built-In Packages : 7.2 DBMS_CRYPTO

Table of Contents Previous Next