ORA-24247: network access denied by access control list (ACL)
調用 UTL_HTTP、UTL_SMTP、UTL_TCP、UTL_MAIL、UTL_INADDR 等網絡包時觸發。
11g 引入**細粒度網絡 ACL**,預設拒絕所有網絡訪問。10g 僅靠 EXECUTE 權限控制。
-- 查看現有 ACL
SELECT * FROM dba_network_acls;
-- 查看 ACL 權限條目
SELECT * FROM dba_network_acl_privileges;
-- 確認目標用戶 (大小寫敏感)
SELECT username FROM dba_users WHERE username LIKE '%ITS%';
BEGIN
-- 1. 建立 ACL 檔案
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'utl_http.xml',
description => 'HTTP Access',
principal => 'ITS', -- 授權用戶,大小寫敏感
is_grant => TRUE,
privilege => 'connect', -- 連線權限
start_date => NULL,
end_date => NULL
);
-- 2. 新增解析權限 (resolve)
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'utl_http.xml',
principal => 'ITS',
is_grant => TRUE,
privilege => 'resolve',
start_date => NULL,
end_date => NULL
);
-- 3. 指派 ACL 給目標主機/端口
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'utl_http.xml',
host => '100.1.2.1', -- IP 或域名,勿用 localhost
lower_port => 9000,
upper_port => NULL -- NULL 表示所有較高端口
);
-- 4. 可重複指派多個主機
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'utl_http.xml',
host => '10.100.49.138',
lower_port => 80,
upper_port => NULL
);
COMMIT;
END;
/
SELECT UTL_HTTP.REQUEST('https://100.1.2.1:9000/hello?wsdl') FROM dual;
若報 no listener,確認 host 參數用 **IP/域名**,不要用 localhost (資料庫非本機時會解析錯誤)。
BEGIN
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(
acl => 'utl_http.xml',
host => '100.1.2.1'
);
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'utl_http.xml');
COMMIT;
END;
/
DBA_USERS 一致DBA_NETWORK_ACL_PRIVILEGES