Add a schema to the External Network Services Access Control List

From Oracle 11g network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR which can be used to access external network resources, are more restricted and secured. Oracle 11g introduced Fine-Grained Access to these packages by creating an Access Control List to use any external network resource through these packages. Before this any user who had an execute privilege on these packages was able to do anything to any network resource like web and local mail servers etc. But now a user needs a little more then just an execute privilege on the network packages.

Run the following as SYS to add a schema into the access control list

BEGIN

 dbms_network_acl_admin.add_privilege ( 
   acl         => 'netacl01.xml', 
   principal   => 'MYSCHEMA',
   is_grant    => TRUE, 
   privilege   => 'connect'
 );

 dbms_network_acl_admin.add_privilege ( 
   acl         => 'netacl01.xml', 
   principal   => 'MYSCHEMA',
   is_grant    => TRUE, 
   privilege   => 'resolve'
 );

 COMMIT;

END;

Or by doing this:

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl01.xml' ,'MYSCHEMA', TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl01.xml' ,'MYSCHEMA', TRUE, 'resolve');
commit;

To test, send a mail as that user:

alter session set current_schema=MYSCHEMA;
exec send_mail.mail('mos@domain.co.uk','mos@domain.co.uk','Testing ACL - ### SENDMAIL ###','Test');

Note: This requires the sendmail package to be setup in the schema

Drop a Schema from the ACL:

exec DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('netacl01.xml' ,'MYSCHEMA');
exec DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('netacl01.xml' ,'MYSCHEMA');
commit;

To see permissions who has permissions for ACL:

SELECT acl , principal , privilege , is_grant 
FROM DBA_NETWORK_ACL_PRIVILEGES;

Troubleshooting

ERROR at line 1:

ORA-44416: Invalid ACL: Unresolved principal ‘MIKE_R’

ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 421

ORA-06512: at line 1

This indicates that a user exists in the ACL which has been dropped from the database. Follow the Drop command above to resolve this.

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 246

ORA-06512: at "SYS.UTL_SMTP", line 115

ORA-06512: at "SYS.UTL_SMTP", line 138

The above error indicates that the schema has not been added into the ACL

Advertisements

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

%d bloggers like this: