ORA-24247: ACL 从 PL/SQL 函数内拒绝访问，但不是从 SQL 访问 [英] ORA-24247: Access denied by ACL from within PL/SQL function but NOT from SQL

问题描述

我已经成功为我的用户和URL设置了ACL.我通过运行确认这一点:

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;

返回相应的HTML代码.

然而，当我将这段代码放在一个 PL/SQL 函数中时，如下所示:

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;

并从匿名 PL/SQL 块运行此代码:

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;

我收到以下错误堆栈:

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.

有什么办法可以解决这个问题吗?

我正在阅读 https://support.oracle.com/知识/Oracle%20Database%20Products/1074843_1.html我发现最接近的事情是:'4.当从 PLSQL 过程请求服务时，通过角色授予 ACL 不起作用，但是，我在设置 ACL 时没有使用角色.

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.

谢谢！

我的数据库版本:

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

我的 ACL 设置:

-- 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;

推荐答案

当事情在匿名块中起作用但在存储过程中不起作用时，通常是因为定义者的权限与调用者的权限不同.匿名块和调用者的权限过程可以使用通过角色授予的权限，但定义者的权限过程不能.

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.

尝试将 return varchar2 is 更改为 return varchar2 authid current_user is.

这篇关于ORA-24247: ACL 从 PL/SQL 函数内拒绝访问，但不是从 SQL 访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！