Fine-grained access to network services

(or my database emails have stopped working)


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:

l_email clob := 'test email';
i_sender => '',
i_recipients => '',
i_subject => 'test',
i_mime_type => 'text/html',
i_priority => NULL,
i_message => l_email);

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:

acl => 'my_mailserver_acl.xml');
WHEN others THEN
acl => 'my_mailserver_acl.xml',
description => 'Connect Access To Mail Server',
principal => 'UTILITY',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
acl => 'my_mailserver_acl.xml',
host => '',
lower_port => 25);

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.


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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: