Function for RSA SHA-256 digital signature

sect55

I am attempting to write an Oracle function to authenticate against DocuSign using JWT. It requires RSA SHA-256 digital signature as outlined below:

The signature part of the JWT is a digital signature that enables DocuSign to verify that the JWT was created by your application and has not been modified since it was created. The first two parts of the JWT are signed with your application's private key (using the RSA SHA-256 digital signature algorithm) as shown in the diagram.

When Oracle function should I be using?

I'm using Oracle 19c (EE).

Robert

ascheffer

declare
  l_header varchar2(1000);
  l_payload varchar2(1000);
  l_sign varchar2(1000);
  l_token varchar2(1000);
  function base64URL_encode( p_raw in raw )
  return varchar2
  is
    l_rv varchar2(32767);
  begin
    l_rv := utl_raw.cast_to_varchar2( utl_encode.base64_encode( p_raw ) );
    l_rv := replace( l_rv, '+', '-' );
    l_rv := replace( l_rv, '/', '_' );
    l_rv := replace( l_rv, chr(10), '' );
    l_rv := replace( l_rv, chr(13), '' );
    return rtrim( l_rv, '=' );
  end;
  function base64URL_encode( p_txt in varchar2 )
  return varchar2
  is
  begin
    return base64URL_encode( p_raw => utl_raw.cast_to_raw( p_txt ) );
  end;
begin
  l_header := base64URL_encode( p_txt => '{"alg":"RS256","typ":"JWT"}' );
  l_payload := base64URL_encode( p_txt => '{"sub":"1234567890","name":"John Doe","admin":true,"iat":1516239022}' );
  l_sign := base64URL_encode( p_raw => as_crypto.sign( utl_raw.cast_to_raw( l_header || '.' || l_payload )
                                                     , utl_raw.cast_to_raw( 'MIIEogIBAAKCAQEAnzyis1ZjfNB0bBgKFMSvvkTtwlvBsaJq7S5wA+kzeVOVpVWw
kWdVha4s38XM/pa/yr47av7+z3VTmvDRyAHcaT92whREFpLv9cj5lTeJSibyr/Mr
m/YtjCZVWgaOYIhwrXwKLqPr/11inWsAkfIytvHWTxZYEcXLgAXFuUuaS3uF9gEi
NQwzGTU1v0FqkqTBr4B8nW3HCN47XUu0t8Y0e+lf4s4OxQawWD79J9/5d3Ry0vbV
3Am1FtGJiJvOwRsIfVChDpYStTcHTCMqtvWbV6L11BWkpzGXSW4Hv43qa+GSYOD2
QU68Mb59oSk2OB+BtOLpJofmbGEGgvmwyCI9MwIDAQABAoIBACiARq2wkltjtcjs
kFvZ7w1JAORHbEufEO1Eu27zOIlqbgyAcAl7q+/1bip4Z/x1IVES84/yTaM8p0go
amMhvgry/mS8vNi1BN2SAZEnb/7xSxbflb70bX9RHLJqKnp5GZe2jexw+wyXlwaM
+bclUCrh9e1ltH7IvUrRrQnFJfh+is1fRon9Co9Li0GwoN0x0byrrngU8Ak3Y6D9
D8GjQA4Elm94ST3izJv8iCOLSDBmzsPsXfcCUZfmTfZ5DbUDMbMxRnSo3nQeoKGC
0Lj9FkWcfmLcpGlSXTO+Ww1L7EGq+PT3NtRae1FZPwjddQ1/4V905kyQFLamAA5Y
lSpE2wkCgYEAy1OPLQcZt4NQnQzPz2SBJqQN2P5u3vXl+zNVKP8w4eBv0vWuJJF+
hkGNnSxXQrTkvDOIUddSKOzHHgSg4nY6K02ecyT0PPm/UZvtRpWrnBjcEVtHEJNp
bU9pLD5iZ0J9sbzPU/LxPmuAP2Bs8JmTn6aFRspFrP7W0s1Nmk2jsm0CgYEAyH0X
+jpoqxj4efZfkUrg5GbSEhf+dZglf0tTOA5bVg8IYwtmNk/pniLG/zI7c+GlTc9B
BwfMr59EzBq/eFMI7+LgXaVUsM/sS4Ry+yeK6SJx/otIMWtDfqxsLD8CPMCRvecC
2Pip4uSgrl0MOebl9XKp57GoaUWRWRHqwV4Y6h8CgYAZhI4mh4qZtnhKjY4TKDjx
QYufXSdLAi9v3FxmvchDwOgn4L+PRVdMwDNms2bsL0m5uPn104EzM6w1vzz1zwKz
5pTpPI0OjgWN13Tq8+PKvm/4Ga2MjgOgPWQkslulO/oMcXbPwWC3hcRdr9tcQtn9
Imf9n2spL/6EDFId+Hp/7QKBgAqlWdiXsWckdE1Fn91/NGHsc8syKvjjk1onDcw0
NvVi5vcba9oGdElJX3e9mxqUKMrw7msJJv1MX8LWyMQC5L6YNYHDfbPF1q5L4i8j
8mRex97UVokJQRRA452V2vCO6S5ETgpnad36de3MUxHgCOX3qL382Qx9/THVmbma
3YfRAoGAUxL/Eu5yvMK8SAt/dJK6FedngcM3JEFNplmtLYVLWhkIlNRGDwkg3I5K
y18Ae9n7dHVueyslrb6weq7dTkYDi3iOYRW8HRkIQh06wEdbxt0shTzAJvvCQfrB
jg/3747WSsf/zBTcHihTRBdAv6OmdhV4/dD5YBfLAkLrd+mX7iE=' ), as_crypto.KEY_TYPE_RSA, as_crypto.SIGN_SHA256_RSA ) );
  l_token := l_header || '.' || l_payload || '.' || l_sign;
  dbms_output.put_line( l_token );
end;

Paulzip

See DBMS_Crypto.Hash and use 4 as the type :

HASH_SH256     CONSTANT PLS_INTEGER      :=   4;

Paulzip

Here's a wrapper function that should return the SHA 256 hash (in raw form) of a file in an Oracle directory. If you want it in hex string form, use RawToHex on that result.

create or replace function SHA256Hash(pOracleDir in varchar2, pFilename in varchar2) return raw is
-- Returns the SHA256 hash of a file
-- Use RawToHex on result to return as hex string 
  vBFile bfile;
  vBlob  blob;
  vHash  raw(32);
  vLen   number;
begin
  vBFile := BFilename(pOracleDir, pFilename);
  DBMS_LOB.Open(vBFile);
  vLen := DBMS_LOB.GetLength(vBFile);
  DBMS_LOB.CreateTemporary(vBlob, True, DBMS_LOB.Call);
  DBMS_LOB.LoadFromFile(vBlob, vBFile, vLen, 1, 1);
  vHash := DBMS_CRYPTO.Hash(vBlob, DBMS_CRYPTO.HASH_SH256);
  DBMS_LOB.FreeTemporary(vBlob);
  vBlob := null;
  DBMS_LOB.Close(vBFile);  
  return vHash;
exception
  when OTHERS then
    if vBlob is not null then
      DBMS_LOB.FreeTemporary(vBlob);
    end if;
    if DBMS_LOB.FileIsOpen(vBFile) = 1 then 
      DBMS_LOB.Close(vBFile);
    end if;
    raise;
end;

sect55

Paulzip,

Thank you for your replies. They gave me more information, but I'm trying to fill a hole:

I need to create SHA256 RSA signature using oracle 19c

In JWT.io, it uses:

RSASHA256(

 base64UrlEncode(header) + "." +

 base64UrlEncode(payload),

  

your-256-bit-secret

)

It uses two parameters : key and data. I have both of them. But, my question is how do I do this in Oracle PL/SQL with DBMS_CRYTO.HASH (src, 4)?

ascheffer

The RSA part is only available in Oracle 21 dbms_crypto. Or in

https://github.com/antonscheffer/as_crypto

I don't know if JWT needs the sign/verify functions, which are available in Oracle 21 dbms_crypto but not yet in as_crypto. Shouldn't be that much trouble adding them to as_crypto if needed.

OK, I've just added those functions to as_crypto.

sect55

ascheffer,

We are using 19c so I guess I will try to use your package.

What function and what parameters should I use. The private key and pubic key is in the your-256-bit-secret parameter :

RSASHA256(

 base64UrlEncode(header) + "." +

 base64UrlEncode(body),

 "-----BEGIN PUBLIC KEY-----

MIIBIj...IDAQAB

-----END PUBLIC KEY-----",

  "-----BEGIN RSA PRIVATE KEY-----

MIIEpQ...9ZwKY=

-----END RSA PRIVATE KEY-----"  

)

Robert

ascheffer

As said, I don't know JWT but I expect it needs the sign function. My package behaves just like dbms_crypto, so you can use the Oracle documentation. Something not mentioned in the documentations is the expected format for the public and private keys: you have to strip the leading and trailing comments like "-----BEGIN PUBLIC KEY-----" and "-----END RSA PRIVATE KEY-----". So something like:

..., utl_raw.cast_to_raw( 'MIGoAgEAAiEAm2GMUv882pq6zgK2hFwJVB4QVJZahLheY2XXf+ZDuesCASUCICn+

sE3IcU/WyrQ4Fac7eCQmsWMZpXRMFftVkGp2CFJNAhEAyRvzKX+PGwHkF5r0jDWx

...

KpCmT8AsbR3lAhBpVddrdj7KGbHfbyWUyqWs' ), ....

sect55

ascheffer,

While I understand you are not familiar with JWT, I would like to create a digital signature using the package you provided.

The parameters are:

  base64UrlEncode(header) + "." +
  base64UrlEncode(payload),

• and RSA SHA 256 (Public Key or Certificate [in plain text] and Private Key [in plain text ] )

RSASHA256(

base64UrlEncode(header) + "." + base64UrlEncode(payload),

Public Key or Certificate

Private Key

)

For more information, see JWT.io

Thank you,

Robert

ascheffer

I leave the base64Url encoding to you, but this produces the correct signing for the message:

dbms_output.put_line( as_crypto.sign( utl_raw.cast_to_raw( 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9' || '.' || 'eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWUsImlhdCI6MTUxNjIzOTAyMn0' )
, utl_raw.cast_to_raw( 'MIIEogIBAAKCAQEAnzyis1ZjfNB0bBgKFMSvvkTtwlvBsaJq7S5wA+kzeVOVpVWw
kWdVha4s38XM/pa/yr47av7+z3VTmvDRyAHcaT92whREFpLv9cj5lTeJSibyr/Mr
m/YtjCZVWgaOYIhwrXwKLqPr/11inWsAkfIytvHWTxZYEcXLgAXFuUuaS3uF9gEi
NQwzGTU1v0FqkqTBr4B8nW3HCN47XUu0t8Y0e+lf4s4OxQawWD79J9/5d3Ry0vbV
3Am1FtGJiJvOwRsIfVChDpYStTcHTCMqtvWbV6L11BWkpzGXSW4Hv43qa+GSYOD2
QU68Mb59oSk2OB+BtOLpJofmbGEGgvmwyCI9MwIDAQABAoIBACiARq2wkltjtcjs
kFvZ7w1JAORHbEufEO1Eu27zOIlqbgyAcAl7q+/1bip4Z/x1IVES84/yTaM8p0go
amMhvgry/mS8vNi1BN2SAZEnb/7xSxbflb70bX9RHLJqKnp5GZe2jexw+wyXlwaM
+bclUCrh9e1ltH7IvUrRrQnFJfh+is1fRon9Co9Li0GwoN0x0byrrngU8Ak3Y6D9
D8GjQA4Elm94ST3izJv8iCOLSDBmzsPsXfcCUZfmTfZ5DbUDMbMxRnSo3nQeoKGC
0Lj9FkWcfmLcpGlSXTO+Ww1L7EGq+PT3NtRae1FZPwjddQ1/4V905kyQFLamAA5Y
lSpE2wkCgYEAy1OPLQcZt4NQnQzPz2SBJqQN2P5u3vXl+zNVKP8w4eBv0vWuJJF+
hkGNnSxXQrTkvDOIUddSKOzHHgSg4nY6K02ecyT0PPm/UZvtRpWrnBjcEVtHEJNp
bU9pLD5iZ0J9sbzPU/LxPmuAP2Bs8JmTn6aFRspFrP7W0s1Nmk2jsm0CgYEAyH0X
+jpoqxj4efZfkUrg5GbSEhf+dZglf0tTOA5bVg8IYwtmNk/pniLG/zI7c+GlTc9B
BwfMr59EzBq/eFMI7+LgXaVUsM/sS4Ry+yeK6SJx/otIMWtDfqxsLD8CPMCRvecC
2Pip4uSgrl0MOebl9XKp57GoaUWRWRHqwV4Y6h8CgYAZhI4mh4qZtnhKjY4TKDjx
QYufXSdLAi9v3FxmvchDwOgn4L+PRVdMwDNms2bsL0m5uPn104EzM6w1vzz1zwKz
5pTpPI0OjgWN13Tq8+PKvm/4Ga2MjgOgPWQkslulO/oMcXbPwWC3hcRdr9tcQtn9
Imf9n2spL/6EDFId+Hp/7QKBgAqlWdiXsWckdE1Fn91/NGHsc8syKvjjk1onDcw0
NvVi5vcba9oGdElJX3e9mxqUKMrw7msJJv1MX8LWyMQC5L6YNYHDfbPF1q5L4i8j
8mRex97UVokJQRRA452V2vCO6S5ETgpnad36de3MUxHgCOX3qL382Qx9/THVmbma
3YfRAoGAUxL/Eu5yvMK8SAt/dJK6FedngcM3JEFNplmtLYVLWhkIlNRGDwkg3I5K
y18Ae9n7dHVueyslrb6weq7dTkYDi3iOYRW8HRkIQh06wEdbxt0shTzAJvvCQfrB
jg/3747WSsf/zBTcHihTRBdAv6OmdhV4/dD5YBfLAkLrd+mX7iE=' ), as_crypto.KEY_TYPE_RSA, as_crypto.SIGN_SHA256_RSA ) );

Please note that base64 is not the same as base64URL

And I predict that in the next Oracle version 22, as soon as they add KEY_TYPE_ED25519 or KEY_TYPE_NISTP256, the constant SIGN_SHA256_RSA is obsoleted and replaced by just SIGN_SHA256

sect55

ascheffer,

Thank you for a quick response. I can now go further in the testing. I will let you know if it works and then state that you answered my question.

Thanks again! I appreciate it.

Robert

sect55

ascheffer,

Thank you for a quick response. I can now go further in the testing. I will let you know if it works and then state that you answered my question.

Thanks again! I appreciate it.

Robert