Tuesday, November 19, 2013

Setting up Network oracle ACLs in Oracle 11g

 Goal: how to add oracle ACL to database

with sys investigate:
 
select * from dba_network_acls;

select * from dba_network_acl_privileges;

select * from user_network_acl_privileges;


dependencies from users:

SELECT   owner,
         name,
         TYPE,
         referenced_name
  FROM   DBA_DEPENDENCIES
 WHERE   referenced_name IN ('UTL_TCP', 'UTL_SMTP', 'UTL_MAIL', 'UTL_HTTP', 'UTL_INADDR')
         AND owner NOT IN ('SYS', 'PUBLIC', 'ORDPLUGINS');


add my ACL to all of them:

DECLARE
  ACL_PATH  VARCHAR2(4000);
  CURSOR C1 IS
  SELECT distinct owner FROM DBA_DEPENDENCIES
  WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
  AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
BEGIN
  FOR R1 IN C1 LOOP
  BEGIN
  SELECT acl INTO acl_path FROM dba_network_acls
  WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL;
        IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,
                                         r1.owner,'connect') IS NULL THEN
               DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,
                                         r1.owner, TRUE, 'connect');
        END IF;
  EXCEPTION
     WHEN no_data_found THEN
       DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('myACL.xml',
                                         'ACL for network packages',
                                         r1.owner,
                                         TRUE,
                                         'connect');
       DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('myACL.xml','host_name');
END;
COMMIT;
END LOOP;
END;
/