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;
/

4 comments:

  1. As I wrote at the outset, "The Last Dance" crystallizes this post Cold War, pre 9/11 moment in the sun for America and American culture around the world, even if we see that moment Ray Ban Outlet as pretty vapid in retrospect. I think that carries a harder degree of Yeezy Discount difficulty than y'all Coach Outlet Store give it credit for. What say you, BT?.

    Barney McGowan, Ted Grillone, 3. Frank Lancaster, Ozzie Leclair; flight 5: 1. Greg Faulkner, Ryan Ramsdell, 2. AbstractBased on the "Human in vitro dermal absorption datasets" published as supporting information to the revised Yeezy Boost 350 EFSA New Jordan Shoes 2020 Guidance on Dermal Absorption, in silico models for prediction of absorption across the skin have been evaluated. For this evaluation, a systematic literature search and review Ray Ban Glasses was Coach Outlet performed, identifying 288 publications describing mathematical models for prediction of dermal absorption. Eleven models potentially relevant to the regulatory assessment of pesticides Coach Handbags Clearance and which cover a range of approaches were selected for in depth evaluation.

    ReplyDelete