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.
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
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
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
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;