Fine-Grained Access to External Network Services

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.

DECLARE
acl_path     VARCHAR2 (4000);
v_hostname   VARCHAR2 (200)  := 'xxx.leavemealone.local';
v_user       VARCHAR2 (30)   := 'APEX_050100';
BEGIN
SELECT acl
INTO acl_path
FROM dba_network_acls
WHERE HOST = v_hostname AND lower_port IS NULL AND upper_port IS NULL;

IF dbms_network_acl_admin.check_privilege (acl_path, v_user, 'connect') IS NULL
THEN
dbms_network_acl_admin.add_privilege (acl_path, v_user, TRUE, 'connect');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
dbms_network_acl_admin.create_acl ('send_mail.xml', 'send_mail ACL', v_user, TRUE, 'connect');
dbms_network_acl_admin.assign_acl ('send_mail.xml', v_hostname, 25, 25);
END;
/
COMMIT;

New way of adding ACL in Oracle 12c

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'xxx.leavemealone.local', 
    lower_port => 25,
    upper_port => 25,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'APEX_050100',
                              principal_type => xs_acl.ptype_db)); 
END;
/
COMMIT;

Check ACL and privileges under SYS

SELECT * FROM dba_network_acls;

SELECT * FROM dba_network_acl_privileges order by principal;

Add missing ACL after upgrading APEX

BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege ('/sys/acls/power_users.xml',
'APEX_050100',
TRUE,
'connect');
DBMS_NETWORK_ACL_ADMIN.add_privilege ('/sys/acls/send_mail.xml',
'APEX_050100',
TRUE,
'connect');
END;

Duplicate ACL for one schema. In this example, we use APEX_200200. Copy the creation script output and run it on the destination database.

SELECT    'BEGIN DBMS_NETWORK_ACL_ADMIN.append_host_ace (
            HOST         => '''
       || dna.HOST
       || ''',
            lower_port   => '
       || dna.lower_port
       || ',
            upper_port   => '
       || dna.upper_port
       || ',
            ace          =>
                xs$ace_type (privilege_list   => xs$name_list (''connect''),
                             principal_name   => '''
       || dnap.principal
       || ''',
                             principal_type   => xs_acl.ptype_db)); END;
                             /' v_sql
  FROM dba_network_acls dna, dba_network_acl_privileges dnap
 WHERE dna.aclid = dnap.aclid AND dnap.principal = 'APEX_200200';