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:

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.

About these ads

2 Responses to Fine-grained access to network services

  1. dombrooks says:

    Thanks RC – it was already a link mid-article.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 75 other followers

%d bloggers like this: