In order to decrypt CMS (Cryptography Message Syntax) encrypted data, we need the private key corresponding to the X.509 certificate used for the encryption operation.
In this chapter, we will decrypt CMS encrypted data using Oracle PL/SQL.
Table of contents
Decrypt CMS data from VARCHAR2 column
In the example code block below the PKCS#12 (.pfx) file containing the private key is loaded from the file system into a BLOB field. We can easily modify this code to store and load the private key from a BLOB column from withing our database:
DECLARE private_key_file_handle BFILE; private_key BLOB; private_key_password VARCHAR2(2000); encrypted_data VARCHAR2(2000); decrypted_data VARCHAR2(2000); BEGIN -- RSA Encrypt encrypted_data := ... -- load the CMS encrypted data -- load a private key from the file system private_key_file_handle := BFILENAME('KEYS_DIR', 'PKCS12_Credential_sales@didisoft.com.pfx'); -- Note: directory name must be Upper case -- initialize the private key BLOB storage DBMS_LOB.createtemporary(private_key, TRUE); -- load the private key into a BLOB DBMS_LOB.OPEN(private_key_file_handle, DBMS_LOB.LOB_READONLY); DBMS_LOB.LoadFromFile( DEST_LOB => private_key, SRC_LOB => private_key_file_handle, AMOUNT => DBMS_LOB.GETLENGTH(private_key_file_handle) ); DBMS_LOB.CLOSE(private_key_file_handle); -- RSA Decrypt private_key_password := '4Yvv8RH33D81'; decrypted_data := ORA_RSA.CMS_DECRYPT_VARCHAR2(encrypted_data, private_key, private_key_password); -- print the decrypted data DBMS_OUTPUT.put_line('decrypted_data='||decrypted_data); END; |
Decrypting from CLOB
ORA_RSA.CMS_DECRYPT_CLOB is used to decrypt previously encrypted CMS data, which is assumed to be a textual content.
DECLARE private_key_file_handle BFILE; private_key BLOB; mydata CLOB; encrypted_data CLOB; decrypted_data CLOB; BEGIN -- load encrypted data encrypted_data := ... -- load a private key from the file system private_key_file_handle := BFILENAME('KEYS_DIR', 'PKCS12_Credential_sales@didisoft.com.pfx'); -- initialize the private key BLOB storage DBMS_LOB.createtemporary(private_key, TRUE); -- load the private key into a BLOB DBMS_LOB.OPEN(private_key_file_handle, DBMS_LOB.LOB_READONLY); DBMS_LOB.LoadFromFile( DEST_LOB => private_key, SRC_LOB => private_key_file_handle, AMOUNT => DBMS_LOB.GETLENGTH(private_key_file_handle) ); DBMS_LOB.CLOSE(private_key_file_handle); -- RSA Decrypt decrypted_data := ORA_RSA.CMS_DECRYPT_CLOB(encrypted_data, private_key, '4Yvv8RH33D81'); DBMS_OUTPUT.PUT_LINE(decrypted_data); END; |
Decrypting from BLOB
The method ORA_RSA.CMS_DECRYPT_BLOB is more suitable for decrypting CMS encrypted data in DER (binary) format. Afterwards we may need to manually convert the decrypted result to textual format if must be displayed or processed as such.
DECLARE private_key_file_handle BFILE; private_key BLOB; mydata BLOB; encrypted_data BLOB; decrypted_data BLOB; BEGIN -- load encrypted data encrypted_data := ... -- load a private key from the filesystem private_key_file_handle := BFILENAME('KEYS_DIR', 'PKCS12_Credential_sales@didisoft.com.pfx'); -- initialize the private key BLOB storage DBMS_LOB.createtemporary(private_key, TRUE); -- load the private key into a BLOB DBMS_LOB.OPEN(private_key_file_handle, DBMS_LOB.LOB_READONLY); DBMS_LOB.LoadFromFile( DEST_LOB => private_key, SRC_LOB => private_key_file_handle, AMOUNT => DBMS_LOB.GETLENGTH(private_key_file_handle) ); DBMS_LOB.CLOSE(private_key_file_handle); -- CMS Decrypt decrypted_data := ORA_RSA.CMS_DECRYPT_BLOB(encrypted_data, private_key, '4Yvv8RH33D81'); END; |
Summary
This chapter discussed how to decrypt CMS encrypted data inside the Oracle® database using the PL/SQL programming language.
CMS encrypted data in PEM format is preferable to be decrypted with ORA_RSA.CMS_DECRYPT_CLOB or ORA_RSA.CMS_DECRYPT_VARCHAR2.
CMS encrypted data in DER format is preferable to be decrypted with ORA_RSA.CMS_DECRYPT_BLOB or ORA_RSA.CMS_DECRYPT_RAW.