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