In OAuth 2.0 client credentials, acquiring a token necessitates passing the client ID and client secret. Upon successful validation, a token is returned, which is then utilized to access other secured web services. This token remains valid for multiple calls to web services until its expiration.
One method of calling web services involves using UTL_HTTP, typically employed by developers on Oracle databases that does not have Oracle Apex installed. However, this approach presents several disadvantages:
- A need arises to develop an additional mechanism for storing and retrieving credentials. These credentials are stored in an unencrypted table, raising significant security concerns.
- Manual management of token expiration becomes necessary, requiring awareness of its expiry to fetch a new token.
- Performance is impacted; in the provided example, each call to the web service generates a new token instead of reusing the existing one until its expiration.
Using UTL_HTTP
declare
CURSOR get_cred IS
select lookup_code, meaning
from eba_apex_lookups
where lookup_type like '%APEX_CRED%'
and enabled_flag = 'Y';
v_req UTL_HTTP.req;
v_resp UTL_HTTP.resp;
v_txt VARCHAR2 (5000);
v_access_token VARCHAR2 (5000);
v_url VARCHAR2 (1024);
auth_body VARCHAR2 (4000);
g_wallet varchar2(100);
g_wallet_pwd varchar2(100);
BEGIN
-- Get credentials
FOR x IN get_cred LOOP
IF x.lookup_code = 'TOKEN_URL' THEN
v_url := x.meaning;
ELSIF x.lookup_code = 'AUTH_BODY' THEN
auth_body := x.meaning;
ELSIF x.lookup_code = 'WALLET' THEN
g_wallet := x.meaning;
ELSIF x.lookup_code = 'WALLET_PWD' THEN
g_wallet_pwd := x.meaning;
END IF;
END LOOP;
-- Generate Token
UTL_HTTP.set_wallet (g_wallet, g_wallet_pwd);
v_req := UTL_HTTP.begin_request (v_url, 'POST', ' HTTP/1.1');
UTL_HTTP.set_body_charset('UTF-8');
UTL_HTTP.set_header (v_req,
'Content-Type',
'application/x-www-form-urlencoded');
UTL_HTTP.set_header (v_req, 'Content-Length', LENGTH (auth_body));
UTL_HTTP.write_text (v_req, auth_body);
v_resp := UTL_HTTP.get_response (v_req);
UTL_HTTP.read_line (v_resp, v_txt);
-- Get Access Token
SELECT JSON_VALUE (v_txt, '$.access_token')
INTO v_access_token
FROM DUAL;
UTL_HTTP.end_response (v_resp);
IF length(v_access_token) > 1 THEN
-- Call Rest API
v_url := 'https://graph.microsoft.com/v1.0/users/' || v_upn;;
v_req := UTL_HTTP.begin_request (v_url, 'GET', ' HTTP/1.1');
UTL_HTTP.set_body_charset('UTF-8');
UTL_HTTP.set_header(v_req, 'Authorization', v_access_token);
v_resp := UTL_HTTP.get_response (v_req);
-- read response
UTL_HTTP.read_line (v_resp, v_txt);
dbms_output.put_line ('response ' || v_txt);
END IF;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response (v_resp);
IF v_resp.status_code < 200 OR v_resp.status_code >= 300 THEN
dbms_output.put_line('Error Occurred');
END IF;
END;
The use of the ‘Web Credentials’ from Oracle Apex and APEX_WEB_SERVICE solves the pain points of using the UTL_HTP. Developers does not have to deal with calling the token and managing the token expiration. This is facilitated by passing the parameters p_credential_static_id and p_token_url to APEX_WEB_SERVICE, referencing the ‘Web Credentials’ and URL token, thereby streamlining the process. Additionally, ‘Web Credentials’ stored the client secret on encrypted.
Web Credentials
Using APEX_WEB_SERVICE
DECLARE
l_response_clob CLOB;
l_rest_url VARCHAR2(1000);
l_token_url VARCHAR2(1000);
l_count_posted PLS_INTEGER;
v_upn VARCHAR2(100) := '<userPrincipalName>';
BEGIN
-- To use Web Credentials, you need to create apex session
apex_session.create_session (p_app_id => 100, p_page_id => 1, p_username => 'ORCLAPEX');
-- Set the URL of the REST Service.
l_rest_url := 'https://graph.microsoft.com/v1.0/users/' || v_upn;
l_token_url := 'https://login.microsoftonline.com/common/oauth2/v2.0/token';
-- Call the web services passing the two parameters.
-- p_credential_static_id references the "MS_GRAPH_API_ACCESS" that was set up Workspace Utilities.
-- p_token_url is token url for the l_rest_url
l_response_clob := apex_web_service.make_rest_request(
p_url => l_rest_url,
p_http_method => 'GET',
p_credential_static_id => 'MS_GRAPH_API_ACCESS',
p_token_url => l_token_url);
IF apex_web_service.g_status_code = 200 THEN
-- 4. Check the response => l_response_clob, p_strict => true);
END IF;
END;
/
Please note before you can call the web services you have to make sure the ACL and certificates are already set up. Click here for assistance in setting them up.
