Apex调用Jasper Report遇ACL权限错误，已创建ACL仍无法解决

一、排查并修正ACL配置细节 #

Oracle 11g的ACL权限配置需要精准绑定用户、主机和端口，执行以下SQL确认并修复：

-- 1. 创建或更新ACL（针对Apex解析用户）
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl          => 'jasper_server_acl.xml',
    description  => '授权访问Jasper Server',
    principal    => 'APEX_210100', -- 替换为你的Apex实际解析用户
    is_grant     => TRUE,
    privilege    => 'connect'
  );
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- 如果ACL已存在，直接更新权限
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
      acl         => 'jasper_server_acl.xml',
      principal   => 'APEX_210100',
      is_grant    => TRUE,
      privilege   => 'connect'
    );
    COMMIT;
END;
/

-- 2. 将ACL绑定到Jasper Server的主机和端口
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl         => 'jasper_server_acl.xml',
    host        => 'localhost', -- 替换为Jasper Server实际主机名/IP
    lower_port  => 5080, -- Jasperserver监听端口
    upper_port  => 5080
  );
  COMMIT;
END;
/

-- 3. 验证权限是否生效
SELECT host, lower_port, upper_port, acl
FROM dba_network_acls
WHERE host = 'localhost';

SELECT principal, privilege, is_grant
FROM dba_network_acl_privileges
WHERE acl LIKE '%jasper_server_acl.xml%';

二、修正Apex代码中的核心问题 #

你的代码存在3个关键错误，导致请求失败或权限验证失效，修正后的代码如下：

DECLARE
  v_blob                 BLOB;
  v_file_type            VARCHAR2 (25) :='pdf';
  v_file_name            VARCHAR2 (25) := 'employee.'||v_file_type;
  v_vcContentDisposition VARCHAR2 (25)  := 'inline';
  v_DEPTNO               NUMBER := :P10_DEPTNO;
  v_hostname   VARCHAR2(30) := 'localhost';
  v_port       VARCHAR2(5)  := '5080'; -- 改为VARCHAR2避免数字转字符串异常
  v_username   VARCHAR2(50) := 'anonymousUser';
  v_password   VARCHAR2(50) := 'anonymousUser';
  v_login_url  VARCHAR2(200) := 'http://' || v_hostname || ':' || v_port || '/jasperserver/rest/login';
  v_report_url VARCHAR2(200) := 'http://' || v_hostname || ':' || v_port || '/jasperserver/rest_v2/reports/reports/' || v_file_name;
BEGIN
  -- 1. Jasper登录接口要求POST方法，原GET请求会导致登录失败
  apex_web_service.make_rest_request(
    p_url => v_login_url,
    p_http_method => 'POST',
    p_parm_name => apex_util.string_to_table('j_username;j_password',';'),
    p_parm_value => apex_util.string_to_table(v_username || ';' || v_password,';')
  );
  
  -- 2. 携带登录会话Cookie请求报表，否则会被判定为未授权
  v_blob := apex_web_service.make_rest_request_b(
    p_url => v_report_url,
    p_http_method => 'GET',
    p_parm_name => apex_util.string_to_table('p_dept',';'),
    p_parm_value => apex_util.string_to_table(v_DEPTNO || '',';'), -- 确保参数为字符串类型
    p_headers => apex_util.string_to_table('Cookie:' || apex_web_service.g_cookies(1).name || '=' || apex_web_service.g_cookies(1).value)
  );
  
  -- 3. 输出PDF内容，修正MIME类型为application/pdf
  OWA_UTIL.MIME_HEADER( 'application/pdf', FALSE );
  HTP.p('Content-Length: ' || DBMS_LOB.GETLENGTH(v_blob));
  HTP.p('Content-Disposition: ' || v_vcContentDisposition ||'; filename="' || v_file_name || '"');
  OWA_UTIL.http_header_close;
  WPG_DOCLOAD.DOWNLOAD_FILE(v_blob);
  APEX_APPLICATION.STOP_APEX_ENGINE;
END;

代码修正说明： #

• v_port改为VARCHAR2类型，避免数字转字符串时的隐式转换错误
• Jasper Server的REST登录接口仅支持POST方法，原GET请求无法完成身份验证
• 报表请求必须携带登录后的会话Cookie，否则会被Jasper Server拒绝访问
• 调整MIME类型为application/pdf，确保浏览器正确解析内容

三、额外排查点 #

1. 确认Jasper Server的anonymousUser账号拥有目标报表/reports/reports/employee.pdf的访问权限
2. 如果Oracle服务器与Jasper Server不在同一机器，将代码中的localhost替换为Jasper Server的实际IP或主机名
3. 用UTL_HTTP测试基础连接，排查网络连通性：

DECLARE
  req UTL_HTTP.REQ;
  resp UTL_HTTP.RESP;
BEGIN
  req := UTL_HTTP.BEGIN_REQUEST('http://localhost:5080/jasperserver/rest/login');
  UTL_HTTP.SET_HEADER(req, 'Content-Type', 'application/x-www-form-urlencoded');
  UTL_HTTP.WRITE_TEXT(req, 'j_username=anonymousUser&j_password=anonymousUser');
  resp := UTL_HTTP.GET_RESPONSE(req);
  UTL_HTTP.END_RESPONSE(resp);
  DBMS_OUTPUT.PUT_LINE('连接成功');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);
END;
/

内容的提问来源于stack exchange，提问作者nasim