General Checking
select * from dba_users where username ='SCHEMA_APP1';
SELECT * FROM dba_network_acl_privileges where principal='SCHEMA_APP1';
select * from dba_network_acls
select * from dba_network_acl_privileges
--- execute for granting acl privileges --
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => '/sys/acls/system_email_perms.xml',
principal => 'FPA_PS',
is_grant => TRUE,
privilege => 'connect',
position => NULL,
start_date => NULL,
end_date => NULL);
COMMIT;
Sample Issue:
INC0719511 : User is not able to debug PL/SQL packages in SMA staging db. Can use debug before the DB upgrade to 12c.
User: SCHEMA_APP1
Error:
Connecting to the database SCHEMA_APP1.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.43.78.72', '60235' )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database SCHEMA_APP1.
Resolution:
Found a similar case reported as referenced here :"https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9528615800346614204"
and we've tried to create the ACL and grant
grant execute on DBMS_DEBUG_JDWP to SCHEMA_APP1;
begin
dbms_network_acl_admin.append_host_ace
(host=>'10.18.191.38',
ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
principal_name=>'SCHEMA_APP1',
principal_type=>sys.XS_ACL.PTYPE_DB) );
end;
/
Sample Issue:
Reason: Sent mail issue on UTL_MAIL
ERROR at line 1:
ORA-20001: SectionName:SendEmail Description:-24247-ORA-24247: network access
denied by access control list (ACL)
ORA-06512: at "SCHEMA_APP1.PKP_SABRIX_TAX_REPORT", line 139
ORA-06512: at line 1
From sysdba,
grant execute on UTL_MAIL to SCHEMA_DATA1;
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'my_email_permissions.xml',
description => 'Enables email',
principal => 'SCHEMA_DATA1',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'my_email_permissions.xml',
host => 'mh.ok.mailhost.seagate.com',
lower_port => 25,
upper_port => 25 );
commit;
END;
/
-- Testing --
begin
UTL_MAIL.send( sender => 'my_email1@email.com'
,recipients => 'recipient_email@email.com '
,subject=>'20190201 - utl_mail test'
,message=>'Hello world !');
end;
/
SQL> show parameter smtp_out_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server string mh.ok.mailhost.seagate.com
-----------------------
Note that 12c ACL is using different format to add ACL
begin
dbms_network_acl_admin.append_host_ace
(host=>'mh.ok.mailhost.seagate.com',
ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('connect', 'resolve') ,
principal_name=>'SCHEMA_APP1',
principal_type=>sys.XS_ACL.PTYPE_DB) );
end;
/