ORA-20001: Authentication failed when calling external api in oracle apex

Question

I am currently trying to consume a external api through Oracle apex. I was able to call below api by hard coding 'Baerer' taken but, at the line no 5, it throws an error

ORA-20001: Authentication failed. ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 474 ORA-06512: at "APEX_220100.WWV_FLOW_WEBSERVICES_API", line 682 ORA-06512: at "APEX_220100.WWV_FLOW_WEBSERVICES_API", line 705 ORA-06512: at line 5

My pl/sql code is

 declare l_bearer_token varchar2(1000); l_clob clob; begin apex_web_service.oauth_authenticate_credential( p_token_url => 'https://www.xxxportaltest.ab.cde.us/api/tokens/auth/token', p_credential_static_id => 'EDU_CRED'); apex_web_service.g_request_headers(1).name := 'Authorization'; apex_web_service.g_request_headers(1).value := 'Bearer '||apex_web_service.oauth_get_last_token; -- call the REST API, using the module URL. The request headers are already set in the g_request_headers l_clob := apex_web_service.make_rest_request ( p_url => 'https://www.xxxportaltest.ab.cde.us/api/requests/v1/Request/A7ACDAED-FAF1-9BAE-AA56-01B925E5156F/', p_http_method => 'GET', p_wallet_path => 'file:/u01/app/oracle/admin/orcl/wallet/', p_wallet_pwd => 'WalletPWD!'); -- display the result of the REST API call dbms_output.put_line('Here is the API call result -> '||l_clob); end;

I have already added certificate of 'www.xxxportaltest.ab.cde.us' in the wallet and referred in Oracle Apex admin instance setting. Further, I have already added ACL, granted privileges and assigned them.

SELECT host, acl FROM dba_host_acls;

| HOST | ACL | |:---- |:------:| | xxxportaltest.ab.cde.us | /sys/acls/hr_utl_http.xml |

SELECT privilege, grant_type, principal, host FROM dba_host_aces;

| PRIVILEGE | GRANT_TYPE | PRINCIPAL | HOST | | :---- | :------: | :-----: | -----: | | RESOLVE | GRANT | PUBLIC | xxxportaltest.ab.cde.us | | CONNECT | GRANT | PUBLIC | xxxportaltest.ab.cde.us | | HTTP | GRANT | PUBLIC | xxxportaltest.ab.cde.us |

I tried this api through POSTMAN and it works but with 'Auth URL' (https://www.xxxportaltest.ab.cde.us/api/tokens/oauth2/authorise) and with 'Authorize using browser' checkbox. But I have no idea on how to config the same in oracle apex.Can someone help me to solve this issue?

So, it looks like the OAUTH_AUTHENTICATE_CREDENTIAL call is failing. 1. Are you sure that the Credentials stored in the EDU_CRED Web Credential are correct? Is that API really using the OAuth Client Credentials Flow? Note that the exact OAuth flow is important here - "hard coded Bearer token" tells us nothing! — Carsten
– Carsten, Commented May 21, 2024 at 6:51
Hi @Carsten, Thanks. Yes., when debugging I found that the issue is at the line of oauth_authenticate_credential. I confirmed that the credentials provided by 'EDU_CRED' are accurate as I tested in POSTMAN. But when I call through POSTNMAN, I had use 'Authorization URL' and it generates token using web browser. But I don't know how I can handle the same in Oracle Apex. — chk.buddi
– chk.buddi, Commented May 21, 2024 at 7:17

Carsten · Accepted Answer · 2024-05-22 08:06:23Z

The OAUTH_AUTHENTICATE function only supports the OAuth Client Credentials flow. As you're working with an Authorization URL, and there is a browser redirect to a consent page, this is either the Implicit Grant or the Authorization Code flow. Both are not supported by the OAUTH_AUTHENTICATE - remember that APEX runs completely in the Oracle Database.

Try to use an authentication flow which does not require a browser redirect, which is OAuth Client Credentials, Basic Authentication or simple API keys. If such is not available, you need to implement the token challenge manually and yourself.

Collectives™ on Stack Overflow

ORA-20001: Authentication failed when calling external api in oracle apex

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related