ORA-24247: network access denied by access control list (ACL) 错误处理
及DBMS_NETWORK_ACL_ADMIN用法汇总
通过oracle的存储过程发邮件，出现问题，具体过程如下：
发邮件的存储过程PROC_SENDMAIL_SIMPLE在A用户，而B用户要调用A用的PROC_SENDMAIL_SIMPLE来发邮件。
其中，A用户已经把PROC_SENDMAIL_SIMPLE的执行权限给了B用户
grant execute on PROC_SENDMAIL_SIMPLE to B;
但是在B用户的存储过程中调用PROC_SENDMAIL_SIMPLE依然报错
ORA-24247: 网络访问被访问控制列表 (ACL) 拒绝
ORA-24247: network access denied by access control list (ACL)
发生这个错误是因为网络访问控制列表管理着用户访问网络的权限。
========
解决办法：
========
拥有DBA权限的用户执行下面的SQL，分3部分
BEGIN
--1.创建访问控制列表sendmail.xml，sendmail.xml控制列表拥有connect权限，并把这个权限给了B用户，
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl=> 'sendmail.xml', -- ACL的名字，自己定义
description => 'sendmail ACL', -- ACL的描述
principal => 'B', -- 这里是用户名，大写，表示把这个ACL的权限赋给B用户
is_grant => true, --true：授权 ;false：禁止
privilege => 'connect'); --授予或者禁止的网络权限
--2.为sendmail.xml控制列表添加resolve权限，且赋给B用户
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl=> 'sendmail.xml',
principal => 'B',
is_grant => true,
privilege => 'resolve');
--3.为控制列表ACL sendmail.xml分配可以connect和resolve的host
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'sendmail.xml',
host => 'smtp.163.com'); --smtp.163.com是邮箱服务器主机名
END;
/
COMMIT;
再次在用户B调用A的PROC_SENDMAIL_SIMPLE发邮件过程，成功发送邮件。
======================联想到其他情况======================
情况1：同一个ACL给多个用户使用
用户B调用A的发邮件存储过程PROC_SENDMAIL_SIMPLE，那么C用户很可能也要这么做。
这时，不必创建一个新的ACL，用原有的ACL sendmail.xml即可，也就是把sendmail.xml给用户C使用。
这样C用户自然可以访问网络发送邮件。
--给C用户resolve权限
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'sendmail.xml',
principal => 'C',
is_grant => true,
privilege => 'resolve');
--给C用户 conenct权限
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'sendmail.xml',
principal => 'C',
is_grant => true,
privilege => 'connect');
END;
/
COMMIT;
情况2：取消给ACL sendmail.xml 指派的主机smtp.163.com ，也就是所有使用sendmail.xml 的用户都不能connect和resolve主机smtp.163.com
<1>查看一下
select * from dba_network_acls;
HOST LOWER_PORT UPPER_PORT ACL ACLID
----------------- ------------------ ----------------- --------------------- --------------------------
smtp.163.com /sys/acls/sendmail.xml D07B6F4707E7EFFDE040007F01005C7F
<2>收回sendmail.xml控制列表中访问smtp.163.com的权限
BEGIN
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'smtp.163.com');
END;
/
COMMIT;
<3>
select * from dba_network_acls;
空
不过这时ACL sendmail.xml依然存在，只不过sendmail.xml中没有任何主机信息
<4>那么怎么让sendmail.xml重新能访问smtp.163.com呢？
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'sendmail.xml',
host => 'smtp.163.com');
END;
/
COMMIT;
<5>再次看,sendmail.xml中含有主机smtp.163.com了
select * from dba_network_acls;
HOST LOWER_PORT UPPER_PORT ACL ACLID
-------------------- ---------- ---------- ------------------------------ --------------------------------
smtp.163.com /sys/acls/sendmail.xml D07B6F4707xFFDExx007F01005C7F
情况3：取消B用户使用sendmail.xml ACL，B用户不能访问smtp.163.com 主机了
BEGIN
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
acl => 'sendmail.xml',
principal => 'B')
END;
=========================================================================
================DBMS_NETWORK_ACL_ADMIN知识汇总==================
=========================================================================
说了这么多，其实都是对DBMS_NETWORK_ACL_ADMIN过程的使用。
下面是DBMS_NETWORK_ACL_ADMIN的相关只是汇总。
1.创建ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl IN VARCHAR2,
description IN VARCHAR2,
principal IN VARCHAR2,
is_grant IN BOOLEAN,
privilege IN VARCHAR2,
start_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
end_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL );
例子：
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => '',
description => 'WWW ACL',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect');
END;
/
COMMIT;
2.为ACL添加权限
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl IN VARCHAR2,
principal IN VARCHAR2,
is_grant IN BOOLEAN,
privilege IN VARCHAR2,
position IN PLS_INTEGER DEFAULT NULL,
start_date IN TIMESTAMP WITH TIMESTAMP DEFAULT NULL,
end_date IN TIMESTAMP WITH TIMESTAMP DEFAULT NULL );
例子：
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => '',
principal => 'SCOTT',
is_grant => true,
privilege => 'resolve');
END;
/
COMMIT;
3.指派ACL可以访问的host
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl IN VARCHAR2,
host IN VARCHAR2,
lower_port IN PLS_INTEGER DEFAULT NULL,
upper_port IN PLS_INTEGER DEFAULT NULL);
注意：host这个参数可以写作
一个网址：
也可以是一个网段：*.us.oracle.com或者*.oracle.com或者*.com
当然也可以是所有host：*
例子：
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'us-oracle-com-permissions.xml',
host => '*.us.oracle.com',
lower_port => 80);
END;
4.检测用户是否拥有某个ACL中的某个权限
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE (
acl IN VARCHAR2,
user IN VARCHAR2,
privilege IN VARCHAR2)
RETURN NUMBER;
Returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.
例子：
如scott拥有sendmail.xml中的resolve权限
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(
'sendmail.xml', 'SCOTT', 'resolve'),
1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
FROM DUAL;
PRIVILE
-------
GRANTED
5.删除acl中的connect或者resolve权限
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE (
acl IN VARCHAR2,
principal IN VARCHAR2,
is_grant IN BOOLEAN DEFAULT NULL,
privilege IN VARCHAR2 DEFAULT NULL);
例子：
BEGIN
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
acl => 'us-oracle-com-permissions.xml',
principal => 'ST_USERS')
END;
6.删除ACL
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
acl IN VARCHAR2);
例子：
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
acl => 'us-oracle-com-permissions.xml');
END;
7.取消ACL已分配的host
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (
acl IN VARCHAR2 DEFAULT NULL,
host IN VARCHAR2 DEFAULT NULL,
lower_port IN PLS_INTEGER DEFAULT NULL,
upper_port IN PLS_INTEGER DEFAULT NULL);
例子：
BEGIN
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(
host => '*.us.oracle.com',
lower_port => 80);
END;
8.查看语句
--ACL的信息，包括host，ACL名字等。
select * from dba_network_acls;
--各用户对应的ACL，用户拥有的权限
select acl,principal,privilege,is_grant,to_char(start_date, 'dd-mon-yyyy') as start_date,to_char(end_date, 'dd-mon-yyyy') as end_date from dba_network_acl_privileges;
参考
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm
