For a long time now, I have been eagerly awaiting for out-of-the-box RSA encryption support with pure PL/SQL. Not so much for to sharing secrets, but rather, the ability to sign messages and verify signatures. I might have missed the announcements, but it looks like my wait is over support with asymmetric key operations added in 21c, and (perhaps backported) in 19c as-of release 19.9.
Before we continue though, along with many other members of the Oracle community, I'd like to ask that you take a one-minute pause to remember Joel Kallman on this day. Joel was a wonderful man who was not only passionate about his work, but was even more devoted to uplifting others. He was taken away from us too soon, but the best way to move forward, is to continue his work and mould ourselves with his legacy.
Secret Story Continued...
Before this release, supporting RSA encryption typically involved installing a Java package and writing some custom PL/SQL code. With Oracle Database 19.9 and 21.x, The updated
DBMS_CRYPTO package now includes four new functions:
By default, new schemas do not have the appropriate privileges to execute the
DBMS_CRYPTO package, so please kindly request it from your DBA. The grant statement that you will need is:
grant execute on dbms_crypto to jenglish;
To demonstrate how to use these new PL/SQL APIs, I have created a simple Oracle Application Express (APEX) application that users will:
- Load a private key.
- Decrypt a message that was signed by the receiver's public key.
- Load the sender's public key and a base64-encoded signature signed using the sender's private key. The sender's public key will be used to verify the signature.
Generate the Keys
For secure and non-repudiable communications, participants in the conversation would each generate a private-public key pair, and only exchange their public keys. To create the key pair, the common tool used is OpenSSL. These days, you can find the application already installed in Linux, Mac, and Windows. For me, Alice loves working with Bash and so here's the command that she can use to generate her private key:
openssl genrsa -out alice_key.pem 2048
And then followed by a command to get her public key to be shared with her peers:
openssl rsa -in alice_key.pem -outform PEM -pubout -out alice_key_public.pem
The private key should look somewhat like this:
-----BEGIN RSA PRIVATE KEY-----
-----END RSA PRIVATE KEY-----
And the public key:
-----BEGIN PUBLIC KEY-----
-----END PUBLIC KEY-----
Do the same for Charlie.
Stripping the Keys
In the early days of Oracle Cloud Infrastructure REST API support in APEX, you might recall from this article, that we had to remove the enclosing tags and linefeeds. While this is no longer required when working with APEX Web Credentials, it's still necessary when working with the
DBMS_CRYPTO API. Since we are going to need this a lot, I decided to follow the DRY principle and coded a function to strip these unwanted characters from any RSA private or public keys entered.
create or replace function strip_rsa_key(p_rsa_key in varchar2)
,'(-+((BEGIN|END) (RSA )?(PUBLIC|PRIVATE) KEY)-+\s?|\s)'
Secret Agent APEX
I create a simple APEX application with two pages. Page 1 includes the workflow for steps 2 and 3. To avoid displaying the private key once set, a dedicated page 2 was created to provide that information.
Load a Private Key
Alice loves Bash, but she happens to own a Windows machine, so she often relies on Windows Subsystem for Linux v2 (WSL2). In Windows, there's also a helpful utility to copy text to the clipboard. The following command copies Alice's private key to her clipboard:
clip.exe < alice_key.pem
She then pastes the clipboard contents into the textarea on page 2.
This should activate the Decrypt button back on page 1.
Decrypt the Secret Message
The secret message that Charlie is sending to Alice will be encrypted using her public key. At the moment, the API appears to only support RSA Public Key Cryptosystem with PKCS1 and OAEP padding. So, if I am not mistaken, Alice should be able to generate the encrypted message using the following OpenSSL command:
echo -n "Bob has no keys" | openssl rsautl -pubin -inkey alice_key_public.pem \
-encrypt -oaep | openssl base64
Unfortunately, she couldn't get it to work at the time of writing. If you had any pointers, please feel free to comment below. For now, to continue, Alice generated the encrypted message using the function
l_input_string varchar2(100) := 'Bob has no keys';
-----BEGIN PUBLIC KEY-----
-----END PUBLIC KEY-----
src => utl_i18n.string_to_raw(l_input_string)
, pub_key => l_public_key
, pubkey_alg => dbms_crypto.key_type_rsa
, enc_alg => dbms_crypto.pkencrypt_rsa_pkcs1_oaep
Here's an example of the base64-encoded, encrypted output:
Running this through the secret agent's cryptopad with Alice's private key loaded, it appears that she has successfully mission #2!
Verify the Sender
But how can Alice know that Charlie was truly the person who had sent this message. For that, we will rely on Charlie signing his message (in this case, the unencrypted text), and send the digital digest along with the message payload. Again, you can do this with either OpenSSL,
DBMS_CRYPTO.SIGN, or any other programming language that supports public key encryption. Here's an example OpenSSL command that Charlie can use to generate the digital signature:
echo -n "Bob has no keys" | openssl dgst -sha256 -sign charlie_key.pem | \
Along with his Charlie, Alice can now verify that he was indeed the sender of the message "Bob has no keys".
I am very excited! There are more practical uses for this API, and you can definitely expect a follow up post. Stay tuned!