Create and Configure ACLs in Oracle database

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        true

Once 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

Create and Configure ACLs in Oracle database

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        true

Once 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