Forum Stats

  • 3,855,245 Users
  • 2,264,487 Discussions
  • 7,905,946 Comments

Discussions

Categories

Function for RSA SHA-256 digital signature

sect55
sect55 Member Posts: 1,106 Bronze Badge
in SQL & PL/SQL

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

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    Answer ✓ 
    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;
    · Share on TwitterShare on Facebook
«134

Answers

  • Paulzip
    Paulzip Member Posts: 8,759 Blue Diamond

    See DBMS_Crypto.Hash and use 4 as the type :

    HASH_SH256     CONSTANT PLS_INTEGER      :=   4;

    sect55
    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,759 Blue Diamond
    edited Apr 6, 2021 5:37PM

    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
    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,106 Bronze Badge

    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)?

    · Share on TwitterShare on Facebook
  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Apr 7, 2021 1:14PM

    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.

    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,106 Bronze Badge

    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

    · Share on TwitterShare on Facebook
  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy

    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' ), ....

    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,106 Bronze Badge

    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

    · Share on TwitterShare on Facebook
  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Apr 9, 2021 6:59AM

    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
    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,106 Bronze Badge

    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

    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,106 Bronze Badge

    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

    · Share on TwitterShare on Facebook