ORA-24247: Access denied by ACL from within PL/SQL function but NOT from SQL

Question

I've successfully set up the ACL for my user and URL. I confirm this by running:

select utl_http.request(*my URL*) 
from   dual;

which returns the corresponding HTML code.

However, when I place this code inside a PL/SQL function, as follows:

create or replace function temp_func (p_url varchar2)
return varchar2 is
    v_output varchar2(1000);
begin
    select  utl_http.request(p_url)
    into    v_output
    from    dual;

    return v_output;
end;

and run this code from an anonymous PL/SQL block:

declare
    v_result varchar2(1000);
begin
    v_result := temp_func(*my URL*);
    dbms_output.put_line(v_result);
end;

I get the following error stack:

Error report -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
ORA-06512: at "SIEF.TEMP_FUNC", line 7
ORA-06512: at line 4
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

Is there any way to fix this?

I was reading https://support.oracle.com/knowledge/Oracle%20Database%20Products/1074843_1.html and the closest thing I find is: '4. Granting the ACL via roles does not work when the service is requested through from a PLSQL procedure', however, I did not use roles while setting up the ACL.

Thank you!

My database version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE   11.2.0.3.0  Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

My ACL setup:

-- Creating ACL
begin
    dbms_network_acl_admin.create_acl(
        acl         => 'WS_test_acl.xml',
        description => 'ACL file for testing purposes.',
        principal   => *my user*,
        is_grant    => TRUE,
        privilege   => 'connect');
end;
-- Adding URL to ACL
begin
    dbms_network_acl_admin.assign_acl(
        acl         => 'WS_test_acl.xml',
        host        => *my URL*);
end;

So how did you set up the ACLs? It would be helpful to include all relevant statements you executed in the question. — Alex Poole, May 27 '19 at 17:52
Thank you for your time! Jon Heller's suggestion has solved my problem, but I'll include my ACL setup in the question for the record. — João Lovegrove, May 28 '19 at 10:20

score 2 · Accepted Answer · answered May 27 '19 at 23:55

2

When things work in anonymous blocks but not in stored procedures it's usually because of definer's rights versus invoker's rights. Anonymous blocks and invoker's rights procedures can use privileges granted through roles, but definer's rights procedures cannot.

Try changing return varchar2 is to return varchar2 authid current_user is.

answered May 27 '19 at 23:55

Jon Heller

34,999
6
74
132

It works! Thank you for your time. I appreciate the suggestion and also the explanation - I was unaware of definer's rights and invoker's rights. – João Lovegrove May 28 '19 at 10:10

ORA-24247: Access denied by ACL from within PL/SQL function but NOT from SQL

1 Answers1