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. Inspecting OpenPGP data

1. ENCRYPT

FUNCTION ENCRYPT(message varchar2, public_key varchar2) RETURN varchar2

OpenPGP encrypts a VARCHAR2 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 varchar2) 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 varchar2, private_key varchar2, key_password varchar2) RETURN varchar2

OpenPGP decrypts a VARCHAR2 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 VARCHAR2 field

FUNCTION DECRYPT(DATA BLOB, private_key varchar2, 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. SIGN

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

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 VARCHAR2 field

FUNCTION SIGN(DATA BLOB, private_key varchar2, 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 varchar2, public_key varchar2, 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 varchar2) 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 varchar2, private_key varchar2, key_password varchar2, public_key varchar2) RETURN varchar2

OpenPGP signs and encrypts a VARCHAR2 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 VARCHAR2 field

FUNCTION SIGN_AND_ENCRYPT(message BLOB, private_key varchar2, key_password varchar2, public_key varchar2) 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 varchar2, private_key varchar2, privatekey_password varchar2, public_key varchar2, decrypted_message OUT varchar2) 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 varchar2, privatekey_password varchar2, public_key varchar2) 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

7. Inspecting OpenPGP data

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

gets the hexadecimal encryption Key ID of an 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

FUNCTION SIGNING_KEY_ID(message varchar2) 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

FUNCTION IS_ENCRYPTED(message varchar2) 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

FUNCTION IS_SIGNED(message varchar2) 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.