Use Private Functions with ATP

We wanted to limit the use of certain functions to a database on a private subnet.

Ref:

https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/call-web-services.html#GUID-844FCA1B-3619-4B94-8164-00A89484DEB3

BEGIN
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'aaaaaaaa.ap-melbourne-1.functions.oci.oraclecloud.com',
         ace => xs$ace_type( privilege_list => xs$name_list('http'),
                             principal_name => 'ADMIN',
                             principal_type => xs_acl.ptype_db),
                             private_target => TRUE);
END;
/
  • Create an Appliation – private subnet on your VCN
  • Create a function and deploy it to the application
  • Get the endpoint of the function
  • Add the host name of the end point to the ATP ACL

Ref:

https://dba.stackexchange.com/questions/115807/oracle-viewing-settings-for-dbms-network-acl-admin-acl

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_NETWORK_ACL_ADMIN.html#GUID-D70BE754-F21E-4C74-8D26-25258C6F4FE1

SELECT HOST, LOWER_PORT, UPPER_PORT,
       ACE_ORDER, PRINCIPAL, PRINCIPAL_TYPE,
       GRANT_TYPE, INVERTED_PRINCIPAL, PRIVILEGE,
       START_DATE, END_DATE
  FROM (SELECT ACES.*,
DBMS_NETWORK_ACL_UTILITY.CONTAINS_HOST('stfjhixhofa.ap-melbourne-1.functions.oci.oraclecloud.com',
                                                      HOST) PRECEDENCE
          FROM DBA_HOST_ACES ACES)
 WHERE PRECEDENCE IS NOT NULL
 ORDER BY PRECEDENCE DESC,
          LOWER_PORT NULLS LAST,
          UPPER_PORT NULLS LAST,
          ACE_ORDER;

You can use this query to see your ACL privileges. Set the CONTAINS_HOST to the host name of your end point.

Note:If you set ROUTE_OUTBOUND_CONNECTIONS to PRIVATE_ENDPOINT, setting the private_target parameter to TRUE is not required in this API. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

Try the function next:

Not authenticated – what is going on?

https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-cloud-subprograms.html#GUID-CC237BEC-92CF-4051-82BE-76ADBB57F4B5

https://blogs.oracle.com/datawarehousing/post/how-to-level-up-and-invoke-an-oracle-function-or-any-cloud-rest-api-from-within-your-autonomous-database

We need a credential to authenticate, lets use this sub program to create a credential and try again..

To find out what the values are, Oracle provide them when you create the credential

Go to Identity Domain > Users

Create a key for a user:

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => ‘YOUR_OCI_KEY_CRED’,
       user_ocid       => ‘ocid1.user.oc1..aaaaaaaam2…’,
       tenancy_ocid    => ‘ocid1.tenancy.oc1..aaaaaaaakc…’,
       private_key     => ‘MIIEogIBAAKCAQEAtU…’,
       fingerprint     => ‘f2:db:d9:18:a4:aa:fc:83:f4:f6..’);
END;
/

Invoke the function, in this case I have used the hello world function which I created earlier. {create application, then follow instructions ‘getting started’}

Get the endpoint for your function:

SET SERVEROUTPUT ON
  DECLARE
    resp DBMS_CLOUD_TYPES.resp;
  BEGIN
    --HTTP POST Request
    resp := DBMS_CLOUD.send_request(
               credential_name => 'YOUR_OCI_KEY_CRED',
               uri => 'https://YOUR-ENDPOINT.ap-melbourne-1.xxx/actions/invoke',
               method => DBMS_CLOUD.METHOD_POST,
               body => UTL_RAW.cast_to_raw('{"name": "Bob"}')
            );
     
  -- Response Body in TEXT format
  DBMS_OUTPUT.put_line('Body: ' || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_text(resp) || CHR(10));
  
  -- Response Headers in JSON format
  DBMS_OUTPUT.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));
 
  -- Response Status Code
  DBMS_OUTPUT.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_status_code(resp));
END;
/ 

Body: ————
{“message”:”Hello Bob”}

Headers: 
————
{“Content-Length”:”23″,”Content-Type”:”application/json”,”Date”:”Sat, 04 May
2024 02:06:35
GMT”,”Fn-Call-Id”:”0xxxxxxx0″,”Fn-Fdk-Runtime”:”node/18.19.1″,”
Fn-Fdk-Version”:”fdk-node/0.0.65
(njsv=v18.19.1)”,”Opc-Request-Id”:”/01HX0NMSB7000000000000B3W0/01HX0NMSB70000000
00000B3W1″,”Connection”:”close”}

Status Code: 
————
200

Thats it.

Now you have a private function which can be invoked by ATP on the Private VCN subnet.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *