Oracle 11g: Access Control List and ORA-24247
From 11g Oracle offers more fine-grained access control for users to access external network resources With ACL’s.
Prior 11g:
The packages UTL_MAIL, UTL_SMTP, UTL_HTTP, UTL_TCP etc. allow communication beyond the database server to the outside world, but when granted access, all hosts can be accessed.
This can be interpreted as a security flaw as no login is required when using UTL_TCP.
For example. DBA’s are advised to revoke the execute privileges from public on these kind of packages.
Since Oracle 11g:
The Access Control List is introduced. You not only can control who has access to these packages by granting, but now you can also control which resources they can call.
For instance, when a user is granted to send emails using UTL_MAIL, you can also control that he/she is only able to send through a specified mail server.
At first this looks like a obstacle (ORA-24247), but since the Voyager worm struck Oracle databases a year ago, it is introduced as an extra security measurement.
I faced the same scenario with my client,
Client complained saying his package is not working after the DB is upgraded to 11gR2 and throws below error.
ORA-24247: network access denied by access control list (ACL)
Here are athe steps i followed to resolve this issue.
NOTE: Package,DB and host name
[oracle@unixprod]:../oracle>
[oracle@unixprod]:../oracle>. oraenv
ORACLE_SID = [PROD1] ? PROD1
The Oracle base for ORACLE_HOME=/local/oracle/product/11.2.0.2 is /local/oracle/product/11.2.0.2
[oracle@unixprod]:../oracle>sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 9 15:32:24 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
PROD1
SQL> conn user1/******
Connected.
SQL> show user
USER is "USER1"
SQL> exec END_REPORT.send_email('1111','ramshankar80@gmail.com','123','123');
BEGIN END_REPORT.send_email('1011','ram.shankar@am.sony.com','123','123'); END;
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "USER1.END_REPORT", line 4275
ORA-06512: at line 1
SQL> desc END_REPORT
..............
PROCEDURE SEND_EMAIL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
I_FISCAL_DATE VARCHAR2 IN
I_EMAIL_ID VARCHAR2 IN
I_ERROR_CODE VARCHAR2 IN
I_ERROR_MESSAGE VARCHAR2 IN
..................
SQL> !
[oracle@ubetud17]:../oracle>[oracle@ubetud17]:../oracle>oerr ora 24247
24247, 00000, "network access denied by access control list (ACL)"
// *Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not
// been granted to the user in the access control list.
// *Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the
// target host has been granted to the user.
[oracle@ubetud17]:../oracle>exit
SQL> show user
USER is "USER1"
SQL> conn /as sysdba
Connected.
SQL> begin
dbms_network_acl_admin.create_acl (
acl => 'utl_mail.xml',
description => 'Allow mail to be send',
principal => 'USER1',
is_grant => TRUE,
privilege => 'connect'
);
commit;
end;
/
2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
SQL> SQL> begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_mail.xml',
principal => 'USER1',
is_grant => TRUE,
privilege => 'resolve'
);
commit;
end;
/
2 3 4 5 6 7 8 9 10
PL/SQL procedure successfully completed.
SQL> SQL> begin
dbms_network_acl_admin.assign_acl(
acl => 'utl_mail.xml',
host => '<smtp host name or IP address>'
);
commit;
end;
/
2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> conn user1
Connected.
SQL> show user
USER is "USER1"
SQL> exec END_REPORT.send_email('1111','ramshankar80@gmail.com','123','123');
PL/SQL procedure successfully completed.
SQL>
Cleaning Up the Access Control List
BEGIN
DBMS_NETWORK_ACL_ADMIN.unassign_acl (
acl => 'utl_mail.xml',
host => 'smtp_host_name');
COMMIT;
END;
/
Delete a privilege from Access Control List
BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => 'utl_mail.xml',
principal => 'utl_mail_p',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
Drop an Access Control List
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
acl => 'utl_mail.xml');
COMMIT;
END;
/
Qery to view the Access Control List details:
col principal for a40
col acl for a40
set pages 340
set lines 300
select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
Send_mail Procedure
CREATE PROCEDURE send_mail_test
(sender IN VARCHAR2
,recipient IN VARCHAR2
,subject IN VARCHAR2
,message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'smtp_host_server';
mail_conn utl_smtp.connection;
crlf VARCHAR2(2) := CHR(13) || CHR(10);
mesg VARCHAR2(32000);
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg := 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf
||'From: <' || sender || '>' || crlf ||
'Subject: '|| subject || crlf ||
'To: ' || recipient || crlf ||
'' || crlf || message;
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
exec send_mail_test (' oracle@host_name.com','ramshankar80@gmail.com','test subject','test body');
From 11g Oracle offers more fine-grained access control for users to access external network resources With ACL’s.
Prior 11g:
The packages UTL_MAIL, UTL_SMTP, UTL_HTTP, UTL_TCP etc. allow communication beyond the database server to the outside world, but when granted access, all hosts can be accessed.
This can be interpreted as a security flaw as no login is required when using UTL_TCP.
For example. DBA’s are advised to revoke the execute privileges from public on these kind of packages.
Since Oracle 11g:
The Access Control List is introduced. You not only can control who has access to these packages by granting, but now you can also control which resources they can call.
For instance, when a user is granted to send emails using UTL_MAIL, you can also control that he/she is only able to send through a specified mail server.
At first this looks like a obstacle (ORA-24247), but since the Voyager worm struck Oracle databases a year ago, it is introduced as an extra security measurement.
I faced the same scenario with my client,
Client complained saying his package is not working after the DB is upgraded to 11gR2 and throws below error.
ORA-24247: network access denied by access control list (ACL)
Here are athe steps i followed to resolve this issue.
NOTE: Package,DB and host name
[oracle@unixprod]:../oracle>
[oracle@unixprod]:../oracle>. oraenv
ORACLE_SID = [PROD1] ? PROD1
The Oracle base for ORACLE_HOME=/local/oracle/product/11.2.0.2 is /local/oracle/product/11.2.0.2
[oracle@unixprod]:../oracle>sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 9 15:32:24 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
PROD1
SQL> conn user1/******
Connected.
SQL> show user
USER is "USER1"
SQL> exec END_REPORT.send_email('1111','ramshankar80@gmail.com','123','123');
BEGIN END_REPORT.send_email('1011','ram.shankar@am.sony.com','123','123'); END;
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "USER1.END_REPORT", line 4275
ORA-06512: at line 1
SQL> desc END_REPORT
..............
PROCEDURE SEND_EMAIL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
I_FISCAL_DATE VARCHAR2 IN
I_EMAIL_ID VARCHAR2 IN
I_ERROR_CODE VARCHAR2 IN
I_ERROR_MESSAGE VARCHAR2 IN
..................
SQL> !
[oracle@ubetud17]:../oracle>[oracle@ubetud17]:../oracle>oerr ora 24247
24247, 00000, "network access denied by access control list (ACL)"
// *Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not
// been granted to the user in the access control list.
// *Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the
// target host has been granted to the user.
[oracle@ubetud17]:../oracle>exit
SQL> show user
USER is "USER1"
SQL> conn /as sysdba
Connected.
SQL> begin
dbms_network_acl_admin.create_acl (
acl => 'utl_mail.xml',
description => 'Allow mail to be send',
principal => 'USER1',
is_grant => TRUE,
privilege => 'connect'
);
commit;
end;
/
2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
SQL> SQL> begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_mail.xml',
principal => 'USER1',
is_grant => TRUE,
privilege => 'resolve'
);
commit;
end;
/
2 3 4 5 6 7 8 9 10
PL/SQL procedure successfully completed.
SQL> SQL> begin
dbms_network_acl_admin.assign_acl(
acl => 'utl_mail.xml',
host => '<smtp host name or IP address>'
);
commit;
end;
/
2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> conn user1
Connected.
SQL> show user
USER is "USER1"
SQL> exec END_REPORT.send_email('1111','ramshankar80@gmail.com','123','123');
PL/SQL procedure successfully completed.
SQL>
Cleaning Up the Access Control List
BEGIN
DBMS_NETWORK_ACL_ADMIN.unassign_acl (
acl => 'utl_mail.xml',
host => 'smtp_host_name');
COMMIT;
END;
/
Delete a privilege from Access Control List
BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => 'utl_mail.xml',
principal => 'utl_mail_p',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
Drop an Access Control List
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
acl => 'utl_mail.xml');
COMMIT;
END;
/
Qery to view the Access Control List details:
col principal for a40
col acl for a40
set pages 340
set lines 300
select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
Send_mail Procedure
CREATE PROCEDURE send_mail_test
(sender IN VARCHAR2
,recipient IN VARCHAR2
,subject IN VARCHAR2
,message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'smtp_host_server';
mail_conn utl_smtp.connection;
crlf VARCHAR2(2) := CHR(13) || CHR(10);
mesg VARCHAR2(32000);
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg := 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf
||'From: <' || sender || '>' || crlf ||
'Subject: '|| subject || crlf ||
'To: ' || recipient || crlf ||
'' || crlf || message;
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
exec send_mail_test (' oracle@host_name.com','ramshankar80@gmail.com','test subject','test body');
