Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Function for RSA SHA-256 digital signature

sect55Apr 6 2021

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

This post has been answered by Anton Scheffer on Apr 14 2021
Jump to Answer

Comments

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

Anton Scheffer

The RSA part is only available in Oracle 21 dbms_crypto. Or in
antonscheffer/as_crypto (0 Bytes)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

Anton Scheffer

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

Anton Scheffer

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

sect55

I ran the sign function but the result is in hex. The result for JWT token is not in HEX format. It should look something like
JWT verify_signature_example.pngI am following this webpage:
How to get an access token with JWT Grant (0 Bytes)
What should I do??

Robert

Paulzip

That's Base64 encoding.
If it's shorter than 32k you use utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(<INPUT>)));
If it's longer, you'll have to process the chunks. I wrote as package to simplify some of the hassles of Base64 here.

Anton Scheffer
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;


Marked as Answer by sect55 · Apr 23 2021
sect55

Thank you ascheffer.
I was able to generate the sign. But I'm still cannot get the JWT from DocuSign. I get the following result for my response to the API:
{"error":"invalid_grant","error_description":"unsupported_grant_type"}
DocuSign states that the grant_type was blank but it wasn't.
So I'm asking are there any Oracle PL/SQL developers who has had experience with DocuSign embedded signature with JWT authentication. My management is ready to pull the plug on this project since it seems that APEX/ PL/SQL does not work well with DocuSign.
Please help me!!!
Robert

Anton Scheffer

I suspect that you will get more help if you show the code you have used.

sect55

obtain_jwt.txt (9.19 KB)Ascheffer,
Here is the code. Hope you can find the resolution. Thanks in advance.

Robert

Anton Scheffer

Several things:
No need to select sysdate from dual, l_iat := round(( SYSDATE - DATE '1970-01-01' ) * 86400 ,0) will do
I would check the Oracle documentation for ltrim/rtrim if I was you
you only need a private key for the signing function
you token is not l_sign, but l_header || '.' || l_payload || '.' || l_sign;
Just a quick check, no guarantee that this is complete.

sect55

Ascheffer,

I may the changes you suggestion. Please see the attached. I still get the same error. Can you see anything else?
obtain_jwt_20200426_1341.txt (10.19 KB)
Robert

sect55

Ascheffer,
Some more information:
body :
grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion=eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJpc3MiOiI4ZWNkNTRkYi0yODUyLTRhZWEtODhhOS03ZmM2ZDhkOTg5OGEiLCJzdWIiOjZlYzc1ZTg0LTYwMWYtNGNhNC05NzE3LTFkZDFlNDBkZDc2ZSwiaWF0IjoxNjE5NDM2MjY3LCJleHAiOjE2MTk0Mzk4NjcsImF1ZCI6ImFjY291bnQtZC5kb2N1c2lnbi5jb20iLCJzY29wZSI6InNpZ25hdHVyZSBpbXBlcnNvbmF0aW9uIn0.FzoxYh3pN2A-Tqx5Tn6J1ps6wWMewtd_3rmTpRm2gmh5poOTUzO8hNpP4dAbeJbUZuwVkCBC3Z6lxyBVCumYCJ8pHMeHLWisX0kamUge-1FPo4iBSlF0pz9gcYYt1TIl5w2VwKpGHmUdekw2Bu3PtenO3frq0xLuQrlEiMG5EarHethNcRV0zo6FD63fplLoWRWuYK6_CDnbmi1qO8WZVyhSHkysstRwF4kDXUle9SF6jmgh1T4zQhJ8DcG7iYZ4mLig3JccCnChVUmaWpC3Ja2ad3CO-sHcERS475wqLu3oa27_P-9P9HOxryTTLglEQgCgHjUOLSj3_Ux2h6jVfQ

header : {"typ":"JWT","alg":"RS256"}
Payload ={"iss":"8ecd54db-2852-4aea-88a9-7fc6d8d9898a","sub":6ec75e84-601f-4ca4-9717-1dd1e40dd76e,"iat":1619436267,"exp":1619439867,"aud":"account-d.docusign.com","scope":"signature impersonation"}

Robert

Anton Scheffer

Are you sure you get the same error {"error":"invalid_grant","error_description":"unsupported_grant_type"}?
I get a {"error":"invalid_grant","error_description":"user_not_found"} using your body
Might have something to do with the sub json string value not being surround with ""
But the way, using this forum as a debug service is probably not the fastest way to solve your problems.

User_O7GVP

Ascheffe,
I am trying to implement your solution but I am getting the below error while calling as_crypto.sign function from github (https://github.com/antonscheffer/as_crypto) library. In the github library the function as_crypto.sign only accepts integers to sign.
I tried to run in both Oracle 12 and 19c but no luck. Any help would be highly appreciated.
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 359
ORA-06512: at "SYS.HTP", line 1418
ORA-06512: at "SYS.HTP", line 1494
ORA-06512: at "SYS.HTP", line 1809
ORA-06512: at "AS_CRYPTO", line 726
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "AS_CRYPTO", line 653
ORA-06512: at "AS_CRYPTO", line 670
ORA-06512: at "AS_CRYPTO", line 715
ORA-06512: at "AS_CRYPTO", line 3210

Anton Scheffer

My guess is that you got the parameters mixed up.

User_O7GVP

When I replace my key in your function, I get the error.
l_sign := base64URL_encode( p_raw => as_crypto.sign( utl_raw.cast_to_raw( l_header || '.' || l_payload ), utl_raw.cast_to_raw('MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQC+CXUDT6S44Nni
Q+PGO+Q+8zUdVavAqdz+hGS2Zewjbwwu/Jgxfu32ipE7szK6tadGZVaqLPFdZMFu
XDkG7dM4Skl5wYoaD/M0MgRbT336yyLEc/0wMjvuTAaRmJBR2FP/qJ/rbK9XUGut
sPr0qpIJcW+pKnf4MEHMourskvSieUTfPtUiKlQFHJS/amYQMaNRYBIGOk0XTTGS
7xmQa6jBJ/n+s8dL6+jqKbzpsJ3lYmHdHdQRTaePU4c6FbCF3JD/a0r7czSI+TgY
p4SavSQqZfxkXWEzQR2KgY7HqHkP5qecdHhISN6h0LBJjaVVN0xBkTHpayQnVgie
Johp8HlJAgMBAAECggEAC03vtn812sORYnrMYV3tsWrxRWnzuhXArik8/naoqeDw
thjTito0mJ5cjlMWGPI2XaMomR3sS7RzJzf4MnfSQT52CXagWvWI75lsFexwH4uW
6V3G+Wd7lizSlTWgnCXEtWgX/z6rXYPMq/vjqcs9va8QR91dXpesSlqEpAcH42Q9
7TdsZN1MD+bEN7QRcf5eI4M4gJTcg00/N7alsiVLXnN45HZw8mX6vpzZ7dGxfe3E
nsQTNuuzG6FVwOPaaWFgUx80MCwt0+5OTxhJltdCrGMGZhppA4RYygHfHWLPyb7v
kfu/dJN1HKVmFY/tYXKObkU0xl7P9NWEBut4vxf5UwKBgQD4k9R0FPS7wo21jjch
/TSjKD+BVh1fsxaWVeXFEPvByAWL9Io1s/C2SFIVRVhpAWHvAyVMOYvXMpIpWts6
NLp96FDs8aC0/H7eEjAGbUHxI0RfukrHly4Awx5sKhfiHyMPjCduiMVvtt1B/brZ
PA6TnZ58YoOoZDL4YSAPMJj7nwKBgQDDtiISwixOzJmRUF97fhHGew4cqyoFg1ra
WKhyB0S5x1ZInTuiBVIINKp5RTgK45c3F8hmBmLClWVxCCGnQXxRxNI+0nlsAXyJ
Jbez0Y7uXTTukxocdFsQBC8FYIhnGUxbKdtoFtr0PxGW4WkUCz3wgD/dLzOA6E5Q
VTkwl8FiFwKBgGfuHxDdxilOwcMcD+8VF9HzvQGNiMB/dz0p8uR/IlO2FqOafcLQ
Vh9Qg4lDMiDNJC4ziX+iO9ag3T2PMEPY0fRckqFOsVuaiky9G/lD6yMwFKFVmXhz
vBczfV/KBaxUSmtwtPfhFXOM6Ft0A5BaLNoJN79XzSY1pp30tdvD/OKPAoGBAL+X
6BdxG6swxQ3rHu5oZsJl6VTLg+HNy/2BWbX3w6ITsTZ5Js3gY4RA+rqptsmCMKDw
mXC3w6s2Ug6rpTZ191ydUC3MIq5HbkFdOU/CUSis+oqsW52LkzJExjt/W0mDDKsa
eP6XkVYXAAMzPIkw0DxHcT35XXRO94vRQqF9jwWVAoGAJRVMzxhpPseQv791mwq0
b0qhtaimHLvvVgXrNnnB8vaKKC2oViyuqJygzU6U+iPoED/lrcTvO0FMOgtx4tPo
9OFGPjNa8MtbZoCHMOHAc8HUt5ZlFA19Tl/8cyuMCmCjhelG00BTSHk7Hd65WoK6
bZNs0GvR3n6KBqKqG8G3Ba8='
), as_crypto.KEY_TYPE_RSA, as_crypto.SIGN_SHA256_RSA ) );

User_O7GVP

When I use the key provided in your sample code it works fine.

Anton Scheffer

The key used in that example is in PKCS#1 format, the key in your case is in PKCS#8 format. I've no idea if the Oracle dbms_crypto supports PKCS#8 too, but I will change my code to support both formats. Might take some time :)

Anton Scheffer

If you change the function parse_DER_RSA_PRIV_KEY in as_crypto to the following it might work.
Update: I just compared the results of this new function to the Oracle 21.0.0.0.0 version: both support PKCS#1 and PKCS#8 format and produce the same "sign". And still no support for a key with the headers.

  function parse_DER_RSA_PRIV_key
    ( p_key raw
    , p_key_parameters out tp_key_parameters
    )
  return boolean
  is
    l_dummy raw(3999);
    l_ind pls_integer;
    l_len pls_integer;
    l_tmp pls_integer;
  begin
    p_key_parameters.delete;
    check_starting_sequence( p_key, l_ind );
    l_dummy := get_integer( p_key, l_ind );  -- version
    if utl_raw.substr( p_key, l_ind, 1 ) = c_SEQUENCE
    then -- PKCS#8
      l_tmp := l_ind;
      l_len := get_len( p_key, l_ind );
      if get_oid( p_key, l_ind ) != '2A864886F70D010101' -- 1.2.840.113549.1.1.1 rsaEncryption
      then
        raise value_error;
      end if;
      l_ind := l_tmp + l_len + 2; -- skip optional stuff of AlgorithmIdentifier
      if utl_raw.substr( p_key, l_ind, 1 ) not in ( c_OCTECT, c_BIT_STRING )
      then
        raise value_error;
      end if;
      l_len := get_len( p_key, l_ind );
      check_starting_sequence( p_key, l_ind );
      l_dummy := get_integer( p_key, l_ind );  -- version
    end if;
    -- process PKCS#1
    p_key_parameters(1) := get_integer( p_key, l_ind ); -- n modulus
    p_key_parameters(2) := get_integer( p_key, l_ind ); -- e public
    p_key_parameters(3) := get_integer( p_key, l_ind ); -- d private
    l_dummy := get_integer( p_key, l_ind ); -- p prime1
    l_dummy := get_integer( p_key, l_ind ); -- q prime2
    l_dummy := get_integer( p_key, l_ind ); -- d mod (p-1) exponent1
    l_dummy := get_integer( p_key, l_ind ); -- d mod (q-1) exponent2
    l_dummy := get_integer( p_key, l_ind ); -- (inverse of q) mod p coefficient
    return true;
  exception when value_error
    then
      p_key_parameters.delete;
      return false;
  end;


User_O7GVP

Thank you ascheffer for all your help. It worked for us after you changed the code. Thanks again.

Anton Scheffer

You're welcome :)

MonkeyThink

Hi Anton, firstly, thanks for the package. Very useful on a 19 db where we want to use JWTs for authentication to an APEX app.
I've used the examples and discussion above to be able to prove that using the as_crypto.sign function produces exactly the same JWT as the jwt.io tool for an RSA256 encrypted JWT.
However I can't seem to get the verification working.
Code sample to produce the JWT which matched jwt.io is this:
declare
l_header varchar2(1000);
l_payload varchar2(1000);
l_sign varchar2(1000);
l_token varchar2(32767);
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( '-----BEGIN PRIVATE KEY-----
MIIEvwIBADANBgkqhkiG9w0BAQEFAASCBKkwggSlAgEAAoIBAQC7VJTUt9Us8cKj
MzEfYyjiWA4R4/M2bS1GB4t7NXp98C3SC6dVMvDuictGeurT8jNbvJZHtCSuYEvu
NMoSfm76oqFvAp8Gy0iz5sxjZmSnXyCdPEovGhLa0VzMaQ8s+CLOyS56YyCFGeJZ
qgtzJ6GR3eqoYSW9b9UMvkBpZODSctWSNGj3P7jRFDO5VoTwCQAWbFnOjDfH5Ulg
p2PKSQnSJP3AJLQNFNe7br1XbrhV//eO+t51mIpGSDCUv3E0DDFcWDTH9cXDTTlR
ZVEiR2BwpZOOkE/Z0/BVnhZYL71oZV34bKfWjQIt6V/isSMahdsAASACp4ZTGtwi
VuNd9tybAgMBAAECggEBAKTmjaS6tkK8BlPXClTQ2vpz/N6uxDeS35mXpqasqskV
laAidgg/sWqpjXDbXr93otIMLlWsM+X0CqMDgSXKejLS2jx4GDjI1ZTXg++0AMJ8
sJ74pWzVDOfmCEQ/7wXs3+cbnXhKriO8Z036q92Qc1+N87SI38nkGa0ABH9CN83H
mQqt4fB7UdHzuIRe/me2PGhIq5ZBzj6h3BpoPGzEP+x3l9YmK8t/1cN0pqI+dQwY
dgfGjackLu/2qH80MCF7IyQaseZUOJyKrCLtSD/Iixv/hzDEUPfOCjFDgTpzf3cw
ta8+oE4wHCo1iI1/4TlPkwmXx4qSXtmw4aQPz7IDQvECgYEA8KNThCO2gsC2I9PQ
DM/8Cw0O983WCDY+oi+7JPiNAJwv5DYBqEZB1QYdj06YD16XlC/HAZMsMku1na2T
N0driwenQQWzoev3g2S7gRDoS/FCJSI3jJ+kjgtaA7Qmzlgk1TxODN+G1H91HW7t
0l7VnL27IWyYo2qRRK3jzxqUiPUCgYEAx0oQs2reBQGMVZnApD1jeq7n4MvNLcPv
t8b/eU9iUv6Y4Mj0Suo/AU8lYZXm8ubbqAlwz2VSVunD2tOplHyMUrtCtObAfVDU
AhCndKaA9gApgfb3xw1IKbuQ1u4IF1FJl3VtumfQn//LiH1B3rXhcdyo3/vIttEk
48RakUKClU8CgYEAzV7W3COOlDDcQd935DdtKBFRAPRPAlspQUnzMi5eSHMD/ISL
DY5IiQHbIH83D4bvXq0X7qQoSBSNP7Dvv3HYuqMhf0DaegrlBuJllFVVq9qPVRnK
xt1Il2HgxOBvbhOT+9in1BzA+YJ99UzC85O0Qz06A+CmtHEy4aZ2kj5hHjECgYEA
mNS4+A8Fkss8Js1RieK2LniBxMgmYml3pfVLKGnzmng7H2+cwPLhPIzIuwytXywh
2bzbsYEfYx3EoEVgMEpPhoarQnYPukrJO4gwE2o5Te6T5mJSZGlQJQj9q4ZB2Dfz
et6INsK0oG8XVGXSpQvQh3RUYekCZQkBBFcpqWpbIEsCgYAnM3DQf3FJoSnXaMhr
VBIovic5l0xFkEHskAjFTevO86Fsz1C2aSeRKSqGFoOQ0tmJzBEs1R6KqnHInicD
TQrKhArgLXX4v3CddjfTRJkFWDbE/CkvKZNOrcf1nhaGCPspRJj2KUkj1Fhl9Cnc
dn/RsYEONbwQSjIfMPkvxF+8HQ==
-----END PRIVATE KEY-----' ), 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;

and produces this JWT:
eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWUsImlhdCI6MTUxNjIzOTAyMn0.NHVaYe26MbtOYhSKkoKYdFVomg4i8ZJd8_-RU8VNbftc4TSMb4bXP3l3YlNWACwyXPGffz5aXHc6lty1Y2t4SWRqGteragsVdZufDn5BlnJl9pdR_kdVFUsra2rWKEofkZeIC4yWytE58sMIihvo9H1ScmmVwBcQP6XETqYd0aSHp1gOa9RdUPDvoXQ5oqygTqVtxaDr6wUFKrKItgBMzWIdNZ6y7O9E0DhEPTbE9rfBo6KTFsHAZnMg4k68CDp2woYIaXbmYTWcvbzIuHO7_37GT79XdIwkm95QJ7hYC9RiwrV7mesbY4PAahERJawntho0my942XheVLmGwLMBkQ

To verify I assumed that we need to take the first two elements of the JWT which represent the base64url encoded header and payload and use those in the src parameter of the verify function. The sign parameter is the 3rd element of the JWT, along with public key parameter and the pubkey_alg and sign_alg parameters being the same as in the call to the sign function, which gives me this code:

begin
if as_crypto.verify ( src => utl_raw.cast_to_raw ( 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWUsImlhdCI6MTUxNjIzOTAyMn0')
, sign => utl_raw.cast_to_raw ( 'NHVaYe26MbtOYhSKkoKYdFVomg4i8ZJd8_-RU8VNbftc4TSMb4bXP3l3YlNWACwyXPGffz5aXHc6lty1Y2t4SWRqGteragsVdZufDn5BlnJl9pdR_kdVFUsra2rWKEofkZeIC4yWytE58sMIihvo9H1ScmmVwBcQP6XETqYd0aSHp1gOa9RdUPDvoXQ5oqygTqVtxaDr6wUFKrKItgBMzWIdNZ6y7O9E0DhEPTbE9rfBo6KTFsHAZnMg4k68CDp2woYIaXbmYTWcvbzIuHO7_37GT79XdIwkm95QJ7hYC9RiwrV7mesbY4PAahERJawntho0my942XheVLmGwLMBkQ')
, pub_key => utl_raw.cast_to_raw ( '-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAu1SU1LfVLPHCozMxH2Mo
4lgOEePzNm0tRgeLezV6ffAt0gunVTLw7onLRnrq0/IzW7yWR7QkrmBL7jTKEn5u
+qKhbwKfBstIs+bMY2Zkp18gnTxKLxoS2tFczGkPLPgizskuemMghRniWaoLcyeh
kd3qqGElvW/VDL5AaWTg0nLVkjRo9z+40RQzuVaE8AkAFmxZzow3x+VJYKdjykkJ
0iT9wCS0DRTXu269V264Vf/3jvredZiKRkgwlL9xNAwxXFg0x/XFw005UWVRIkdg
cKWTjpBP2dPwVZ4WWC+9aGVd+Gyn1o0CLelf4rEjGoXbAAEgAqeGUxrcIlbjXfbc
mwIDAQAB
-----END PUBLIC KEY-----' )
, pubkey_alg => as_crypto.KEY_TYPE_RSA
, sign_alg => as_crypto.SIGN_SHA256_RSA )
then
dbms_output.put_line ('Verified');
else
dbms_output.put_line ('Failed verification');
end if ;
end ;
But it always comes back as 'Failed verification' :(

Any thoughts on where I've gone wrong?
Thanks
Adam

MonkeyThink

Ok, I've got a bit further with this. It's something to do with the datatypes/casting of the src and/or sign parameters. Working to track it down now.

Anton Scheffer-Oracle

In case you did not found the solution. Your sign parameter is wrong. You should base64 decode the third part of the JWT. So use something like this:

declare
  function base64URL_decode( p_txt varchar2 )
  return raw
  is
  begin
    return utl_encode.base64_decode( utl_raw.cast_to_raw( translate( p_txt, '-_', '+/' ) ) ); 
  end;
begin
 if as_crypto.verify ( src   => utl_raw.cast_to_raw ( 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWUsImlhdCI6MTUxNjIzOTAyMn0')
           , sign   => base64URL_decode( 'NHVaYe26MbtOYhSKkoKYdFVomg4i8ZJd8_-RU8VNbftc4TSMb4bXP3l3YlNWACwyXPGffz5aXHc6lty1Y2t4SWRqGteragsVdZufDn5BlnJl9pdR_kdVFUsra2rWKEofkZeIC4yWytE58sMIihvo9H1ScmmVwBcQP6XETqYd0aSHp1gOa9RdUPDvoXQ5oqygTqVtxaDr6wUFKrKItgBMzWIdNZ6y7O9E0DhEPTbE9rfBo6KTFsHAZnMg4k68CDp2woYIaXbmYTWcvbzIuHO7_37GT79XdIwkm95QJ7hYC9RiwrV7mesbY4PAahERJawntho0my942XheVLmGwLMBkQ')
           , pub_key => utl_raw.cast_to_raw ( '-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAu1SU1LfVLPHCozMxH2Mo
4lgOEePzNm0tRgeLezV6ffAt0gunVTLw7onLRnrq0/IzW7yWR7QkrmBL7jTKEn5u
+qKhbwKfBstIs+bMY2Zkp18gnTxKLxoS2tFczGkPLPgizskuemMghRniWaoLcyeh
kd3qqGElvW/VDL5AaWTg0nLVkjRo9z+40RQzuVaE8AkAFmxZzow3x+VJYKdjykkJ
0iT9wCS0DRTXu269V264Vf/3jvredZiKRkgwlL9xNAwxXFg0x/XFw005UWVRIkdg
cKWTjpBP2dPwVZ4WWC+9aGVd+Gyn1o0CLelf4rEjGoXbAAEgAqeGUxrcIlbjXfbc
mwIDAQAB
-----END PUBLIC KEY-----' )
          , pubkey_alg => as_crypto.KEY_TYPE_RSA
          , sign_alg  => as_crypto.SIGN_SHA256_RSA )
 then
  dbms_output.put_line ('Verified');
 else
  dbms_output.put_line ('Failed verification');
 end if ;
end ;

P.S. Using pubkey_alg => as_crypto.KEY_TYPE_EC and sign_alg => as_crypto.SIGN_SHA256withECDSAinP1363 you can use as_crypto for the JWT "alg": "ES256"

User_XC37T

Thank you @antonscheffer-oracle, I was able to integrate Apex with DocuSign successfully on 19C. Your comments on this discussion thread were really very helpful.

Awais Khan

@antonscheffer-oracle You are doing a great work,

I want to sign a string using algorithm “SHA256 with ECDSA”. I have tried different combinations for private key and public key algorithm (KEY_TYPE_RSA ,KEY_TYPE_EC ,KEY_TYPE_EdDSA) provided in as_crypto but the package is returning Invalid Cipher error. Could you please suggest that how can I use as_crypto to sign with algorithm “SHA256 with ECDSA”?

begin
dbms_output.put_line( as_crypto.sign( utl_raw.cast_to_raw( 'This is a Test String to Sign' )
, utl_raw.cast_to_raw( 'MIGNAgEAMBAGByqGSM49AgEGBSuBBAAKBHYwdAIBAQQgVeyzaduHJqf0S1zbSjEfuZoNAjKFQXnv3OxDAUYXRlygBwYFK4EEAAqhRANCAARPoyEl1jOnQlI7qsveLz4O3Ci7tG4DWS+C1JngICZXwBjECT71s6zzHVblSiHSa+3EC1BRJy+1OONIXQf2HgXl' ), as_crypto.KEY_TYPE_EdDSA, as_crypto.SIGN_SHA256withECDSA ) );

End;

BluShadow

@User_YK891, Please do NOT drag up old threads to hijack it for your own question. Instead start your own question, referencing this old thread if it seems appropriate.

Please see the FAQ: https://forums.oracle.com/ords/apexds/post/sql-and-pl-sql-faq-2088

Locking this thread

1 - 34
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 5 2023
Added on Apr 6 2021
34 comments
6,284 views