Need to performs an OAuth 2.0 JWT assertion flow to obtain an access token from Oracle Identity Cloud Service (IDCS). It creates and signs a JWT with a private key, sends it to the token endpoint along with client credentials, and prints the received access token if successful. Trying to get the token for a REST call.
Got a code from GEN AI and need this pl/sql block to be working but Getting error :
ORA-28817: PL/SQL function returned an error
ORA-06512: at "SYS.DBMS_CRYPTO_FFI"
ORA-06512: at “SYS.DBMS_CRYPTO”
Is this(through passing private key) not possible through PL/SQL?
Earlier I was passing key string directly in the block but it suggested to store in a table.
set define off;
DECLARE
------------------------------------------------------------------
-- Inputs (same as Python)
------------------------------------------------------------------
l_userappid VARCHAR2(200) := 'TEST@TEST.COM';
l_alias VARCHAR2(200) := 'PUBLIC-KEY';
l_client_id VARCHAR2(200) := 'xxxxx';
l_client_secret VARCHAR2(200) := 'xxxyy';
l_token_url VARCHAR2(500) := 'https://idcs.identity.cloud.com/oauth2/v1/token';
l_scope VARCHAR2(200) := 'titest';
------------------------------------------------------------------
-- JWT variables
------------------------------------------------------------------
l_header CLOB;
l_payload CLOB;
l_unsigned_jwt CLOB;
l_signature_raw RAW(32767);
l_jwt_assertion CLOB;
------------------------------------------------------------------
-- Time (epoch)
------------------------------------------------------------------
l_iat NUMBER := FLOOR((SYSDATE - DATE '1970-01-01') * 86400);
l_exp NUMBER := FLOOR((SYSDATE - DATE '1970-01-01') * 86400) + 3600;
------------------------------------------------------------------
-- Private key (PKCS#8, NO -----BEGIN----- lines)
------------------------------------------------------------------
l_private_key_raw CLOB ;
------------------------------------------------------------------
-- HTTP
------------------------------------------------------------------
l_auth_header VARCHAR2(4000);
l_response CLOB;
------------------------------------------------------------------
-- Base64URL encode
------------------------------------------------------------------
FUNCTION base64url_encode(p_input RAW) RETURN VARCHAR2 IS
l_b64 VARCHAR2(32767);
BEGIN
l_b64 := UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(p_input)
);
l_b64 := REPLACE(l_b64, '+', '-');
l_b64 := REPLACE(l_b64, '/', '_');
l_b64 := REPLACE(l_b64, '=', '');
RETURN l_b64;
END;
BEGIN
SELECT UTL_ENCODE.BASE64_DECODE(
UTL_RAW.CAST_TO_RAW(
REPLACE(
REPLACE(private_der_key, CHR(10), ''),
CHR(13), ''
)
)
)
INTO l_private_key_raw
FROM jwt_keys
WHERE key_name = 'PRIVATE_KEY';
------------------------------------------------------------------
-- JWT HEADER (Python: headers = {"kid": alias})
------------------------------------------------------------------
l_header :=
'{"alg":"RS256","typ":"JWT","kid":"' || l_alias || '"}';
------------------------------------------------------------------
-- JWT PAYLOAD
------------------------------------------------------------------
l_payload :=
'{' ||
'"iss":"",' ||
'"sub":"' || l_userappid || '",' ||
'"aud":"https://identity.cloud.com/",' ||
'"iat":' || l_iat || ',' ||
'"exp":' || l_exp ||
'}';
------------------------------------------------------------------
-- Base64URL(header.payload)
------------------------------------------------------------------
l_unsigned_jwt :=
base64url_encode(UTL_RAW.cast_to_raw(l_header)) || '.' ||
base64url_encode(UTL_RAW.cast_to_raw(l_payload));
l_unsigned_jwt := REPLACE(REPLACE(l_unsigned_jwt, CHR(10), ''), CHR(13), '') ;
DBMS_OUTPUT.PUT_LINE('l_unsigned_jwt:'||l_unsigned_jwt);
------------------------------------------------------------------
-- SIGN JWT (RS256)
------------------------------------------------------------------
l_signature_raw :=
DBMS_CRYPTO.sign(
src => UTL_RAW.cast_to_raw(l_unsigned_jwt),
sign_alg => DBMS_CRYPTO.sign_sha256_rsa,
pubkey_alg =>DBMS_CRYPTO.KEY_TYPE_RSA,
prv_key => UTL_RAW.cast_to_raw(l_private_key_raw)
);
DBMS_OUTPUT.PUT_LINE('l_signature_raw:'|| l_signature_raw);
IF l_signature_raw IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'RSA signing failed – invalid private key');
END IF;
l_jwt_assertion :=
l_unsigned_jwt || '.' ||
base64url_encode(l_signature_raw);
------------------------------------------------------------------
-- BASIC AUTH (client_id:client_secret)
------------------------------------------------------------------
l_auth_header :=
'Basic ' ||
UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(
UTL_RAW.cast_to_raw(l_client_id || ':' || l_client_secret)
)
);
------------------------------------------------------------------
-- HTTP HEADERS
------------------------------------------------------------------
APEX_WEB_SERVICE.g_request_headers.delete;
APEX_WEB_SERVICE.g_request_headers(1).name := 'Content-Type';
APEX_WEB_SERVICE.g_request_headers(1).value := 'application/x-www-form-urlencoded;charset=UTF-8';
APEX_WEB_SERVICE.g_request_headers(2).name := 'Authorization';
APEX_WEB_SERVICE.g_request_headers(2).value := l_auth_header;
------------------------------------------------------------------
-- TOKEN REQUEST
------------------------------------------------------------------
l_response :=
APEX_WEB_SERVICE.make_rest_request(
p_url => l_token_url,
p_http_method => 'POST',
p_body =>
'grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer'
|| '&assertion=' || l_jwt_assertion
|| '&scope=' || l_scope,
p_wallet_path => rms_admin.rms_rest_service.get_wallet_directory
--,p_wallet_pwd => 'wallet_password'
);
DBMS_OUTPUT.put_line('Token response:');
DBMS_OUTPUT.put_line(l_response);
END;
/
The equivalent python script works and returns the token.
import jwt
import requests
import base64
from datetime import datetime, timedelta
userappid = "*****_ww@test.com"
private_keyname = "private_key.pem" #The private key file you generated in Step 7 should be in the same directory as this Python code.
alias = "<<alias>>>" #The alias set up in IDCS is the value you provided in the ticket created for Identity in Step 8.
client_id = "**************************" #Client ID You received this via email in Step 6.
client_secret = "**************************" #Client Secret You received this via email in Step 6.
token_url = "https://idcs-*******.identity.cloud.com/oauth2/v1/token" #You received this via email after completing Step 6.
token_scope = "**" #You received this via email in Step 6.
# Define the payload (claims)
payload = {
"iss": "", # Issuer
"sub": userappid, # Subject
"aud": "https://identity.cloud.com/", # Audience
"exp": datetime.utcnow() + timedelta(hours=1), # Expiration time (1 hour from now)
"iat": datetime.utcnow() # Issued at time
}
# Load the private key
with open(private_keyname, "rb") as f:
private_key = f.read()
# Create headers with the kid
headers = {"kid": alias}
# Create and sign the JWT assertion
jwt_assertion = jwt.encode(payload, private_key, algorithm="RS256", headers=headers)
# Concatenate client_id and client_secret with a colon separator
credentials = f"{client_id}:{client_secret}"
# Encode the concatenated string using Base64
credentials_base64 = "Basic " + base64.b64encode(credentials.encode()).decode()
# Token request
headers = {"Content-Type": "application/x-www-form-urlencoded;charset=UTF-8", "Authorization": credentials_base64}
data = {
"grant_type": "urn:ietf:params:oauth:grant-type:jwt-bearer",
"assertion": jwt_assertion,
"scope": token_scope
}
# Make token request
response = requests.post(token_url, headers=headers, data=data)
print("Token request response:", response.status_code)
# Check response
if response.status_code == 200:
token_response = response.json()
access_token = token_response.get("access_token")
print("Access Token:", access_token)
else:
print("Token request failed:", response.text)