Friday, February 4, 2011

ORA-24247 when trying To send email using UTL_SMTP in Oracle 11gR2

If any of your applications use SYS.UTL_SMTP package to send emails in Oracle 11gR2, you will come across ORA-24247 in Oracle 11gR2. Following notes is explaining the problem and solution.

I have a procedure to use SYS.UTL_SMTP package to send mail from my sql scripts in Oracle 10gR2 and was running fine. Here is the simplified version of this code.


create or replace procedure mail_send_test
as
smtp_server varchar2(50);
c utl_smtp.connection;
begin
smtp_server := 'mailserver.domain.name';
c := utl_smtp.open_connection(smtp_server,25);
dbms_output.put_line ('connection success');
end;
/


I started getting following error after upgrading my database instance to 11gR2.
Here is the full work log


[oracle@prdcxx002:/home/oracle] $ sqlplus /
SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 2 15:40:14 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> @smtp_test.sql;
Procedure created.
SQL> exec mail_send_test;
BEGIN mail_send_test; END;
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "OPS$ORACLE.MAIL_SEND_TEST", line 10
ORA-06512: at line 1


After reading Oracle Metalink and Oracle 11g manual, I understood that this is an expected behavior as Oracle started implementing Access Control List (ACL) in sensitive packages like UTL_SMTP, UTL_HTTP etc to take care of security loop holes. We need to start granting privileges for users who needs to use these packages via SYS.DBMS_NETWORK_ACL_ADMIN procedure. DBMS_NETWORK_ACL_ADMIN procedure provides security for network related PL/SQL packages UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR. Here are the steps to grant them. You need XDB to be installed for ACL to work.


Solution is to execute following sql procedures after connecting as SYSDBA
1) Creates an access control list (ACL) with an initial privilege setting


begin
dbms_network_acl_admin.create_acl (
acl => 'mailserver_acl.xml',
description => 'ACL for user to connect to SMTP Server',
principal => 'ORACLE_USERNAME',
is_grant => TRUE,
privilege => 'connect');
end;
/
commit;


2) Assigns an access control list (ACL) to a network host, and optionally specific to a TCP port range


begin
dbms_network_acl_admin.assign_acl (
acl => 'mailserver_acl.xml',
host => 'mailservername.domain.name',
lower_port => 25);
end;
/
commit;


3) Adds a privilege to grant or deny the network access to the user in an access control list (ACL)


begin
dbms_network_acl_admin.add_privilege (
acl => 'mailserver_acl.xml',
principal => 'OPS$ORACLE',
is_grant => TRUE,
privilege => 'connect');
end;
/
commit;


After these 3 steps, I logged in as Oracle on prdcmds002 and executed the procedure which connects to mail server and it worked without any ORA errors.


SQL> set serveroutput on
SQL> exec mail_send_test;
connection success
PL/SQL procedure successfully completed.


Please note that due to some bugs, we need to grant these privileges to individual users and not to a role. See Bug 7010711: ACL PRIVILEGES GRANTED TO ROLES ARE NOT CASCADED TO THE USERS


References
=======
1. See Oracle Metalink Doc: 754909.1 - ORA-24247 Calling UTL_SMTP or UTL_HTTP or UTL_TCP in a Stored Procedure [ID 754909.1]
2. See Oracle Metalink Doc: 557070.1 - ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher

We need to set ACL for UTL_INADDR package also in Oracle 11g. We could check this by issuing
SELECT utl_inaddr.get_host_name FROM dual;If you get ORA-24247, create a acl with privilege=resource.

Here is the sql I used.
-- rem Create ACL for using UTL_INADDR
begin
dbms_network_acl_admin.create_acl (
acl => 'resolve_ipaddr_acl.xml',
description => 'ACL for user to resolv Network names and ip address',
principal => 'ETRADM',
is_grant => TRUE,
privilege => 'resolve');
end;
/
commit;


-- rem Assign ACL to one or more network hosts
begin
dbms_network_acl_admin.assign_acl (
acl => 'resolve_ipaddr_acl.xml',
host => '*');
end;
/
commit;


You need XDB to be installed for ACL to work. So if you get following error, verify XDB is installed.

Duplicate Filename Found for Submission_id: 6011
Standard exception
VALIDATE Submission: 6011 Step: Duplicate Submission Ch
Exception CODE: 1
Exception ERRM: User-Defined Exception
Stop Time: 21-MAR-2011 08:15:04 AM
DECLARE
*
ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at "FFLADM.EMAIL_HANDLER", line 36
ORA-06512: at "FFLADM.REBATE_PROCESS", line 515
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 5

can use following script to install XDB

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /orasw/app/oracle/admin/scripts/xdb_protocol.log append
@/orasw/app/oracle/product/11.2.0/db/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP YES;
connect "SYS"/"&&sysPassword" as SYSDBA
@/orasw/app/oracle/product/11.2.0/db/rdbms/admin/catxdbj.sql;
@/orasw/app/oracle/product/11.2.0/db/rdbms/admin/catrul.sql;
spool off

You can use following views to see current ACL settings.
 
SQL>  select acl,principal from dba_network_acl_privileges;

ACL                                 PRINCIPAL
----------------------------------- -------------------------
/sys/acls/mailserver_acl.xml        OPS$ORACLE
/sys/acls/mailserver_acl.xml        ETRADM
/sys/acls/resolve_ipaddr_acl.xml    ETRADM

SQL> select * from DBA_NETWORK_ACLS;

HOST                                     LOWER_PORT UPPER_PORT ACL                                 ACLID
---------------------------------------- ---------- ---------- ----------------------------------- --------------------------------
myserver.unix.tarikida.org                          25         25 /sys/acls/mailserver_acl.xml        9B6B9AF666988026E0430A9A1D188026
*                                                              /sys/acls/resolve_ipaddr_acl.xml    9B7AC2AFB00B202AE0430A9A1D18202A

2 comments:

  1. We need to set ACL for UTL_INADDR package also in Oracle 11g. We could check this by issuing
    SELECT utl_inaddr.get_host_name FROM dual;

    If you get ORA-24247, create a acl with privilege=resource.

    Here is the sql I used.
    -- rem Create ACL for using UTL_INADDR
    begin
    dbms_network_acl_admin.create_acl (
    acl => 'resolve_ipaddr_acl.xml',
    description => 'ACL for user to resolv Network names and ip address',
    principal => 'ETRADM',
    is_grant => TRUE,
    privilege => 'resolve');
    end;
    /
    commit;

    -- rem Assign ACL to one or more network hosts
    begin
    dbms_network_acl_admin.assign_acl (
    acl => 'resolve_ipaddr_acl.xml',
    host => '*');
    end;
    /
    commit;

    ReplyDelete
  2. You need XDB to be installed for ACL to work. So if you get following error, verify XDB is installed.

    Duplicate Filename Found for Submission_id: 6011
    Standard exception
    VALIDATE Submission: 6011 Step: Duplicate Submission Ch
    Exception CODE: 1
    Exception ERRM: User-Defined Exception
    Stop Time: 21-MAR-2011 08:15:04 AM
    DECLARE
    *
    ERROR at line 1:
    ORA-24248: XML DB extensible security not installed
    ORA-06512: at "SYS.UTL_TCP", line 17
    ORA-06512: at "SYS.UTL_TCP", line 246
    ORA-06512: at "SYS.UTL_SMTP", line 127
    ORA-06512: at "SYS.UTL_SMTP", line 150
    ORA-06512: at "FFLADM.EMAIL_HANDLER", line 36
    ORA-06512: at "FFLADM.REBATE_PROCESS", line 515
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at line 5


    you can use following script to install XDB

    SET VERIFY OFF
    connect "SYS"/"&&sysPassword" as SYSDBA
    set echo on
    spool /orasw/app/oracle/admin/scripts/xdb_protocol.log append
    @/orasw/app/oracle/product/11.2.0/db/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP YES;
    connect "SYS"/"&&sysPassword" as SYSDBA
    @/orasw/app/oracle/product/11.2.0/db/rdbms/admin/catxdbj.sql;
    @/orasw/app/oracle/product/11.2.0/db/rdbms/admin/catrul.sql;
    spool off

    ReplyDelete