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