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!
how to identify usb port there is detect a usb device means that if i attach a usb device(as pendrive) java(in swing) program says this is a usbdevice and it is connected with this port....
i am new user in java program... plz help me
See DBMS_Crypto.Hash and use 4 as the type : HASH_SH256 CONSTANT PLS_INTEGER := 4;
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;
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)?
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.
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
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' ), ....
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
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
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
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 I am following this webpage: How to get an access token with JWT Grant (0 Bytes) What should I do??
Robert
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.
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;
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
I suspect that you will get more help if you show the code you have used.
obtain_jwt.txt (9.19 KB)Ascheffer, Here is the code. Hope you can find the resolution. Thanks in advance.
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.
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
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"}
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.
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
My guess is that you got the parameters mixed up.
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 ) );
When I use the key provided in your sample code it works fine.
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 :)
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;
Thank you ascheffer for all your help. It worked for us after you changed the code. Thanks again.
You're welcome :)
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
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.
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"
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.
@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;
@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