Forum Stats

  • 3,759,958 Users
  • 2,251,621 Discussions
  • 7,870,883 Comments

Discussions

Function for RSA SHA-256 digital signature

sect55
sect55 Member Posts: 1,085 Bronze Badge

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:

Best Answer

  • ascheffer
    ascheffer Member Posts: 1,913 Gold Trophy
    Accepted 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;
    
    
    
«13

Answers

  • Paulzip
    Paulzip Member Posts: 8,460 Blue Diamond

    See DBMS_Crypto.Hash and use 4 as the type :

    HASH_SH256     CONSTANT PLS_INTEGER      :=   4;

    sect55
  • Paulzip
    Paulzip Member Posts: 8,460 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
  • sect55
    sect55 Member Posts: 1,085 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)?

  • ascheffer
    ascheffer Member Posts: 1,913 Gold Trophy
    edited Apr 7, 2021 1:14PM

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

    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
    sect55 Member Posts: 1,085 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

  • ascheffer
    ascheffer Member Posts: 1,913 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' ), ....

  • sect55
    sect55 Member Posts: 1,085 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

  • ascheffer
    ascheffer Member Posts: 1,913 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
  • sect55
    sect55 Member Posts: 1,085 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

  • sect55
    sect55 Member Posts: 1,085 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