Oracle Database 11g中的访问控制列表(ACL)拒绝了网络访问 [英] Network access denied by access control list (ACL) in Oracle Database 11g
问题描述
最近我们已经从Oracle 10g切换到11g，直到现在我才注意到我的邮件发送功能不起作用，现在我得到一个错误:
Recently we have switched from Oracle 10g to 11g, and only now I noticed that my mailing function does not work, I now get an error:
ORA-24247: network access denied by access control list (ACL)
因此，我做了一些谷歌搜索，并发现Oracle 11g中的一项新功能现在限制了用户使用某些软件包，包括utl_smtp.因为我正在寻找一种快速的解决方案，所以我没有阅读Oracle文档，而是去寻找更简单的解决方案，并找到了本教程:
So I did a bit of googling and was able to figure out that a new feature in Oracle 11g is now restricting users from using certain packages including utl_smtp. Because I am looking for a quick solution I did not read Oracle documentation, but instead I went looking for easier solutions and came across this tutorial:
https://www.pythian.com/blog/setting-up-network-acls-in-oracle-11g-for-dummies/
我稍微弄乱了它，但是因为我不知道更好，所以我认为我添加了两个单独的配置.xml文件.因此，我的问题的第一部分是-如何删除它?
I messed around with it a little bit, but because I did not know any better I think I added two seperate configuration .xml files. So first part of my question is - HOW DO I REMOVE IT?
第二个问题是:
在向用户添加了一些授权后，我尝试进行测试以查看其是否有效，但我很快意识到它没有作用:
After adding some grants to my user I try to test to see if it worked, but I soon realised it did not:
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('netacl.xml', 'TEST1', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual;
返回:
PRIVILE
-------
DENIED
为什么?(已排序)
问题的第三部分-阅读被拒绝后，我尝试将其修复为:
Third part of the question - after reading it was denied I try to fix it like:
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'TEST1', TRUE, 'connect');
END;
但这给了我一个错误:
Ora19279 - XQuery dynamic type mismatch.....(more text meaning nothing to me).
为什么?(我想出了，如果您允许同一用户第二次使用相同的权限，就会发生错误)
WHY?(I FIGURED OUT, THAT ERROR HAPPENS WHEN YOU GRANT SAME PERMISSION TO SAME USER SECOND TIME)
更新
我遵循了下面kevinsky的建议答案，并且在此过程中学到了很多东西，但是我仍然有一个问题.我仍然收到ORA-24247:访问控制列表(ACL)拒绝了网络访问.因为我按照建议执行了其他所有操作，所以我开始认为问题可能是我添加的第一个配置文件，但现在无法删除，因为我不记得它的名称了.如果有人可以帮助我，我将非常感激.
I have followed the suggested answer by kevinsky below and have learned quite a bit in the process, however I still have a problem. I still get the ORA-24247: network access denied by access control list (ACL). Because I did everything else as suggested, I am starting to think that the problem could be that first configuration file which I added, but cannot remove now because I cannot remember its name. If anyone can help me I would appreciate that very much.
结果(我正在尝试一些其他方法)
RESULTS OF(I was trying out a few different things so):
select * from dba_network_acls;
返回
* | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...
myservername.com | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...
myDBName | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...
mailServerDomainName | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...
mailserver.myDomain.local | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...
推荐答案
我进行了此升级，这需要花费大量时间.对于版本12，所有操作都必须进行不同的重做.每个过程调用都必须具有一个提交.一般的想法是，您创建访问权限，添加详细信息，授予特权.您必须知道:
I did this upgrade and it was hours of work. It all has to be redone differently for version 12. Every procedure call must have a commit. The general idea is that you create an access,add details, grant privileges. You must know:
- 您的邮件服务器名称和端口
- 是否需要用户名和密码才能访问它(可能不需要)
- 将要呼叫该邮件包的用户，如果他们也拥有该邮件包，则更加容易
/*create the access permission to connect*/
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'utl_smtp.xml',
description => 'access to smtp email',
principal => 'YourUser',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
--add the privilege to resolve names
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'YourUser',
is_grant => TRUE,
privilege => 'resolve');
COMMIT;
END;
--assign your mailserver
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'utl_smtp.xml',
host => 'mailserver.YourDomain.local',
lower_port => 25,
upper_port => NULL);
commit;
END;
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'utl_smtp.xml',
host => 'YourDBName',
lower_port => 25,
upper_port => NULL);
COMMIT;
END;
--more housekeeping
alter system set smtp_out_server = 'mailserver.YourDomain.local:25' scope = both;
--make sure the user can access the smtp packages
GRANT EXECUTE ON UTL_TCP TO YourUser;
GRANT EXECUTE ON UTL_SMTP TO YourUser;
GRANT EXECUTE ON UTL_MAIL TO YourUser;
--check your work
select * from dba_network_acls;
--verify permissions for your user
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(
'utl_smtp.xml', 'YourUser', 'resolve'),
1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
FROM DUAL;
--if you have created access permissions you wish to delete
--using the information from the select use this to delete what you don't want
exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL ('acl_utl_smtp.xml');
--for more troubleshooting try this barebones mail procedure, run with your user. Copied from [here][1]
DECLARE
v_From VARCHAR2(80) := 'oracle@mycompany.com';
v_Recipient VARCHAR2(80) := 'test@mycompany.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail', TRUE);
END;
这篇关于Oracle Database 11g中的访问控制列表(ACL)拒绝了网络访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
