| ORA-24247 with valid ACL when sending smtp mail [message #562386] |
Wed, 01 August 2012 03:26  |
NanteJena
Messages: 11
Registered: November 2007
Location: Jena
|
Junior Member |
|
|
I have users with the same privileges to the same ACL and some can send emails and some others cannot !
In principle I created the ACL for USER1:
BEGIN
dbms_network_acl_admin.create_acl
(acl => 'mails_senden.xml',
description => 'Mails senden ueber INTRANET Mail-Server ...',
principal => 'USER1',
is_grant => true,
privilege => 'connect');
COMMIT;
END;
/
BEGIN
dbms_network_acl_admin.assign_acl
(acl => 'mails_senden.xml',
host => 'xx.xx.x.xx',
lower_port => null,
upper_port => null);
COMMIT;
END;
/
BEGIN
dbms_network_acl_admin.add_privilege
(acl => 'mails_senden.xml',
principal => 'USER1',
is_grant => true,
privilege => 'resolve');
COMMIT;
END;
/
Then I added USER2:
BEGIN
dbms_network_acl_admin.add_privilege
(acl => 'mails_senden.xml',
principal => 'USER2',
is_grant => true,
privilege => 'connect');
COMMIT;
END;
/
BEGIN
dbms_network_acl_admin.add_privilege
(acl => 'mails_senden.xml',
principal => 'USER2',
is_grant => true,
privilege => 'resolve');
COMMIT;
END;
/
USER1 sent a mail via UTL_SMTP successful whereas USER2 got the errors:
DECLARE
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at line 34
What the matter ?
There is still the following fact:
USER1 has the role DBA, USER2 got the EXECUTE privilege to packages UTL_TCP, UTL_SMTP (GRANT from SYS).
IF the role DBA was granted to USER2 too then he couldt send emails just as well as USER1.
Is this a problem with privileges, with wich?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-24247 with valid ACL when sending smtp mail [message #562558 is a reply to message #562410] |
Thu, 02 August 2012 04:21  |
NanteJena
Messages: 11
Registered: November 2007
Location: Jena
|
Junior Member |
|
|
Hi,
the problem is solved, details see My Oracle Support Community, PL/SQL, same title.
It had nothing to do with ports (NULL is default, all ports of the host included!).
The cause was, that I specified in ACL the IP address and in PL/SQL mail/package the hostname was coded
(no chance to see this in Oracle documentation)!
Besides users with role DBA can access the network without ACL too and so the given ACL doesn't work at all!
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
