oracle-databaseplsqloracle-cloud-infrastructureoracle-autonomous-db

PL/SQL UTL_HTTP request returns "ORA-01031: insufficient privileges" on OCI AutonomousDB


I'm trying to do a REST/HTTP request from PL/SQL in OCI.

I am stumped when trying UTL_HTTP ( but successful using APEX_WEB_SERVICE package).

Has anyone had success using UTL_HTTP on OCI AutonomousDB?

BEGIN
    UTL_HTTP.SET_WALLET('');
    http_request := UTL_HTTP.begin_request(UTL_URL.Escape([url]), 'GET');
    http_response := UTL_HTTP.get_response(http_request);
    UTL_HTTP.read_text(http_response, return_text);
    DBMS_OUTPUT.put_line (return_text);
END;

Error report – ORA-01031: insufficient privileges ORA-06512: at "SYS.UTL_HTTP", line 136 ORA-06512: at "SYS.UTL_HTTP", line 1410
ORA-06512: at line 7
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without the necessary privileges.
*Action: Ask your database administrator or designated security administrator to grant you the necessary privileges

I have setup ACL as follows so http privilege is granted:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => '[domain]',
        lower_port => 443,
        upper_port => 443,
        ace =>  xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => '[name]',
                             start_date => SYSTIMESTAMP,
                             principal_type => xs_acl.ptype_db));
END;
/

Solution

  • try using

    privilege_list => xs$name_list('connect', 'resolve')

    Docs says:

    Security Model This package is an invoker's rights package and the invoking user will need the connect privilege granted in the access control list assigned to the remote network host to which he wants to connect, as well as the use-client-certificates or the use-passwords privilege to authenticate himself with the remote Web server using the credentials stored in an Oracle wallet.

    You also need 'resolve' to resolve the domain name.