I created a REST APIs and tested the endpoint - works and fetches data.
I secured them using roles and privileges in ORDS, protected the module and re-tested the endpoint - works as expected, Not Authorized.
I created OAUTH client for the privilege and granted OAUTH client role linking client name and role name. I then created a Web Credential using client_id and client_secret and assigned the web credential to the REST Data Source. I used this REST Data Source in a report and it gives the above "ORA-29723: HTTP request failed" error.
I tested the REST endpoint using Postman without any token and it gave 401 Not Authorized error as expected. I re-tested the endpoint in Postman by generating a token using the OAuth2 creds - it WORKS and fetches data fine using Postman.
I setup an Authentication Scheme using the Web Credential store, Social Sign-in and Generic OAuth2 Provider - the report in my app using REST Data Source still does NOT work.
I debugged the report and here is the error -
Exception in "begin_request":
Error Stack: ORA-29273: HTTP request failed
ORA-12541: TNS:no listener
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
Backtrace: ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 756
Exception in "Employee":
Error Stack: ORA-29273: HTTP request failed
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_WEB_SRC", line 2002
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_WEB_SRC", line 1918
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1182
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 431
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1182
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 782
ORA-12541: TNS:no listener
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 756
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1023
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1371
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 410
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 898
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 975
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1371
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_WEB_SRC_UTILS", line 1149
ORA-0651~
Backtrace: ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_WEB_SRC", line 2002
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_WEB_SRC", line 1918
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1182
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 431
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1182
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 782
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 756
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1023
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1371
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 410
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 898
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 975
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 1371
ORA-06512: at "APEX_220100.WWV_FLOW_EXEC_WEB_SRC_UTILS", line 1149
ORA-06512: at "APEX_220100.WWV_FLOW_PLUGIN_UTIL", line 4072
ORA-06~
Our ORDS is hosted on http on the intranet, the endpoint works with Postman but the report does not work when the REST Data Source with Web Credential is used on the report. The report was working fine before I implemented the security on the REST API using role, privilege, OAUTH client & role, Web Credential, Authentication Scheme. If I remove the security from the API, the report will work again.
What did I miss in between where the API is not working on a report in my app using REST Data Source which uses the Web Credential and the Web Credential?
I ran this query and it fetches ZERO rows -
SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges
Do I need to setup any ACL and if so, how? Is there a video or an article on how to setup security on REST APIs using Web Credential and REST Data Source?
TIA