Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Function for RSA SHA-256 digital signature

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
Best 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;
Answers
-
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
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
-
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