OraPGP functions

DidiSoft OraPGP (ORA_PGP) PL/SQL package provides functions that work with VARCHAR2 and BLOB fields. OpenPGP keys are expected either as absolute file paths on the server (network shared folders are also accepted) or serialized in VARCHAR2 fields in ASCII armored format.

List of the procedures and functions contained in the ORA_PGP PL/SQL package:

1. ENCRYPT
2. DECRYPT
3. SIGN
4. VERIFY
5. SIGN_AND_ENCRYPT
6. VERIFY_ENCRYPTED
7. CLEAR_SIGN
8. IS_ENCRYPTED
9. IS_SIGNED
10. ENCRYPTION_KEY_ID
11. ENCRYPTION_KEYS_IDS
12. SIGNING_KEY_ID

1. ENCRYPT

FUNCTION ENCRYPT(message CLOB, public_key CLOB) RETURN CLOB

OpenPGP encrypts a CLOB field with a specified public key.

Parameters:
message – message to be encrypted
public_key – absolute file path on the server to the public key or the public key as ASCII armored text

Result
an encrypted VARCHAR2 field

FUNCTION ENCRYPT(message BLOB, public_key CLOB) RETURN BLOB

OpenPGP encrypts a BLOB field with a specified public key.

Parameters:
message – message to be encrypted
public_key – absolute file path on the server to the public key or the public key as ASCII armored text

Result
an encrypted BLOB field

Online example: PL/SQL OpenPGP encrypt.

2. DECRYPT

FUNCTION DECRYPT(message CLOB, private_key CLOB, key_password varchar2) RETURN CLOB

OpenPGP decrypts a CLOB field with a specified private key.

Parameters:
message – encrypted message to be decrypted
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key

Result
the decrypted CLOB field

FUNCTION DECRYPT(DATA BLOB, private_key CLOB, key_password varchar2) RETURN BLOB

OpenPGP decrypts a BLOB field with a specified private key.

Parameters:
data – encrypted message to be decrypted
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key

Result
the decrypted BLOB field

3. CLEAR_SIGN

FUNCTION CLEAR_SIGN(message CLOB, private_key CLOB, key_password varchar2, hash pls_integer) RETURN CLOB

OpenPGP signs a CLOB field with a specified private key.

Parameters:
message – message to be signed
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key
hash – hash algorithm for the signature

Result
the signed CLOB field

FUNCTION SIGN(DATA BLOB, private_key CLOB, key_password varchar2) RETURN BLOB

OpenPGP signs a BLOB field with a specified private key.

Parameters:
data – message to be signed
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key

Result
the signed BLOB field

3. SIGN

FUNCTION SIGN(message CLOB, private_key CLOB, key_password varchar2) RETURN CLOB

OpenPGP signs a VARCHAR2 field with a specified private key.

Parameters:
message – message to be signed
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key

Result
the signed CLOB field

FUNCTION SIGN(DATA BLOB, private_key CLOB, key_password varchar2) RETURN BLOB

OpenPGP signs a BLOB field with a specified private key.

Parameters:
data – message to be signed
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key

Result
the signed BLOB field

4. VERIFY

FUNCTION VERIFY(message CLOB, public_key CLOB, decrypted_message OUT varchar2) RETURN pls_integer

extracts and verifies the signature of an OpenPGP signed only VARCHAR2 field with a specified public key.

Parameters:
message – signed message to be verified and extracted
public_key – absolute file path on the server to the public key or the public key as ASCII armored text
OUT decrypted_message – the extracted message

Result
1 – if the signature was validated with the provided public key
0 – if the signature cannot be validated with the provided public key

FUNCTION VERIFY(message BLOB, public_key CLOB) RETURN pls_integer

extracts and verifies the signature of an OpenPGP signed only BLOB field with a specified public key.

Parameters:
message – signed message to be verified and extracted
public_key – absolute file path on the server to the public key or the public key as ASCII armored text

Result
1 – if the signature was validated with the provided public key
0 – if the signature cannot be validated with the provided public key

5. SIGN_AND_ENCRYPT

FUNCTION SIGN_AND_ENCRYPT(message CLOB, private_key CLOB, key_password varchar2, public_key CLOB) RETURN CLOB

OpenPGP signs and encrypts a CLOB field in one pass

Parameters:
message – message to be signed and encrypted
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key
public_key – absolute file path on the server to the public key or the key as ASCII armored text

Result
the signed and encrypted CLOB field

FUNCTION SIGN_AND_ENCRYPT(message BLOB, private_key CLOB, key_password varchar2, public_key CLOB) RETURN BLOB

OpenPGP signs and encrypts a BLOB field in one pass

Parameters:
message – message to be signed and encrypted
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key
public_key – absolute file path on the server to the public key or the key as ASCII armored text

Result
the signed and encrypted BLOB field

6. VERIFY_ENCRYPTED

FUNCTION VERIFY_ENCRYPTED(message CLOB, private_key CLOB, privatekey_password varchar2, public_key CLOB, decrypted_message OUT CLOB) RETURN pls_integer

extracts and verifies the signature of an OpenPGP signed and encrypted VARCHAR2 field

Parameters:
message – signed and encrypted message to be verified and extracted
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key
public_key – absolute file path on the server to the public key or the key as ASCII armored text
OUT decrypted_message – the extracted message

Result
1 – if the signature was validated with the provided public key
0 – if the signature cannot be validated with the provided public key

FUNCTION VERIFY_ENCRYPTED(message BLOB, private_key CLOB, privatekey_password varchar2, public_key CLOB) RETURN pls_integer

verifies the signature of an OpenPGP signed and encrypted BLOB field

Parameters:
message – signed and encrypted message to be verified and extracted
private_key – absolute file path on the server to the private key or the key as ASCII armored text
key_password – password that unlocks the private key
public_key – absolute file path on the server to the public key or the key as ASCII armored text

Result
1 – if the signature was validated with the provided public key
0 – if the signature cannot be validated with the provided public key

ENCRYPTION_KEY_ID

FUNCTION ENCRYPTION_KEY_ID(message CLOB) RETURN varchar2
FUNCTION ENCRYPTION_KEY_ID(message BLOB) RETURN varchar2

gets the hexadecimal encryption Key ID for the OpenPGP encrypted data, or the encryption Key ID of an OpenPGP key (public or private)

Parameters:
message – encrypted data or signed and encrypted data or public key or private key

Result
the first hexadecimal encryption Key ID

Other possible results are:
‘ANYKEY’ – if the data is encrypted with a wildcard (hidden) key, in this case all possible keys must be tried for decryption
‘SYMKEY’ – if the data is symmetrically encrypted with a password
‘NOKEY’ – if the data is signed only

ENCRYPTION_KEYS_IDS

FUNCTION ENCRYPTION_KEYS_IDS(message CLOB) RETURN ENCRYPTION_KEYS_IDS_LIST
FUNCTION ENCRYPTION_KEYS_IDS(message BLOB) RETURN ENCRYPTION_KEYS_IDS_LIST

gets a TABLE of hexadecimal encryption Keys ID’s for the OpenPGP encrypted data.

Parameters:
message – encrypted data or signed and encrypted data

Result
ENCRYPTION_KEYS_IDS_LIST is table of varchar2(255), containing the hexadecimal encryption Keys ID’s

Other possible values in the table are:
‘ANYKEY’ – if the data is encrypted with a wildcard (hidden) key, in this case all possible keys must be tried for decryption
‘SYMKEY’ – if the data is symmetrically encrypted with a password
‘NOKEY’ – if the data is signed only

SIGNING_KEY_ID

FUNCTION SIGNING_KEY_ID(message CLOB) RETURN varchar2
FUNCTION SIGNING_KEY_ID(message BLOB) RETURN BLOB

gets the hexadecimal signing Key ID of an OpenPGP signed data or the signing Key ID of an OpenPGP key (public or private)

Parameters:
message – signed data or public key or private key

Result
the first signing Key ID (hexadecimal)

Other possible results are:
‘PUBKEY’ – if the data is signed with a key
‘SYMKEY’ – if the data is symmetrically encrypted with a password
‘NOKEY’ – unknown data

IS_ENCRYPTED

FUNCTION IS_ENCRYPTED(message CLOB) RETURN pls_integer
FUNCTION IS_ENCRYPTED(message BLOB) RETURN pls_integer

checks is given field OpenPGP encrypted.

Parameters:
message – field to be checked

Result
1 – if the data is encrypted with a public key
0 – if the data is NOT encrypted with a public key

IS_SIGNED

FUNCTION IS_SIGNED(message CLOB) RETURN pls_integer
FUNCTION IS_SIGNED(message BLOB) RETURN pls_integer

checks is given field OpenPGP signed only

Parameters:
message – field to be checked

Result
1 – if the data is signed only
0 – if the data is NOT signed only (probably encrypted)

Summary

This chapter listed the PL/SQL functions contained in the DidiSoft ORA_PGP package.