Skip to Main Content
  • Questions
  • "PL/SQL - only" ways to do JSON Token validation

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, jithu.

Asked: November 04, 2022 - 5:13 am UTC

Last updated: February 07, 2023 - 3:36 am UTC

Version: 19.16

Viewed 1000+ times

You Asked

We have a requirement to validate a JSON Web token (JWT) from within the Oracle database (19c)
As I couldn't find any direct ways in PL/SQL which could do JWT validation, a Java program was created which does this job.
This Java program and referenced jar files were then successfully uploaded to the DB using the "loadjava" utility.
A PL/SQL function was then built which internally will call the Java program.
But for some reason the Java program, when invoked via the PL/SQL function returns the error "Cannot obtain jwks from url https://dev-uk.apis.allianz/azuk/ds/uam/identity/oauthv2/v1/.well-known/jwks.json"

I had the same error when the same Java program was executed from my Windows machine.
This was rectified by copying relevant certificate file to "\jre\lib\security" and then importing it using the "keytool" utility.

Questions
---------
1) Does Oracle use its own JVM or the one installed in the Database server, when a stored Java program is invoked from PL/SQL?
2) Similar to what I did in Windows, if we import the certificate to enable connection to the required URL under the "\jre\lib\security" path in the DB server, will it impact any other apps using this JVM?
3) Is there any "PL/SQL - only" ways to do JSON Token validation?


Code snippets for your reference
--------------------------------
1) Java Program

public static String isValidToken(String domain, String encryptedToken) {

DecodedJWT jwt = null;
try {
try {
jwt = JWT.decode(encryptedToken);
} catch (JWTDecodeException e) {

return e.getMessage();
}

JwkProvider provider = new UrlJwkProvider(domain);
Jwk jwk = null;

try {
jwk = provider.get(jwt.getKeyId());
} catch (JwkException e) {

return e.getMessage();
}

Algorithm algorithm = null;

try {
algorithm = Algorithm.RSA256((RSAPublicKey) jwk.getPublicKey(), null);
} catch (IllegalArgumentException | InvalidPublicKeyException e) {

return e.getMessage();
}

algorithm.verify(jwt);

return "VALID";

} catch (Exception e) {
return e.getMessage();
}

}

2) PL/SQL Function

CREATE OR REPLACE FUNCTION is_valid_token (
domain IN VARCHAR2,
token IN VARCHAR2
) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'jwt.checks.JsonWebTokenChecks.isValidToken(java.lang.String, java.lang.String) return java.lang.String'
;

3) Calling PL/SQL function (and thereby Java function)

declare
result varchar2(2000);
url varchar2(1000):= ' https://dev-uk.apis.allianz/azuk/ds/uam/identity/oauthv2/v1';
begin
result := is_valid_token(url,'the-token-the-token');
dbms_output.put_line(result);
end;

4) Result

Cannot obtain jwks from url https://dev-uk.apis.allianz/azuk/ds/uam/identity/oauthv2/v1/.well-known/jwks.json

and Connor said...

I would encourage checking out APEX_JWT to see if it fits your needs

https://asktom.oracle.com/pls/apex/asktom.search?tag=how-does-apex-jwtvalidate-work-in-apex

And if you're not using APEX, you should still install it - just for these types of facilities


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBMS_CRYPTO

m971668, February 01, 2023 - 4:07 pm UTC

(Oracel DB 19c) function DBMS_CRYPTO.verify(...) will validate the JWT (JSON Web Token) using "alg"="RS256"
However, a base64URL.decode function needs to be written and
the public key is needed.

How to construct it from "n" (modulus) and "e" (exponent) in the JWK ( JSON Web Key)?:

{
"kty": "RSA",
"use": "sig",
"kid": "Mr5-AUibfBii7Nd1jBebaxboXW0",
"x5t": "Mr5-AUibfBii7Nd1jBebaxboXW0",
"n": "yr3v1uETrFfT17zvOiy01w8nO-1t67cmiZLZxq2ISDdte9dw-IxCR7lPV2wezczIRgcWmYgFnsk2j6m10H4tKzcqZM0JJ_NigY29pFimxlL7_qXMB1PorFJdlAKvp5SgjSTwLrXjkr1AqWwbpzG2yZUNN3GE8GvmTeo4yweQbNCd-yO_Zpozx0J34wHBEMuaw-ZfCUk7mdKKsg-EcE4Zv0Xgl9wP2MpKPx0V8gLazxe6UQ9ShzNuruSOncpLYJN_oQ4aKf5ptOp1rsfDY2IK9frtmRTKOdQ-MEmSdjGL_88IQcvCs7jqVz53XKoXRlXB8tMIGOcg-ICer6yxe2itIQ",
"e": "AQAB",
"x5c": [
"MIIDBTCCAe2gAwIBAgIQff8yrFO3CINPHUTT76tUsTANBgkqhkiG9w0BAQsFADAtMSswKQYDVQQDEyJhY2NvdW50cy5hY2Nlc3Njb250cm9sLndpbmRvd3MubmV0MB4XDTIxMTAyNDE3NDU1NloXDTI2MTAyNDE3NDU1NlowLTErMCkGA1UEAxMiYWNjb3VudHMuYWNjZXNzY29udHJvbC53aW5kb3dzLm5ldDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMq979bhE6xX09e87zostNcPJzvtbeu3JomS2catiEg3bXvXcPiMQke5T1dsHs3MyEYHFpmIBZ7JNo+ptdB+LSs3KmTNCSfzYoGNvaRYpsZS+/6lzAdT6KxSXZQCr6eUoI0k8C6145K9QKlsG6cxtsmVDTdxhPBr5k3qOMsHkGzQnfsjv2aaM8dCd+MBwRDLmsPmXwlJO5nSirIPhHBOGb9F4JfcD9jKSj8dFfIC2s8XulEPUoczbq7kjp3KS2CTf6EOGin+abTqda7Hw2NiCvX67ZkUyjnUPjBJknYxi//PCEHLwrO46lc+d1yqF0ZVwfLTCBjnIPiAnq+ssXtorSECAwEAAaMhMB8wHQYDVR0OBBYEFDiZG6s5d9RvorpqbVdS2/MD8ZKhMA0GCSqGSIb3DQEBCwUAA4IBAQAQAPuqqKj2AgfC9ayx+qUu0vjzKYdZ6T+3ssJDOGwB1cLMXMTUVgFwj8bsX1ahDUJdzKpWtNj7bno+Ug85IyU7k89U0Ygr55zWU5h4wnnRrCu9QKvudUPnbiXoVuHPwcK8w1fdXZQB5Qq/kKzhNGY57cG1bwj3R/aIdCp+BjgFppOKjJpK7FKS8G2v70eIiCLMapK9lLEeQOxIvzctTsXy9EZ7wtaIiYky4ZSituphToJUkakHaQ6evbn82lTg6WZz1tmSmYnPqRdAff7aiQ1Sw9HpuzlZY/piTVqvd6AfKZqyxu/FhENE0Odv/0hlHzI15jKQWL1Ljc0Nm3y1skut"
],
"issuer": " https://login.microsoftonline.com/{tenantid}/v2.0"
}
Connor McDonald
February 07, 2023 - 3:36 am UTC

You're going to need RSA utilities for that, so I don't think PLSQL is going to be the solution here.

You could load a Java routine into the database to help. Some sample java here

https://forums.oracle.com/ords/apexds/post/how-to-get-public-key-by-using-modulus-and-exponent-6683


PL/SQL Distinguished Encoding Rules

m971668, March 03, 2023 - 12:03 am UTC

No. One just needs to "roll your own" DER ( "Distinguished Encoding Rules" ), because Oracle couldn't be bothered to implement this in PL/SQL packages.

Something better than this [Also there is ASN.1 PKCS#8]:

-- Distinguighed Encoding Rules (DER) ASN.1 PKCS#1 RSA Public key binary file format
/*
RSAPublicKey ::=
SEQUENCE{
modulus INTEGER -- n
, publicExponent INTEGER -- e
}
*/
v_rv_binary raw(2000);
v_key_modulus_binary raw(256); -- 2024 bits
v_public_key_exponent_binary raw(3); -- 24 bits
v_public_key_binary raw(2000);

c_tag_INTEGER raw(1) := hextoraw('02'); -- ASN.1 Universal Primitive Tag: 02 (INTEGER)
c_tag_SEQUENCE raw(1) := hextoraw('30'); -- ASN.1 Universal Constructed Tag: 16 (SEQUENCE)

c_2_LENGTH_BYTES_NEEDED raw(1) := hextoraw('82');
-- 130 = 2 plus 128 [ '10000000' in binary ]
-- ( 2 is the number of bytes used to specify the number of length bytes needed if the value field is more than 127 bytes )
-- a single byte length_byte can specify up to 127 value bytes '01111111' in binary
-- '10000001' in binary is used to specify the number of value bytes will be in stored in 2 bytes, as opposed to the default of 1 byte length_byte
-- more bytes are neeeded to specify longer value fields

c_3_VALUE_BYTES raw(1) := hextoraw('03');
c_257_VALUE_BYTES raw(2) := hextoraw('0101');
c_266_VALUE_BYTES raw(2) := hextoraw('010A');

c_LEADING_ZERO_OF_INTEGER raw(1) := hextoraw('00');

--RSA (256) PKCS#1
v_public_key_binary:=
utl_raw.concat(
r1 => c_tag_SEQUENCE -- 1 byte Tag
, r2 => c_2_LENGTH_BYTES_NEEDED -- 1 byte Length Bytes
, r3 => c_266_VALUE_BYTES -- 2 bytes Length
, r4 => c_tag_INTEGER -- 1 byte Tag
, r5 => c_2_LENGTH_BYTES_NEEDED -- 1 byte Length Bytes
, r6 => c_257_VALUE_BYTES -- 2 bytes Length
, r7 => c_LEADING_ZERO_OF_INTEGER -- 1 byte
, r8 => v_key_modulus_binary -- 256 bytes
, r9 => c_tag_INTEGER -- 1 byte Tag
, r10 => c_3_VALUE_BYTES -- 1 byte Length
, r11 => v_public_key_exponent_binary -- 3 bytes
);

-- binary format of: Privacy Enhanced Mail (PEM): base64 string
v_rv_binary := utl_encode.base64_encode( r => v_public_key_binary );


-- Distinguighed Encoding Rules (DER) ASN.1 PKCS#8 (X.509) RSA Public key binary file format
/*
SubjectPublicKeyInfo ::=
SEQUENCE{
algorithm AlgorithmIdentifier -- see following
, subjectPublicKey BIT_STRING -- RSAPublicKey
}

AlgorithmIdentifier ::=
SEQUENCE{
algorithm OBJECT_IDENTIFIER -- sha256WithRSAEncryption
, parameters ANY DEFINED BY algorithm OPTIONAL -- NULL
}

sha256WithRSAEncryption OBJECT_IDENTIFIER ::=
{ iso(1) member-body(2) us(840) rsadsi(113549) pkcs(1) pkcs-1(1) 1 } -- 1.2.840.113549.1.1.1

RSAPublicKey ::=
SEQUENCE{
modulus INTEGER -- n
, publicExponent INTEGER -- e
}
*/

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library