Grant ACL to APPS account Oracle 11g
1. Check if view dba_network_acls has any row. If yes, run below script to add privilege to APPS account:
DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = '<SERVER NAME>' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'APPS','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'resolve');
END IF;
EXCEPTION
WHEN no_data_found THEN
-- below lines will create a ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','ACL description', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('apps.xml','APPS', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','<SERVER NAME>');
END;
/
SQL> commit;
SQL> select * FROM dba_network_acls;
SQL> select * from dba_network_acl_privileges;
Below two queries should return 1:
SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','connect') from dual;
SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','resolve') from dual;
NOTE: If view dba_network_acls has no data. You will need to create ACL for the user:
SQL> show user
USER is "SYS"
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','APPS acess for UTL', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apps.xml', principal => 'APPS',is_grant => true, privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','<SERVER NAME>');
END;
/
Sometimes, the domain name makes difference. It does not hurt to add full name to the access list:
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','SERVER_NAME.domain.com');
END;
/
SQL> commit;
NOTE: If things does not work, you can start over by dropping entries in dba_network_acls.
SQL> begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('apps.xml');
commit;
end;
/
Now dba_network_acls, dba_network_acl_privileges, net$_acl should have nothing on apps.xml and everything no ACL is cleaned up.
SQL> select * from dba_network_acls;
SQL> select * from dba_network_acl_privileges;
SQL> select * from net$_acl;
