1

I am trying to set up a small Rest API and connect to it from an OCI Autonomous DB. But I am stuck.

This request works:

SELECT apex_web_service.make_rest_request(
p_url => 'https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson'||CHR(38)||'limit=100',
p_http_method => 'GET') JSON
FROM DUAL;

This is a call to an existing public Rest-API (not mine).

This does not work:

SELECT apex_web_service.make_rest_request(
p_url => 'https://raspi.brima.dynv6.net:1880/gettest',
p_http_method => 'GET') JSON
FROM DUAL;

This is my own API. It's only a "Hello World!" example. It is https, because that's a requirement for OCI, but it is not password protected or anything. You should be able to reach it from your browser. So, it is not a "private endpoint", right?

For this call, I get:

ORA-29273: HTTP request failed
ORA-06512: at "APEX_210200.WWV_FLOW_WEB_SERVICES", line 1182
ORA-06512: at "APEX_210200.WWV_FLOW_WEB_SERVICES", line 782
ORA-01031: insufficient privileges
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1209

The same happens when I try to do the request via UTL_HTTP.

I setup an ACE for Host '*.brima.dynv6.net' and port 1880 for my current DB user with privilege CONNECT and RESOLVE, but that did not change anything. Here the output of DBA_HOST_ACLS and DBA_HOST_ACES as JSON:

[
{"host":"raspi.brima.dynv6.net","acl":"NETWORK_ACL_E05BF60BBE2A22C1E0535318000AF6E5","aclid":"0000000080002750","acl_owner":"SYS","use_dns_proxy":"N\/A","private_target":"NO"}
,{"host":"*","acl":"NETWORK_ACL_9D275AE6BBA22013E0530F86E50AB596","aclid":"0000000080002724","acl_owner":"SYS","use_dns_proxy":"N\/A","private_target":"NO"}
,{"host":"raspi.brima.dynv6.net","lower_port":1880,"upper_port":1880,"acl":"NETWORK_ACL_E056B33BAFFC275BE0535318000A1197","aclid":"000000008000274F","acl_owner":"SYS","use_dns_proxy":"N\/A","private_target":"NO"}
]

[
{"host":"*","ace_order":1,"grant_type":"GRANT","inverted_principal":"NO","principal":"GSMADMIN_INTERNAL","principal_type":"DATABASE","privilege":"RESOLVE","use_dns_proxy":"N\/A","private_target":"NO"}
,{"host":"*","ace_order":2,"grant_type":"GRANT","inverted_principal":"NO","principal":"GGSYS","principal_type":"DATABASE","privilege":"RESOLVE","use_dns_proxy":"N\/A","private_target":"NO"}
,{"host":"*","ace_order":3,"grant_type":"GRANT","inverted_principal":"NO","principal":"C##OMLREST2","principal_type":"DATABASE","privilege":"RESOLVE","use_dns_proxy":"N\/A","private_target":"NO"}
,{"host":"raspi.brima.dynv6.net","ace_order":1,"grant_type":"GRANT","inverted_principal":"NO","principal":"SMARTHOME","principal_type":"DATABASE","privilege":"RESOLVE","use_dns_proxy":"N\/A","private_target":"NO"}
,{"host":"*","ace_order":3,"grant_type":"GRANT","inverted_principal":"NO","principal":"C##OMLREST2","principal_type":"DATABASE","privilege":"CONNECT","use_dns_proxy":"N\/A","private_target":"NO"}
,{"host":"raspi.brima.dynv6.net","lower_port":1880,"upper_port":1880,"ace_order":1,"grant_type":"GRANT","inverted_principal":"NO","principal":"SMARTHOME","principal_type":"DATABASE","privilege":"CONNECT","use_dns_proxy":"N\/A","private_target":"NO"}
]

Also, why is the first example working without a special ACL configuration?

I don't understand the problem. I already looked at this question, but it wasn't really answered.

Can anyone point me in the right direction?

Thanks & Regards, Nadine

N.Bri
  • 86
  • 5
  • Did your ACE specify your non-standard port 1880? (Possibly there's a wildcard for any host on standard ports, which is why the first call worked. Just speculating though.) – Alex Poole May 30 '22 at 23:45
  • Any relevant data in `DBA_HOST_ACLS` and `DBA_HOST_ACES`? – Fat P May 31 '22 at 07:24
  • Looks like that URL is private and cannot be accessed from the internet, right? If that's the case you need to configure your database with a private endpoint in a subnet that can access that URL and follow this doc, https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/call-web-services.html#GUID-844FCA1B-3619-4B94-8164-00A89484DEB3. That doc is for UTL_HTTP, but the same applies to apex_web_service too. – Yasin B May 31 '22 at 16:53
  • @Alex Poole, you are right, there was a wildcard for any host existing. I added the port 1880 to the ACE for my adress, but that did not help. – N.Bri Jun 01 '22 at 04:24
  • @Fat P: I added the details of ACLs and ACEs to the question above. – N.Bri Jun 01 '22 at 04:29
  • @Yasin B: My URL which I posted in the question is publicly available. You can open it with your browser. So it's not private? Or do I understand the definition of "private endpoint" incorrectly? – N.Bri Jun 01 '22 at 04:29
  • The URL "https://raspi.brima.dynv6.net:1880/gettest" does not seem to be accessible publicly. – Yasin B Jun 02 '22 at 16:14
  • @YasinB: But with my company mobile, which is not in my home wifi, I can access it. Are you sure you tried https ? Also, without the port number, it won't work. I did not expose port 80... – N.Bri Jun 06 '22 at 16:02
  • Yes, going to https://raspi.brima.dynv6.net:1880/gettest fails as the host is not publicly accessible. Are you sure you are not in your company VPN? – Yasin B Jun 07 '22 at 18:28

0 Answers0