I am getting the dreaded ORA-24247 when I try to execute UTL_MAIL.SEND as a non-DBA user.
The non-DBA users have been granted execute on UTL_MAIL.
The DBA users work fine.
If I grant DBA to a non-DBA account, and try to send an email it works.
The script I ran to create the ACLs is the following:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'UTL_MAIL.xml',
description => 'Granting privs to required users for UTL_MAIL.xml',
principal => 'SYS',
is_grant => TRUE,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => 'UTL_MAIL.xml',
principal => 'SYS',
is_grant => TRUE,
privilege => 'resolve');
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'UTL_MAIL.xml',
host => '172.xx.xx.xxx',
lower_port => '25',
upper_port => null);
COMMIT;
END;
/
- A DBA
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => 'UTL_MAIL.xml',
principal => 'NEWEIS',
is_grant => TRUE,
privilege => 'connect');
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => 'UTL_MAIL.xml',
principal => 'NEWEIS',
is_grant => TRUE,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'UTL_MAIL.xml',
host => '172.xx.xx.xxx');
END;
/
commit;
/
- A non-DBA
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => '/sys/acls/utl_mail.xml',
principal => 'SOPRAS',
is_grant => TRUE,
privilege => 'connect');
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => '/sys/acls/utl_mail.xml',
principal => 'SOPRAS',
is_grant => TRUE,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => '/sys/acls/utl_mail.xml',
host => '172.xx.xx.xxx');
END;
/
commit;
/
Yeah, these errors can be a pain to debug; it's tough for us to help without access to troubleshoot exactly where the issue is.
So I'm not sure what the problem is; check DBA_HOST_ACES to verify that the user does have the privileges you expect.
Also, the ACL for the non-DBA is subtly different:
/sys/acls/utl_mail.xml -> UTL_MAIL.xml
Have you tried changing these to match?