Let us see how to create and configure ACLs in Oracle database.
ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “CINP01314”, line 255
ORA-06512: at line 21
We need to create and configure ACL(access control list). In order to use PL/SQL network utility packages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.
Below Steps need to follow configure :-
1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.
###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered
2. Create an access control list file :-
begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/
PL/SQL procedure successfully completed.
— Drop an access control list :-
BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => '/sys/acls/utl_smtp.xml');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
3. Assign this ACL to your SMTP network host for your email server :-
begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
commit;
end;
/
PL/SQL procedure successfully completed.
— TO drop ACL assignments :-
begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
COMMIT;
end;
/
PL/SQL procedure successfully completed.
4. Grant permission to use ACL file :-
begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;
/
PL/SQL procedure successfully completed.
— If we want to delete or revoke this permission :-
BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => '/sys/acls/utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
5. Check that for INDIA user permission has been set properly :-
SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM
user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILEGE STATUS
------------------- ----------------- --------------- ---------- ------------
10.0.06.01 25 25 connect GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED
–Status column must have value granted.
SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;
PRIVILEGE
---------------------
GRANTED
SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml INDIA connect trueOnce you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL’s output of above query will be like below :-
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
--------------------- ----------- ----------- -------- -------- -------
/sys/acls/utl_mail.xml INDIA connect true
/sys/acls/utl_smtp.xml INDIA connect true
/sys/acls/utl_tcp.xml INDIA connect true
TO enable this ACL permission for a different USER other than above user(INDIA) :-
Connect with TEST user and run below mentioned :-
begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'TEST',
is_grant => FALSE,
privilege => 'connect');
commit;
end;
/
PL/SQL procedure successfully completed.
begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01',
lower_port => 25);
end;
/
PL/SQL procedure successfully completed.
SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
----------------- ------------- ----------- ---------- -------- ----------
/sys/acls/utl_smtp.xml INDIA connect true
/sys/acls/utl_smtp.xml TEST connect false
— This query has to be run with TEST user and Status column should have value “GRANTED“
SQL> SELECT host, lower_port, upper_port, privilege, status FROM
user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILEGE STATUS
-------------------- ---------- ----------- --------- -------
10.0.06.01 25 25 connect GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED
Now check your procedure to send mail through test user , it will work.
After this configuration is completed. we can send a mail with and without attachment. Please check the link for further.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
