Fine-grained access to network services

(or my database emails have stopped working)

Wotcha.

Here’s the background:
We’re in the latter stages of preparing for a production upgrade to 11g (2 weeks to go).

Best practice would say that we should have a stable database by now and very high confidence factor, but the truth is that the production upgrade coincides with a new release of functionality and every now and then it is “discovered” that some core functionality is not working (For example 1200 lines of code to change from using the function XSLPROCESSOR.VALUEOF which has a 4k node size limit to use the procedure which doesn’t. Or actually creating a wrapper for the function signature that calls the procedure, etc, etc)

Back to the matter at hand:

Emails failing with

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 246
ORA-06512: at "SYS.UTL_SMTP", line 115


This is part of the nattily named “Fine-grained Access to External Network Services“, a new feature in 11g. Essentially, previously allowed access to services over the network using packages like UTL_SMTP, UTL_HTTP, UTL_TCP, etc.

Have some links:

• Tim @ Oracle-Base
• Marco Gralike
• Metalink Note 557070.1

So, the problem itself was pretty easy to distill:

declare
l_email clob := 'test email';
begin
email_pkg.send_email(
i_sender => 'me@myemail.com',
i_recipients => 'me@myemail.com',
i_subject => 'test',
i_mime_type => 'text/html',
i_priority => NULL,
i_message => l_email);
end;


which gave, as mentioned:


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 246
ORA-06512: at "SYS.UTL_SMTP", line 115


Solution is to create an ACL:


BEGIN
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
acl => 'my_mailserver_acl.xml');
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Error dropping ACL '||SQLERRM);
END;
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'my_mailserver_acl.xml',
description => 'Connect Access To Mail Server',
principal => 'UTILITY',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'my_mailserver_acl.xml',
host => 'my.mailserver.com',
lower_port => 25);
COMMIT;
END;
/


This failed for me initially because:


function-based index XDB.XDB$ACL_XIDX is disabled


After rebuild of index (note to self – don’t be lazy and use Toad because it expands the syntax to include defaults that aren’t always valid), email test works fine.

Job done, move along – nothing to see here.