11g Oracle Acl Rules to allow web or email calls out (utl_http, utl_smtp, utl_mail)

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm

<myupperaccountname> = user name in upper case
<mydomain> = domain you want to access in lower case
<mypartdomain> = *.your domain name you want to access
<myport> = port number
port ranges
<mylowport> = port number
<myhighport> = port number

 

Using dbconsole or grid to manage acl’s

image

 

image

 

image

 

-- Check users writes to a specific location
SELECT host, lower_port, upper_port, acl,
     DECODE(
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, '<myupperaccountname>', 'connect'),
            1, 'GRANTED', 0, 'DENIED', null) privilege
     FROM dba_network_acls
    WHERE host IN
      (SELECT * FROM
         TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('<mydomain>')));


-- create the acl
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'base_acl.xml',
    description  => 'Configure the base ACL functionality for all access',
    principal    => '<myupperaccountname>',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/
--assign a network to the acl
BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'base_acl.xml',
    host        => '<mypartdomain>',
    lower_port  => <myport>,
    upper_port  => null);

  COMMIT;
END;
/


-- Unassign an acl
BEGIN
  DBMS_NETWORK_ACL_ADMIN.unassign_acl (
    acl         => 'base_acl.xml',
    host        => '<mypartdomain>',
    lower_port  => <myport>,
    upper_port  => NULL);
  COMMIT;
END;
/
-- Assign a range
BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'base_acl.xml',
    host        => '<mypartdomain>',
    lower_port  => <mylowport>,
    upper_port  => <myhighport>);
  COMMIT;
END;
/

-- check assignment as app user
/*
SELECT host, lower_port, upper_port, acl
FROM   dba_network_acls;
SELECT *
FROM   user_network_acl_privileges;
SELECT *
FROM   TABLE(DBMS_NETWORK_ACL_UTILITY.domains('<mydomain>'));
*/

 

Example 2:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'www_acl.xml',
    description  => 'Configure the web access acl',
    principal    => 'SYSDOCS',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'www_acl.xml',
    host        => '*.snapdedo.com',
    lower_port  => 80,
    upper_port  => 80);

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'www_acl.xml',
    host        => '*.google.com',
    lower_port  => 80,
    upper_port  => 80);

  COMMIT;
END;
/