Oracle ACL Configuration on 11g for using Network packages

In Oracle 11g network packages/system packages like UTL_MAIL,UTL_SMTP,UTL_TCP  are restricted from a normal user by using Access Control List (ACL). Oracle has introduced Fine-grained Access to use External Network Services.

Simply giving Execute privilege to these packages may not be essential to use these packages.

We will need to create the below Access Control List to allow use these packages to a normal user.

Below is the procedure  to create an access control list (ACL) with an initial privilege setting. 

An ACL must have at least one privilege setting. The ACL has no access control effect unless it is assigned to network target.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'SCOTT.xml',
        description  => 'ACL for users to send mail.',
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'connect',
        start_date   => null,
        end_date     => null
    );
END;
/

Use below to add a privilege to grant or deny the network access to the user. The access control entry (ACE) will be created if it does not exist.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'SCOTT.xml',
        principal   => 'SCOTT',
        is_grant    =>  TRUE,
        privilege   => 'connect');
END;
/

Use Below to assign an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
     acl         => 'SCOTT.xml',
     host        => 'Mail Server name',
     lower_port => 25);
END;
/
commit;

dont forget to give commit at end of the session to make these changes permanent.

The below procedure deletes a privilege in an access control list.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege (
    acl         => 'SCOTT.xml',
    principal   => 'MN',
    is_grant    => TRUE,
    privilege   => 'connect');
  COMMIT;
END;
/

Execute privilege is must after running the above procedures.

select grantee , table_name , privilege from dba_tab_privs where table_name = 'UTL_MAIL' and   grantee = 'PUBLIC';

GRANTEE    TABLE_NAME   PRIVILEGE                  
---------- ----------- -----------------
PUBLIC     UTL_MAIL     EXECUTE              

select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL                     HOST         LOWER_PORT  UPPER_PORT                           ---------------------------------------------------------
/sys/acls/SCOTT.xml mailservername      25         25
/sys/acls/SCOTT.xml Mailservername
/sys/acls/SCOTT.xml mailserver.in.com

select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES; 

ACL                  PRINCIPAL    PRIVILE   IS_GR
-----------------   ------------ --------- ---------------
/sys/acls/SCOTT.xml SCOTT          connect  true

SELECT HOST, LOWER_PORT, UPPER_PORT, ACL,
   DECODE(
     DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid,  'SCOTT', 'connect'),
     1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE
FROM DBA_NETWORK_ACLS
WHERE host IN
  (SELECT * FROM      TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('msxsmtp.server.bosch.com')))
 ORDER BY
  DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) DESC, LOWER_PORT, UPPER_PORT;

HOST                 LOWER_PORT  UPPER_PORT  ACL           PRIVILE

-------------------- ---------- ------------------------------
smtp.server.in.com     25         25        /sys/acls/SCOTT.xml GRANTED
smtp.server.in.com                          /sys/acls/SCOTT.xml
GRANTED

For adding additional users to the ACL use below

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'SCOTT.xml',
        principal   => 'MN',
        is_grant    =>  TRUE,
        privilege   => 'connect');
END;
/


BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
     acl         => 'SCOTT.xml',
     host        => 'Mail Server name',
     lower_port => 25);
END;
/
commit;

Reference :


http://oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html

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

Regards,

Navaneeth