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