ORA-24247: 存取控制清單 (ACL) 拒絕網路存取－DanBrother的部落格

Error Message:
ORA-24247: 存取控制清單 (ACL) 拒絕網路存取

Solution:

Package:
DBMS_NETWORK_ACL_ADMIN => provides the interface to manage the network Access Control List (ACL).

begin
   -- Creating ACL
   dbms_network_acl_admin.create_acl(
   acl => 'utl_mail.xml',
   description => 'Allow mail to be send',
   principal => 'SCOTT',
   is_grant => TRUE,
   privilege => 'connect'
   );

   -- Adding Privilege to ACL
   dbms_network_acl_admin.add_privilege(
   acl => 'utl_mail.xml',
   principal => 'SCOTT',
   is_grant => TRUE,
   privilege => 'resolve'
   );

   -- Assigning Host to ACL
   dbms_network_acl_admin.assign_acl(
   acl => 'utl_mail.xml',
   host => 'mail.mydomain.com',
   lower_port => 25,
   upper_port => 25
   );
   commit;
end;
/

-- To Add Second User (BOB) to the ACL
begin
   -- Adding Connect Privilege to BOB
   dbms_network_acl_admin.add_privilege(
   acl => 'utl_mail.xml',
   principal => 'BOB',
   is_grant => TRUE,
   privilege => 'connect'
   );
   -- Adding Resolve Privilege to BOB
   dbms_network_acl_admin.add_privilege(
   acl => 'utl_mail.xml',
   principal => 'BOB',
   is_grant => TRUE,
   privilege => 'resolve'
   );
  commit;
end;
/

col host format a30
col acl format a30
col aclid format a32
select * from dba_network_acls;
>>
HOST LOWER_PORT UPPER_PORT ACL ACLID
--------------------------    ------------------     -----------------     -----------------------------  ----------
mail.mydomain.com         25 25 /sys/acls/utl_mail.xml xxxx

select acl,principal,privilege,is_grant,invert from dba_network_acl_privileges;
>>
ACL PRINCIPAL PRIVILEGE IS_GRANT INVERT
--------------------------    ------------------     -----------------     ------------------  ----------
/sys/acls/utl_mail.xml SCOTT connect true false
/sys/acls/utl_mail.xml SCOTT resolve true false
/sys/acls/utl_mail.xml BOB connect true false
/sys/acls/utl_mail.xml BOB resolve true false

alter system set smtp_out_server='mail.mydomain.com' scope=both sid='*';
col name format a30
col type format a20
sho parameter smtp
NAME TYPE VALUE
------------------------------------ -------------------- ------------------------------
smtp_out_server string mail.mydomain.com

Check whether mail server with port 25 is available through "telnet" command:
telnet mail.mydomain.com 25

Check Privileges:
var a number
var b number
var c number
var d number

exec :a:=dbms_network_acl_admin.check_privilege(acl => '/sys/acls/utl_mail.xml',user => 'SCOTT', privilege => 'connect');
exec :b:=dbms_network_acl_admin.check_privilege(acl => '/sys/acls/utl_mail.xml',user => 'SCOTT', privilege => 'resolve');
exec :c:=dbms_network_acl_admin.check_privilege(acl => '/sys/acls/utl_mail.xml',user => 'BOB', privilege => 'connect');
exec :d:=dbms_network_acl_admin.check_privilege(acl => '/sys/acls/utl_mail.xml',user => 'BOB', privilege => 'resolve');

print
a
------
1

b
------
1