Secret Agent APEX

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.

#JoelKallman Day

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:

• PKENCRYPT
• PKDECRYPT
• SIGN
• VERIFY

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:

1. Load a private key.
2. Decrypt a message that was signed by the receiver's public key.
3. 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-----
MIIEogIBAAKCAQEAy/wiWejt+fKwrT7NHTTEeKuWrWqC4KwzpjWUSCK0zWh832xL
jnrZuJc6Sl8gmMz6/p8p6T2NrJEBIda1EbjAD/W4G7IUykf5LrlICEJ3miJV33WL
wpFaFI6wmIas2PpodYhMqA0HngEMTP4oQS4AHnVuxn2naijzeRa0HbG55pAr2K1J
...
/bneFDA7jyv3bbOujCkk3DpaBjHXunqz5JmAJ6JqqMhzCQwRQ7c=
-----END RSA PRIVATE KEY-----

And the public key:

-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAy/wiWejt+fKwrT7NHTTE
eKuWrWqC4KwzpjWUSCK0zWh832xLjnrZuJc6Sl8gmMz6/p8p6T2NrJEBIda1EbjA
D/W4G7IUykf5LrlICEJ3miJV33WLwpFaFI6wmIas2PpodYhMqA0HngEMTP4oQS4A
HnVuxn2naijzeRa0HbG55pAr2K1J+R/o5hOp460iEbP0HWM16QBwI2TTPfq0R+fz
KE2EA72g/gbttWSUdRZE/ZdDzG1ZE06RAfu7bNP7GQqGcyMTJ+bv3zWkYlMeDSQD
5foCzTHrcDomRL9H/snPSfMpnOzn8xwndZcwBE1ydy852NJatIRbPitglgNMKzfi
8QIDAQAB
-----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)
return varchar2
as
begin
  return regexp_replace(
    p_rsa_key
    ,'(-+((BEGIN|END) (RSA )?(PUBLIC|PRIVATE) KEY)-+\s?|\s)'
    , '');
end;
/

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 DBMS_CRYPTO.PKENCRYPT:

declare
  l_input_string varchar2(100) := 'Bob has no keys';

  l_public_key raw(4000);
  l_encrypted_output raw(4000);
begin
  l_public_key :=
    utl_i18n.string_to_raw(strip_rsa_key(q'[
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAy/wiWejt+fKwrT7NHTTE
eKuWrWqC4KwzpjWUSCK0zWh832xLjnrZuJc6Sl8gmMz6/p8p6T2NrJEBIda1EbjA
D/W4G7IUykf5LrlICEJ3miJV33WLwpFaFI6wmIas2PpodYhMqA0HngEMTP4oQS4A
HnVuxn2naijzeRa0HbG55pAr2K1J+R/o5hOp460iEbP0HWM16QBwI2TTPfq0R+fz
KE2EA72g/gbttWSUdRZE/ZdDzG1ZE06RAfu7bNP7GQqGcyMTJ+bv3zWkYlMeDSQD
5foCzTHrcDomRL9H/snPSfMpnOzn8xwndZcwBE1ydy852NJatIRbPitglgNMKzfi
8QIDAQAB
-----END PUBLIC KEY-----
]'));

  l_encrypted_output :=
    dbms_crypto.pkencrypt(
      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
    );

  dbms_output.put_line(utl_i18n.raw_to_char(utl_encode.base64_encode(l_encrypted_output)));
end;
/

Here's an example of the base64-encoded, encrypted output:

Wb3KKyyu+8/MxRKn/kIoqAt4ZMsSvE7AZ/Gr+/3x+xHedtxvj504sy7pffIT1BTl
AbvVdjpcZ+VsxNpYCeN3Q/47R51YKPu/2P5u8FiY+nOLPu+sN5hQc4a6WdHVvRL/
wvXOhSx0fV6M4OtAO7KMuNrrRCqBlOCXPkIIHMeReAq1YVyVuRrofuDptznuEiOo
MKIt6s0j95n9RGCNWC3dI3Rsr+Nm64psjtWPVxCFjMUCSqh4fZcFXXW8xdCsHxYw
0zw43LrD1REnUbUCXAeNOhVXIV5f5TxRYwW9ibkjPTWeDBtViIy33XTvCleEmf7K
8kMkShUD3gfQt2HJ3FSmNQ==

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 | \
    openssl base64

Along with his Charlie, Alice can now verify that he was indeed the sender of the message "Bob has no keys".

Summary

I am very excited! There are more practical uses for this API, and you can definitely expect a follow up post. Stay tuned!

Photo Credits

This post's banner photo is by Kristina Flour on Unsplash